0

I have an SSIS data flow task that reads a CSV file with certain fields, tweaks it a little and inserts results into a table. The source file name is a package parameter. All is good and fine there.

Now, I need to process slightly different kind of CSV files with an extra field. This extra field can be safely ignored, so the processing is essentially the same. The only difference is in the column mapping of the data source..

I could, of course, create a copy of the whole package and tweak the data source to match the second file format. However, this "solution" seems like terrible duplication: if there are any changes in the course of processing, I will have to do them twice. I'd rather pass another parameter to the package that would tell it what kind of file to process.

The trouble is, I don't know how to make SSIS read from one data source or another depending on parameter, hence the question.

Ivan Krivyakov
  • 1,898
  • 1
  • 17
  • 27
  • Might be a duplicate of this question and so I'd see if my answer doesn't apply here. http://stackoverflow.com/questions/8168548/ssis-task-for-inconsistent-column-count-import/8173162#8173162 – billinkc Apr 10 '13 at 20:21
  • This might work. I will check it out and let you know. – Ivan Krivyakov Apr 10 '13 at 20:54
  • On second thought, this would fit only if two kinds of CSS files have exactly the same column names, perhaps in different order and with some columns ommitted. E.g. if one file has column "Price" and the other has "FinalPrice" instead, conditional query logic will be required, so the original question stands. – Ivan Krivyakov Apr 10 '13 at 21:04

2 Answers2

1

I would duplicate the Connection Manager (CSV definition) and Data Flow in the SSIS package and tweak them for the new file format. Then I would use the parameter you described to Enable/Disable either Data Flow.

In essence, SSIS doesnt work with variable metadata. If this is going to be a recurring pattern I would deal with it upstream from SSIS, building a VB / C# command-line app to shred the files into SQL tables.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
0

You could make the connection manager push all the data into 1 column. Then use a script transformation component to parse out the data to the output, depending on the number of fields in the row.

You can split the data based on delimiter into say a string array (I googled for help when I needed to do this). With the array you can tell the size of it and thus what type of file it is that has been connected to.

Then, your mapping to the destination can remain the same. No need to duplicate any components either.

I had to do something similar myself once, because although the files I was using were meant to always be the same format - depending on version of the system sending the file, it could change - and thus by handling it in a script transformation this way I was able to handle the minor variations to the file format. If the files are 99% always the same that is ok.. if they were radically different you would be better to use a separate file connection manager.

Stephen
  • 95
  • 1
  • 9