Hi all. I don't think this was possible in SSIS 2008 but I'm wondering if the new versions like SSIS 2014 have an option to retain the meta-data for any changes to flat file connections. For example if you have a flat file that has been updated with a new column, I'd like to be able to update the connection in the package without having to reset all the advanced column information such as data type, width, etc. This is a real pain for me as I routinely have to do this for growing files. Any suggestions would be greatly appreciated.
Would this also involve the destination table to which the data from flat file is directed?
If I'm understanding the question correctly, always a risky proposition with me, you want to modify the format file to reflect schema changes. The format file would be used with the BCP utility for example. If so, the format file is just a text file; you could modify it by hand to reflect the meta change. Alternately, you could use the "format" option of BCP to regenerate the contents of the format file.
it seems like your source file content is always changing so you will need to implement a system that also changes the destination table dynamically. for this SSIS might be the solution but not your out of the box data work flow. you will need to do this using a scripting language like powershell or script component in SSIS where you can use c# or vb.net to read the file content, get column count and column names, generate a new schema based on content of file and dump data into new dynamically generated table. but I think you will need to fill us in on the rest of the work flow.
Sorry for the late reply. Yes the destination table would be changing as well. To answer stephen_hendricks question, no I'm not using the BCP utility although I know that was just an example of yours.
But yes my source file does continuously change and my main goal is to be able to accommodate those changes without having to re-create those meta-data changes for the file as I mentioned previously. While I do understand the destination table has its own changes, my focus is more on the file connection and its properties. As you suggested though, it seems that an out of box solution doesn't exist for this which I was afraid of. This means every time I have a new column, I need to reset the columns in the file connection which loses all the initial setup of each column before. It would be nice to retain that information but perhaps there isn't a way.
Your suggestion gives me ideas about how the file connection could be dynamically generated but not sure if that's possible. The latter of part your suggestion to dynamically generate the table I'm not so sure about as I prefer to still make changes to that manually.
So manual changes seems to defeat the whole purpose of SSIS packages which are designed to automate things.
so what I recommend does not necessarily mean you need to change your whole process, it means you might just need to add something different to your process so that it accommodates your always changing file. so your connection "could' stay as it but something else in the pipeline or workflow maybe could change.
Well I would like to automate but I'm simply hesitant to dynamically create the table. I think you're right in that I'd need to add something to keep the connection in place. I'm not sure yet but it'll take some experimenting to get something down. Thanks a lot for your help!
Well it looks like the question was actually answered on stack. Haven't tried it yet but looks promising. The solution looks so simple that it drives me crazy I didn't try that. 