question

wpg avatar image
wpg asked

Is it possible to right trim a flat file when bulk inserting using an SSIS package?

I've been tasked with modifying an SSIS package so that the flat files that are inserted into the database via a bulk insert are right trimmed. This is something new to me. The issue at hand is that there are several flat files that get inserted using a bulk insert, however, occassionally the files have carriage returns that are too far to the right which in turns causes the insert to fail. Currently we have to manually verify that the files are in a correct format prior to the package being run. We want to modify the package so that it does this for us. I understand that I can create a data flow with a derived column to do this for me, there approximately 20 files for which I need this done for and I'm looking for a faster, easier way of doing this.
insertbulk-insertderived-column
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you give us an example of the flat file and how it's laid out?
0 Likes 0 ·
How big are the flat files? If small then you could use a script task to shred the data and ensure it is the correct format.
0 Likes 0 ·

1 Answer

· Write an Answer
desertislesql avatar image
desertislesql answered
You are on the right track by looking to create a data flow as when you want to manipulate data within the package, you will need to create a dataflow, passing in your flat file input, create a derived column which trims the field in question and moving the data to whatever your destination is. Assuming your destination is SQL Server, you would use the table or view fast load to map the fields in your flat file to the table within SQL Server where you want to move the data.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.