2012年3月19日星期一

any way to check the duplicated rows in destination before loading data?

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

For your first question, probably an easier approach is to use a SORT transform to remove duplicate records before loading data into your destination.

For the other question, I think it's a matter of which format you used in your source strings. Firstly pls be aware we use locale information when doing converting strings to date types or decimals. Secondly, when converting string to date types, you have two options: normal conversion and fast-parse conversion. Normal conversion supports standard oledb formats while fastparse supports ISO 8601. (fastparse option is on the DataConversion output columns)

You'll need to get more detailed helps on this from SQLServer Books On Line. e.g. For fastparse, pls refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bed6e2c1-791a-4fa1-b29f-cbfdd1fa8d39.htm

thanks

wenyang

|||Thanks for your fast response. My first question is to load date from CSV files to the table, but don't insert the duplicated rows which are already existed in the table.|||

I see. you want to avoid inserting rows which'll duplicate rows in your existing destination table. In that case, you can do a lookup first, then leading only those "failing" rows to destination. Remember to set Lookup's error flow handling to Redirect.

thanks

wenyang

没有评论:

发表评论