Post reply

Multiple Oledb sources Vs Multiple Flat file destinations

  • April 8, 2011 at 3:25 am

    #248682

    Hi All,

    I am new to SSIS,

    I got a problem, and have a hope to get answer here in this forum,

    We have Multiple Oledb Sources and after joining them based on the condition they have to be stored in different flat files as per the condition.

    I will elaborate the problem a little more,

    we have to tables say Emp and Dep

    Emp:

    Eid Ename Dno

    1 ravi 20

    2 hari 10

    3 nari 30

    Dep:

    Did Dname Dno

    1 HR 10

    2 IT 20

    3 BPO 30

    Now i have to load the tables into a flat file destination such that the flat file should have a dynamic name,

    Flat file name: HR.txt

    content in the flat file:

    Ename Dname

    Hari HR

    Thanks in advance.

    Best regards,

    Dharani

  • April 11, 2011 at 12:26 am

    #1309674

    If all the tables are in the same SQL Server, write a TSQL query that joins all those tables together and retrieve the data you need in one single OLE DB Source.

    If they are on seperate servers, use multiple OLE DB Sources. Each SELECT statement in the source has an ORDER BY query. In the advanced editor of the sources, set the IsSorted property to true. Then use a MERGE JOIN in the dataflow to join the sources. (this component needs sorted inputs, hence the IsSorted properties).

    Finally, write the data to a Flat File Destination. Use an expression on the connection mananger to dynamically set the filename.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply