Dynamic Columns in Flat File Destination

3
votes

I am working on a generic SSIS package that receives a flat file, add new columns to it, and generate a new flat file.

The problem I have is that the number of new columns varies based on a stored procedure XML parameter. I tried to use the "Execute Process Task" to call BCP, but the XML parameter is too long for the command line.

I search on the web and found that you cannot dynamically change the SSIS package during runtime and that I would have to use a script task to generate the output. I started going trough that path and found that you still have to let the script component know how may columns will be receiving and that is exactly what I do not know at design time.

I found a third party SSIS extension from CozyRoc, but I want to do it without any extensions.

Has anyone done something like this?

Thanks!

1
sql-serverssis

1 Answers

1
votes

If the number of columns is unknown at run time then you will have to do something dynamically, and that means using a script task and/or a script component.

The workflow could be:

  1. Parse the XML to get the number of rows
  2. Save the number of rows in a package variable
  3. Add columns to the flat file based on the variable

This is all possible using script tasks, although if there is no data flow involved, it might be easier to do the whole thing in an external Perl script or C# program and just call that from your package.