Tuesday, May 22, 2007

SSIS: Flat File Connection Manager gotchyas

So my latest issue with SSIS: The Flat File Connection Manager.

Everything works fine, unless you want to add or remove a column from the source that the manager is connected to. Normally, with the other managers, etc. They pick up the change, or let you "fix" the change. Not with the Flat File Connection manager.

From what I've been able to find, you have to delete the manager, recreate it, and re-assign it to your flat file destination in order to "pick up" that extra column that you wanted. I use the flat file only for debugging the output of my package, before actually updating the database itself. Handy, except for that pesky issue. If I click the reset columns, I would expect to have the columns reset to the input. Nope, everything goes blank and you have to either delete the manager or hand enter the columns. Yikes!

Oh yeah, one more gotcha: if you run it without column headings check for the first row, then decide that you really want them, be sure to delete the files first, or you'll get really hosed.

Pretty much you'll be forced to delete the manager and recreate it.

1 comment:

rbhro said...

i don't know where u problem was exactly reset columns works fine .. u have by any means conn string using a variable?