I am very new to SSIS. I have a requirement to extract data from tables available in multiple databases (table names and metadata are same) and load to a single target staging area. In my staging area also I will create the same tables (same metadata).
I want to know can we achieve the same in a single package or not?
I am familiar with another ETL tool called Datastage where we can achieve it by parametrize the database name and table name and the metadata mapping will take care while run time with a feature called "Run time column propagation".
Please provide your valuable suggestions.
You can use the same package if the metadata (number of columns, columnames, datatypes) is the same for all source databases.
The easiest is to have some table filled with connectionstrings of those databases where you can loop through with the Foreach Loop Container - ADO enumerator. Within the loop you change the connectionstring of the ConnectionManager with a value from that table.
An other options is to use configurations. First start the package with configarations for source A and then start it withconfigarations for source B, etc.
- Proposed as answer by Amit C A Wednesday, October 23, 2013 5:47 AM
Correct. The Dataflow Task is metadata driven: if something changes you have to process that in your package otherwise it will fail.
But there are alternatives like generating your package with BIML. Very useful for staging multiple tables from (mulitple) databases.
thanks alot for your suggestion. Can you please help me to understand BIML. I went to the link which had provided. But couldn't generate the package as it suggested. Please help me with a solution. Thanks
What went wrong? Post errors/screenshots!
Also see http://bimlscript.com/ for all info about BIML and there is also a BIML Forum at http://varigence.com/Forums?forumName=Biml
here is the error which I am getting while compiling.
this is the code which I am using
<!--Package connection managers-->
<oledbconnection connectionstring="Provider=SQLOLEDB;Server=CARESYS\MSSQL20212;Database=CARESYS;User Id=sa;Password=CARESYS;" name="CARESYS">
<oledbconnection connectionstring="Provider=SQLOLEDB;Server=CARESYS\MSSQL20212;Database=CARESYS;User Id=sa;Password=CARESYS;" name="CaresysSTG">
<!--My staging package for the color table -->
<package autocreateconfigurationstype="None" constraintmode="Parallel" name="CaresysSTG" protectionlevel="DontSaveSensitive">
<!--The tasks of my control flow: truncate table and a data flow task-->
<!--Execute SQL Task to truncate the staging table-->
<executesql connectionname="CaresysSTG" name="SQL - Truncate cs_clients" resultset="None">
<directinput>Truncate table cs_clients</directinput>
<!--Data Flow Task to fill the staging table-->
<dataflow name="DFT - stage cs_clients">
<!--Connect it to the preceding Execute SQL Task-->
<input outputpathname="SQL - Truncate cs_clients.Output"/>
<oledbsource connectionname="CARESYS" name="OLE_SRC - cs_clients">
<directinput>SELECT Code, Name FROM Color</directinput>
<!--My destination with no column mapping because all source columns exist in destination table-->
<oledbdestination connectionname="CARESYSSTG" name="OLE_DST - cs_clients">