October 29, 2015 at 6:38 am
I'm guessing there is going to be no easy way to do this, if at all.
I'm trying to create a solution that will allow me to Populate/Add columns to the Data Warehouse without having to edit my SSIS packages.
I have a table that holds the package, table and column information,
I then uses a script task to build my select statement into a variable used as a parameterised OLEDB source.
I want to use the same variable for all tables in a particular package, I.e ExtractDimAccountData.
The problem I'm having is that I need to be able to map the columns in the destination dynamically.
Has anyone any experience of doing this?
October 29, 2015 at 6:43 am
its a fairly common requirement and has been asked bfeore , you could start here , the main thing is to use the script task within the dft.
http://www.sqlservercentral.com/Forums/Topic628424-147-3.aspx
October 29, 2015 at 7:19 am
Jayanth_Kurup (10/29/2015)
http://www.sqlservercentral.com/Forums/Topic628424-147-3.aspx%5B/quote%5D
Common requirement it may be. However, the thread you reference does not tackle it directly, but instead offers a couple of alternatives (the ExecuteSQL one and the Cozyroc one).
Note also that DFTs cannot contain Script Tasks.
The bottom line here is that column mappings in an SSIS package are done at design time and cannot be changed at run time.
Having said that, with a bit of effort you should be able to use BIML to regenerate your packages, with updated column names, assuming they follow a standard design pattern.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply