November 27, 2006 at 4:31 pm
Hi,
I just migrated from DTS to SSIS a couple of days back, and one task which I use quite a bit is setting a text file source (for a data transform task) dynamically via an ActiveX Script to copy text file contents to a DB table.
After migrating to SSIS, i was re-writing the packages and for this one, I am a little unsure how to go about it.
In DTS, I could a oConnection.DataSource = FileName, and that would be sweet.
In SSIS, for the moment, I have the text file source, and a variable that stores the filename and it keeps changing from time to time.
I tried to set the "Expressions" for the Text File Source connection and added the ConnectionString expression to map the variable, and set the Delay Validation property to False in the data flow task, but I still get an error to the nature of:
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Flat File Source [1]] Error: The "output column "Column 0" (56)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (56)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Flat File Source [1]] Error: An error occurred while processing file "D:\FTP\Cardholder\Input\ACPT.CPAC.FRAUD.CARDHLD" on data row 1.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
The file name that it says "ACPT.CPAC.FRAUD.CARDHLD" is correct, but it still fails.
Initially, when I set the flat file source, i set the file name to a file which has the same mapping as this file, so I thought it should retain the transformation as in DTS.
Does anyone know how I can retain the transformation and get the file to complete the transform task?
Thanks,
Saurabh
November 28, 2006 at 2:15 am
Are you using a Script Component in SSIS?
You could try using the ForEach component and set it to look for the part of the filename that does not change.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
November 28, 2006 at 8:51 pm
I didn't need to use the FOR EACH block, but for the moment, it picked up the text file.
But what I noticed was that it wasn't copying all the data across. I.E. the text file has a few thousand rows with data being 750 chars in length, but it has plenty of spaces too, in the middle, at the end.
So i set therow delimiter to be CR and LF, but when i load it into the table, i have rows with length 553, 182, 200, etc.
I ran the same package using the DTS Components wizard in SQL 2005 (using the old DTS package that i converted), and it copied the rows no worries with all having length 750.
Are there any other settings i hv to see as well?
Btw, when i setup the package i set it up with a dummy file, which has only 2 records of length abt 20 each. So i set up using that, with row delim CR, LF, same for column delim.
But it seems to truncate the data at particular places.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply