September 30, 2009 at 2:42 am
Hi All,
I just came accross the weirdest(well for me anyway) problem in SSIS. I had the following sql in my OLE DB Source:
SELECT
'000' + CONVERT(VARCHAR(12), CIS)[CIS],
WORK,
HOME,
CELL
FROM myTable
Clicking on the PREVIEW button displays the CIS field correctly as 000123456789012 . This control is linked to a flat file destination control. When I debug the package the data exports to the flat file but drops the 3 leading zero's. I tried everything including changing the flat file connection properties to accept DT_STR, DT_WSTR, DT_NTEXT etc. None of these worked.
After much frustration I just created a derived column with the following expression: "000"+(DT_STR,20,1252)[CIS] . This worked perfectly. Can somebody tell me why?
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 30, 2009 at 7:33 am
It sounds like your string is being treated like a number, though from what you describe it shouldn't be. Perhaps the metadata on your source is out of date? Try removing and re-adding that column to see if it changes the outcome.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply