How to replace all the empty derivation with SetNull()

aschand · Post by **aschand** » Tue Jan 15, 2008 6:04 pm

Hi i have 700 columns in target side and 400 of them do not have data nor the source columns to be linked... so what should i do such that i can replace the empty dervation columns with Setnull() at the target

Thanks for your help
aschand

ArndW · Post by **ArndW** » Tue Jan 15, 2008 6:29 pm

Select the columns in the right that you wish to modify, then right-mouse click and one of the options will let you replace derivations; you will enter "SetNull($1)" in the bottom box and -presto- the modifications will get done with just a few keystrokes.

aschand · Post by **aschand** » Tue Jan 15, 2008 6:36 pm

But I got these errors while transforming

##W IIS-DSEE-TFEV-00023 00:29:42(093) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 00:29:42(094) <transform> Error when checking composite operator: Converting string to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(095) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 00:29:42(096) <transform> Error when checking composite operator: Converting ustring to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(097) <transform> Error when checking composite operator: Implicit conversion from source type "UString" to result type "Decimal".
##W IIS-DSEE-TFEV-00023 00:29:42(098) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 00:29:42(099) <transform> Error when checking composite operator: Converting string to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(100) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "Decimal".
##W IIS-DSEE-TFEV-00023 00:29:42(101) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00023 00:29:42(102) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00023 00:29:42(103) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 00:29:42(104) <transform> Error when checking composite operator: Converting string to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(105) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 00:29:42(106) <transform> Error when checking composite operator: Converting string to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(107) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "Decimal".
##W IIS-DSEE-TFEV-00025 00:29:42(108) <transform> Error when checking composite operator: Converting string to decimal.
##W IIS-DSEE-TFEV-00023 00:29:42(109) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "Decimal".

Not only for the ones I setnull but also for the other columns to which i have linked from source to target
What do they mean? What should I be doing?

ray.wurlod · Post by **ray.wurlod** » Tue Jan 15, 2008 6:53 pm

You should be making sure that your data types and nullability are all correct, and using the appropriate functions for type conversion (where required) and null conversion (where required).

ArndW · Post by **ArndW** » Tue Jan 15, 2008 7:42 pm

Sorry, you should just have replaced the derivations with SetNull() but you still need to declare correct datatypes.

aschand · Post by **aschand** » Tue Jan 15, 2008 9:17 pm

I did so just with SetNull()....

but i dint get clear picture of the datattypes... how do you think they should be done?

Can you please give me an example in the derivation

aschand · Post by **aschand** » Tue Jan 15, 2008 10:07 pm

Can anyone please tell how to write in the derivation when we have to convert

1. varchar to nvarchar
2. integer to decimal
3. string to decimal
4.string to Ustring
5. ustring to decimal

where first one is source and second is target

Thank you so much

ray.wurlod · Post by **ray.wurlod** » Tue Jan 15, 2008 10:39 pm

Implicit conversions you don't need to do anything. Where you need explicit conversions you can find the function name in the Chapter on the Modify stage or the Chapter on the Transformer stage, depending on which stage type you're using.

I note that you have a sig with "urgent" in it. We don't do "urgent" here. DSXchange is an all-volunteer site, whose participants post as and when they can. If you want urgent support sign up with your support provider for premium service, and learn the true cost of "urgent".

aschand · Post by **aschand** » Tue Jan 15, 2008 11:32 pm

Sorry I dint mean to say its urgent though it is... it was by accident i had attached the signature that i wrote for the first time...

If in source file..... abc varchar 255
target table ........ abc Nvarchar 50

Now how do I link one to the other by converting source datatype and its length into target datatype and its length?

can you please let me know the exact dervation expression in which i have to convert.... i mean the function and so ... please help me with exact terms

thanks a lot

ray.wurlod · Post by **ray.wurlod** » Wed Jan 16, 2008 12:41 am

An implicit conversion will occur, but you will need to use something like a Left() function because the target string is shorter than the source string; otherwise you will get "potential truncation" alerts.

aschand · Post by **aschand** » Wed Jan 16, 2008 12:53 am

can you please put it as normal content instead of premium content please

and can you be more specific... by giving exact derivation

thanks
aschand