March 26, 2010 at 8:45 am
Here goes...
I have a SQL Stored proc, that takes 2 parameters and based on those parameters spits out a different file each time, the number of columns, names of columns etc can change. My next task is to capture the output of this stored proc in a delimited file (delimiter is specified, which again can be anything) whose file name is also to be set dynamically.
Given the above situation, I was wondering if I can use SSIS script task to dynamically set the mappings of the fllatfile connection? Or what other options I have? I have to do this in SSIS, as there are other things I have to do as well.
Also, another part is, I ahve to look fo rthe delimiter (which probably is going to be comma, but code has to be flexible enough to accomodate anything per specification) in the data and escape the data.
March 29, 2010 at 11:12 am
Hi All, I have figured out a way to accompalish what I set out to do and wanted to share the answer. I created a package dynamically in script task, in the package creation script, I created a dataflow task and flatfile destination at run time. Based on the output of the stored proc executed using OLE DB source, the flatfile connection was set at runtime. So, in short I got what I wanted...dynamic mapping of columns for flat file destination in SSIS. But involved a bit more coding.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply