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.
Can you give us an example of the flat file and how it's laid out?
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.
17 People are following this question.
