Today, I came across a post in the SSIS forum today where Ash was trying to get the Max Value from a Varbinary column in a Table and update another Varbinary column in a second table. The poster was trying to use 2 execute SQL Task; but was having hard time to map the Varbinary value to a SSIS variable.
After doing some tests, I came to the conclusion that the range of data types available for SSIS variables does not offer a simple way of accomplishing this. If you decide to use an Object variable to place the value in the first Execute SQL Task; then you won't be able to reference that variable in the second Execute SQL Task. You could try to use the T-SQL cast function in the Select and Update statements to convert the varbinary value back and forth, to let's say string or integer; but this could open the door to other data conversion/lost issues.
Looking again at the requirement, I decided to give it a second try using a dataflow, as the Data Flow has a richer range of data types. In our case, it seems like DT_BYTES would be able to accept the Varbinary value with no problem. Then, to suffice our requirement all we need is an OLE DB Source component and an OLE DB Command transformation. As I said it before, OLE DB Transformation is very expensive from the performance stand point as the command is executed for every row passing through the pipeline; but in this case the statement will be executed only once.
Here, a couple of screen shots of the package:
Hi,
ReplyDeleteI'm working on a ETL package where the excel files will be uploaded by different set of clients. My company doesn't want to enforce any rules on metadata, as the users upload the files on net. Our testing team test the package using character set of data in numeric columns and numeric data where character data should be there.
when there is a error in the excel sheet i should send it to error output. if they put char data in numeric column i get only null values in my package. when i divert the same to error they say that char data should be diverted.
To eliminate this i changed all the metadata to DT_WSTR so that i can divert the char data as it is to error column. but the numeric value which i get in those columns comes differently from the original data. Especially in case of decimals i get 56.000001 instead 56.10(this is just an example)
how to solve this problem.
Thanks,
Regards
Viji
Hi,
ReplyDeleteI'm working on a ETL package where the excel files will be uploaded by different set of clients. My company doesn't want to enforce any rules on metadata, as the users upload the files on net. Our testing team test the package using character set of data in numeric columns and numeric data where character data should be there.
when there is a error in the excel sheet i should send it to error output. if they put char data in numeric column i get only null values in my package. when i divert the same to error they say that char data should be diverted.
To eliminate this i changed all the metadata to DT_WSTR so that i can divert the char data as it is to error column. but the numeric value which i get in those columns comes differently from the original data. Especially in case of decimals i get 56.000001 instead 56.10(this is just an example)
how to solve this problem.
Thanks,
Regards
Viji