adding extra columns to a CSV file cause SSIS import to fail

Question

adding extra columns to a CSV file cause SSIS import to fail

ryan.lawrence

Say Hey Kid

Points: 688
More actions
June 10, 2009 at 10:40 am

#131742

we import over 200 flat files daily into a sql server 2005 database using SSIS. When we were in SQL 2000 using DTS we could add any new columns we wanted to the end of the flat file, and DTS would ignore them until we mapped them in the transformation. SSIS fails because since these new columns aren't mapped, SSIS tries to throw them all into the last column defined in the file.
does anybody know of a way besides adding a dummy column to the end of every file to set SSIS to ignore these extra columns so that we can map them in later than when the file changes.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

ryan.lawrence (6/10/2009)
we import over 200 flat files daily into a sql server 2005 database using SSIS. When we were in SQL 2000 using DTS we could add any new columns we wanted to the end of the flat file, and DTS would ignore them until we mapped them in the transformation. SSIS fails because since these new columns aren't mapped, SSIS tries to throw them all into the last column defined in the file.
does anybody know of a way besides adding a dummy column to the end of every file to set SSIS to ignore these extra columns so that we can map them in later than when the file changes.

Check CozyRoc Data Flow Task Plus. It includes support for dynamic data flows and may help in your scenario. We will be glad to hear your feedback.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 2

Ryan,

its a truncation error while reading from flat file. What u can do is go to the flat file source and select Error Output on left side of ur source editor.

Find the last column and there is a option for "Truncation". select ignore failure. it should work.

however ur data will get truncated after the specified lenth in the flat file source schema.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

All you need to do is update the meta data for the source connection to include the extra columns - but you don't need to send them through the pipeline. You are telling SSIS that you know about the columns, but don't want to process them.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ryan.lawrence Say Hey Kid Points: 688 More actions · Answer 4

thanks everybody for the tips, but let me do a little more explaining so that you are clear on the root problem.

i have 3 X 100 files coming in at 8 hour increments.

So i get a set of 100 files with data from ASIA (i then import the new asia data, along with the old North America and European Data)

8 hours pass

i get another set of 100 files from Europe (i then import the new european data, along with the old North America and Asia Data)

8 more hours pass

i get another set of 100 files from North America(.... you get the idea ... lather, rinse... repeat)

....

i make a change to the source system that extracts the files, which goes into production all at once because while i am processing 3 sets of extracts, it is one source SAP system)

.....

the first download after the production release is europe, so the new file structure is in place for the emea file.

so if i change my ssis package to look for new columns the europe file will be fine, but the other 2 will fail because they won't have the files

.....

bottom line is i am trying to avoid having to publish 3 versions of the same ssis package in short succession, 1 after each import with the updated column metadata.

their has to be a way to set the ssis package to ignore any columns it doesn't have meta data for, just like DTS had to do... that way once all 3 of my extracts have run, i make one change to the SSIS package, and it runs along happy until the next time i add columns to my extract.