none
pull data from multiple remote SQL Server data source

    Question

  • Hi, I am doing a data warehouse project for my employer. Basically I need to perform this job periodically (once a day):

    For each remote SQL Server in the list:

        Read its connection string from a configuration table.

        Connect to it.

        Execute a SQL statement on it to retrieve a recordset.

        Save the recordset into a staging table on the data warehouse database.

        Load the data into a fact table on the data warehouse database.

    Next --iterate to the next SQL Server instance.

    My question is, seems to me it is easier to implement such job by creating a standalone C# program? If use SSIS, I have to create a package for each possible remote SQL Server instance (not what I want) and invoke each package one by one? What is the best way to handle such a dynamic job in SSIS? Thanks in advance!

    Wednesday, June 26, 2013 10:50 PM

Answers

  • Hi

    As you told you can do it in 2 way

    1.  using C# Code

    2. Using foreach loop container and using dataflow task inside that to perform the data transfer operations.

    Better way i suggest is to use Foreach loop container and DFT. because DFT performs better data transfer and fast load options when compared to C# code.

    Regards

    Naveen

    Thursday, June 27, 2013 6:33 AM
  • SSIS is going to perform much better than C# (Whole point of creating SSIS at the first place, otherwise we can anytime write C# codes to perform even complex ETL).

    You do not have to create separate packages for each source. You can handle this very easily in SSIS in either of  the following ways:

    1. Create multipe Data Flow Tasks - one for each source (ofcourse in just one package)

    2. Store all database connections, source table name, destination name in a table. Then in SSIS package use a object variable and put all the values of the table in this variable. Use one single Data Flow Task in side a ForEach Loop Container, which would read all sources and put data in curresponding destinations (Provided the table metadata does not change).

    Once you learn SSIS, you would find this to be a very basic thing which can be handled by SSIS.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh


    Thursday, June 27, 2013 8:10 AM

All replies

  • SSIS is an ETL tool and do all steps that you mentioned in the above query.<o:p></o:p>

    It basically matter of choice what you are more comfortable in implementing it. <o:p></o:p>

    It not always we need to create a separated package for all the remote connection, we store the connection string in Sql table and dynamically change at the runtime for a connection property.(Refer this link - > http://kevine323.blogspot.in/2012/04/dynamic-connection-strings-in-ssis.html)<o:p></o:p>

    For dynamic job(i am not sure what does that means, can you please explain the dynamic part here?)  you can always call the ssis package from window scheduler and using sql agent job and configure them according to your use.<o:p></o:p>



    Regards Harsh

    Thursday, June 27, 2013 6:09 AM
  • Hi

    As you told you can do it in 2 way

    1.  using C# Code

    2. Using foreach loop container and using dataflow task inside that to perform the data transfer operations.

    Better way i suggest is to use Foreach loop container and DFT. because DFT performs better data transfer and fast load options when compared to C# code.

    Regards

    Naveen

    Thursday, June 27, 2013 6:33 AM
  • SSIS is going to perform much better than C# (Whole point of creating SSIS at the first place, otherwise we can anytime write C# codes to perform even complex ETL).

    You do not have to create separate packages for each source. You can handle this very easily in SSIS in either of  the following ways:

    1. Create multipe Data Flow Tasks - one for each source (ofcourse in just one package)

    2. Store all database connections, source table name, destination name in a table. Then in SSIS package use a object variable and put all the values of the table in this variable. Use one single Data Flow Task in side a ForEach Loop Container, which would read all sources and put data in curresponding destinations (Provided the table metadata does not change).

    Once you learn SSIS, you would find this to be a very basic thing which can be handled by SSIS.


    Let me know if you have any more questions on this. Please mark this post as an answer if it helps you resolve your question. - Bhawesh


    Thursday, June 27, 2013 8:10 AM
  • Thanks for all the replies. I think SSIS is good enough for me!
    Saturday, June 29, 2013 12:00 AM