January 22, 2019 at 11:06 am
Hello -
I have a data flow task within which I am moving data from a table in one database to a table in another DB. The source table has three fields which are all VARCHAR(50) and the destination table has those same fields as VARCHAR(10). I've tried changing the length of the fields in the metadata inside the Advanced Editor but I still get a truncation error and the package fails.
Literally at the end of my rope here. Doing a search online just tells me to do what I've already tried and that obviously doesn't work in my case. How the hell am I supposed to get around this?
And ideas?
January 22, 2019 at 12:43 pm
Polymorphist - Tuesday, January 22, 2019 11:06 AMHello -
I have a data flow task within which I am moving data from a table in one database to a table in another DB. The source table has three fields which are all VARCHAR(50) and the destination table has those same fields as VARCHAR(10). I've tried changing the length of the fields in the metadata inside the Advanced Editor but I still get a truncation error and the package fails.Literally at the end of my rope here. Doing a search online just tells me to do what I've already tried and that obviously doesn't work in my case. How the hell am I supposed to get around this?
And ideas?
In the OLEDB Source (assuming that is the object you are using) - change from pulling from a table to pulling from a query. Insert the query as:
SELECT col1 = cast(col1 As varchar(10))
, col2 = cast(col2 As varchar(10))
, col3 = cast(col3 As varchar(10))
FROM yourSourceTable
Better yet - if you can - create a stored procedure on the source system and use that as the source. Instead of the query above - just execute the stored procedure. Either way - this will truncate the source data and define the columns as varchar(10) and you will no longer have any issues. However, you will be losing the truncated data - so if the data after the first 10 characters is important - then you need to change the destination columns to allow for the size of the column from the source system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 22, 2019 at 12:43 pm
You probably need to use a Derived Column transformation to define a varchar(10) column from the expression "LEFT(xyz, 10)".
Changing the metadata to say you only want 10 characters does not tell SSIS to ignore the truncation. You need to put in an explicit expression.
You could also specify a query at the source that used "LEFT(xyz, 10)" expressions for the columns in question.
January 22, 2019 at 5:29 pm
I added the Derived Column Transformation and handled it that way. Seems to work now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply