August 31, 2010 at 8:12 am
Hi -
I am trying to export a DB table into a fixed length flat file. I am new to SSIS so I will explain what I have done.
I set up an OLE DB Source to a Flat File Connection. The format is set to 'Ragged Right'. I have gone into 'advanced' section and set the Input column width and the output column width.
*** I know that the data is going to get truncated which is ok. I have to have the data in the correct starting positions in the flat file. So for example I have input as 100 and output as 40.
I also went into OLE DB Source Error Output and set Truncation to 'Ignore Failure'.
I get the error:
[12419]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
If I change the output to 100, it works fine but obviously the data is not in the correct positions in the flat file.
Any idea how I can allow a truncation to a fixed length file. I need it to truncate the field and just start the next field.
Any input would be extremely helpful.
Thanks in advance.
September 1, 2010 at 1:40 am
Hi,
One thing to remember is that the first column in a flat file is usually Column 0.
If you have already taken this into account then you could use a data transformation task in the data flow to change the length of the input feilds.
Let me know how you get on.
Steve
September 1, 2010 at 8:07 am
Thanks. I just tried this and it seems that regardless I get an error about truncating data. I just want to ignore the truncation. For example if input last name is 25 characters 'JOHNSON' and the output file is 5 characters I want the data to be 'JOHNS'.
I am not sure how to tell SSIS to ignore the truncation error. Any thoughts? Any other work arounds? I know I can perform substings on the data before inserting into the table at the correct length but that seems really messy.
I just dont understand why I cannot ignore a truncation and just let it happen.
September 1, 2010 at 8:47 am
Just tested this functionality myself. In the dataflow, the source was set to the database and the destination was set to a text file. I had the same settings as you. I only exported two columns, forename and surname, both with a max length of 50 .
In between the source and destination i put a data conversion (from the data transformations menu) the input was the forename and surname columns and the output was the same column set to the desired length. I also set it ignore truncation errors.
This is the best way of doing it i believe.
September 1, 2010 at 9:01 am
Like Steve, I when adding a derived column and setting the ignore truncation error on the OLEDB DB source, it works perfectly.
Steve.
September 1, 2010 at 9:49 am
So the output column was set to 2 for example and it just truncated the names?
September 1, 2010 at 10:52 am
Yep, so on the OLE DB source, set the truncate error to ignore. In the derived column, instead of entering a name, selected the 'replace Name with' option (where Name is my field name) and in the expression simply used Substring(Name, 1, 5). Added a new flat file fixed width with row delimiter (which, does as you had originaly posted, makes it a ragged right, which i don't get). Checking the Advanced settings on the Flat File, it shows 3 cols, cols 1 and 2 are incoming, with incoming length as 5, outgoing as 5 and then the row delimiter col.
Steve.
September 1, 2010 at 11:10 am
Thanks Steve - You lost me a little:
In the derived column, instead of entering a name, selected the 'replace Name with' option (where Name is my field name) and in the expression simply used Substring(Name, 1, 5)
So I still use - OLE DB Source -> Data Conversion -> Flat File ?
Where is the 'derived column' you mentioned set-up?
September 1, 2010 at 1:00 pm
Sorry, I realize I basically short-handed my response.
I was referring to a Derived Column Transformation. My Data Flow is made up of 3 components - the Ole DB Source, the derived column transform and then the flat file destination. The derived column transformation allows you to create new columns or alter existing columns that it receives as inputs. I used it to substring the incoming field to only 5 characters.
Honestly, to me this is no different to creating either a view or using a TSQL query to use the TSQL LEFT function - we end up with the same result - in my case, a column with length 5 chars being passed to the flat file destination.
Steve.
September 2, 2010 at 6:35 am
Can't you just prevent the truncation to begin with?
I regularly deal with flat files because we interface with a mainframe. I use this approach.
Use a sql query, or stored procedure as your source and write the query to return the records in the proper format/field size. This way you wont have to deal with derived columns and such. You'll just have a source and flat file destination using this method.
Steve
September 2, 2010 at 7:54 am
So many Steve's, it's an epidemic 😛
Totally agree, why add the additional complexity to the package when a view/query/proc could perform the same function for you and keep the error state for columns etc 'defaulted'.
Steve.
September 2, 2010 at 8:05 am
stevefromOZ (9/2/2010)
So many Steve's, it's an epidemic 😛
...but not many from OZ
September 2, 2010 at 8:33 am
Just to echo the other Steve's, Selecting the data for export from a SP/View/Query and perform the truncation there.
Happy to help
Steve (From England)
September 2, 2010 at 9:24 am
Thanks All. I am planning on going with the truncation in a view but just wanted to make sure I was not overlooking something in SSIS.
Thanks again, as always, this forum is always helpful!! I appreciate it!
September 5, 2010 at 9:39 am
Bosco, sorry to revive this thread but thought you may also be interested in the BIDS Helper (add in for VS 2005 and 08). I was pointing a friend to it today for a different reason but came across it's neat little ability to add a fixed width file connection using a nice ColumnName | Width style interface (ie you can copy|paste this info from excel). Won't necessarily help with the truncation issue but helps with the connection definition 🙂
Steve.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply