none
Need Help in Data Extraction

    Question

  • Hi All,

    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.

    Regards

    Mus

    Tuesday, October 22, 2013 10:43 AM

All replies

  • 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.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Amit C A Wednesday, October 23, 2013 5:47 AM
    Tuesday, October 22, 2013 11:08 AM
  • Thanks for your suggestion.

    So you are telling  I can reuse the same package only if the metadata of the table are same. otherwise we cant use the same package. :(

    Regards

    Mus

    Tuesday, October 22, 2013 11:12 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.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, October 22, 2013 11:30 AM
  • Hi ,

    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

    Wednesday, October 23, 2013 5:06 AM
  • Hi ,

    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

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, October 23, 2013 5:53 AM
  • here is the error which I am getting while compiling.



    this is the code which I am using


    <biml xmlns="http://schemas.varigence.com/biml.xsd">
        <!--Package connection managers-->
        <connections>
            <oledbconnection connectionstring="Provider=SQLOLEDB;Server=CARESYS\MSSQL20212;Database=CARESYS;User Id=sa;Password=CARESYS;" name="CARESYS">
            </oledbconnection>
            <oledbconnection connectionstring="Provider=SQLOLEDB;Server=CARESYS\MSSQL20212;Database=CARESYS;User Id=sa;Password=CARESYS;" name="CaresysSTG">
            </oledbconnection>
        </connections>

        <packages>
            <!--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-->
                <tasks>

                    <!--Execute SQL Task to truncate the staging table-->
                    <executesql connectionname="CaresysSTG" name="SQL - Truncate cs_clients" resultset="None">
                        <directinput>Truncate table cs_clients</directinput>
                    </executesql>

                    <!--Data Flow Task to fill the staging table-->
                    <dataflow name="DFT - stage cs_clients">
                        <!--Connect it to the preceding Execute SQL Task-->
                        <precedenceconstraints>
                            <inputs>
                                <input outputpathname="SQL - Truncate cs_clients.Output"/>
                            </inputs>
                        </precedenceconstraints>

                        <transformations>
                            <!--My source-->
                            <oledbsource connectionname="CARESYS" name="OLE_SRC - cs_clients">
                                <directinput>SELECT Code, Name FROM Color</directinput>
                            </oledbsource>

                            <!--My destination with no column mapping because all source columns exist in destination table-->
                            <oledbdestination connectionname="CARESYSSTG" name="OLE_DST - cs_clients">
                                <externaltableoutput table="cs_clients"></externaltableoutput>
                            </oledbdestination>
                        </transformations>
                    </dataflow>
                </tasks>
            </package>
        </packages>
    </biml>



    Wednesday, October 23, 2013 6:49 AM
  • Sorry the error image was not displayed.

    "http://schemas.varigence.com/biml.xsd:biml" element is not declared.

    Wednesday, October 23, 2013 6:51 AM