Multiple Ole db sources to a single flat file destination

  • Hello friends,

    i have requirement where i have take data from different tables of sql server db, but here the important thing is tables doesnt have any relationship among them, not even one, as they are just logging tables of packages, so i thought of using multiple ole db sources, with one ole db source having data from one table and repeat for others.

    Here i have 5 tables with different columns in each.

    i am not sure how to load that into a single flat file or excel file destination.

    Your help is much apprecaited, Thankyou.

    --

    Sam.

  • sunder.mekala (2/18/2013)


    Hello friends,

    i have requirement where i have take data from different tables of sql server db, but here the important thing is tables doesnt have any relationship among them, not even one, as they are just logging tables of packages, so i thought of using multiple ole db sources, with one ole db source having data from one table and repeat for others.

    Here i have 5 tables with different columns in each.

    i am not sure how to load that into a single flat file or excel file destination.

    Your help is much apprecaited, Thankyou.

    --

    Sam.

    Only a guess as I can't see your structures however if they are just log's can you not do something like this in your data source and then pass the data set to the destination table?

    Andy

    SELECT

    COL1

    ,COL2

    ,COL3

    ,COL4

    ,COL5

    FROM

    TABLE1

    UNION ALL

    SELECT

    COL1

    ,COL2

    ,COL3

    ,COL4

    ,COL5

    FROM

    TABLE2

    UNION ALL

    SELECT

    COL1

    ,COL2

    ,COL3

    ,COL4

    ,COL5

    FROM

    TABLE3

    -- Ect --

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Are you sure that having a single flat file containing mixed data would be an acceptable solution? Sounds like a real mess to me, but, funnily enough, I had to code something similar last week.

    I did it using a single column flat file which was populated in multiple asynchronous script component transformations. I handled the column conversions (.ToString() etc), concatenations and delimiters to produce a single output column from each script component.

    Sounds complex? Maybe a little, but I am very thankful that I was asked to write this file rather than read it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Are you sure that having a single flat file containing mixed data would be an acceptable solution

    Depends Phil, the tables may have no relationship but may contain data that does have a relationship..

    If they are just for logging these columns may just contain auditing such as last update, incomming row count ect that could be put into a table without any issues of mixed data.. 🙂

    Could do with some clarification really on the structure

    Andy

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • i have 5 logging tables with keys to join like

    Table 1:

    Columns : A,B,C

    Table 2:

    Columns : D,E,F,G

    Table 3:

    Columns : H,I,J,k,l

    Table 1:

    Columns : M,N

    Table 1:

    Columns : A1,B1,C1

    i want to pick only few columns from the above tables which have no relationships and no same columns to do union all, so here i am thinking to have different ole db sources and then they can be loaded into one flat file?

  • Sorry in the first statement i said there are keys , But they are no keys to join i mean no relationship between the tables.

  • Any sorting requirements? If each line in the file could have a different number of fields then what format should each line in the file be in, e.g. comma-delimited, tab-delim...?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • FYI, No Sorting requirements, and flat file formatting is delimeter, Thanks.

  • sunder.mekala (2/19/2013)


    FYI, No Sorting requirements, and flat file formatting is delimeter, Thanks.

    I don't know what you mean when you say "..flat file formatting is delimeter," but from what I can gather, it seems that my initial suggestion would still give you what you need.

    I think that this would also work - and if your coding skills are not up to it, you might prefer it:

    1) Create five data flows to create 5 separate flat files. These could run in parallel in your package, in a sequence container.

    2) Create a final execute process task which runs a batch file.

    3) In the batch file, write a simple copy statement along the following lines:

    copy file1.txt+file2.txt+file3.txt+file4.txt+file5.txt allFiles.txt

    Now your allFiles.txt file contains all output from the other files.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Just to make calrify, my requirement is i have 5 ole db sources that i have load it into a SINGLE flat file

    why 5 ole db sources?

    As i dont have any relation between the tables (they are just logging tables) i have to go for 5 different ole db sources (sql server tables with different columns in each table).

    Thanks,

    Sam.

  • sunder.mekala (2/19/2013)


    Just to make calrify, my requirement is i have 5 ole db sources that i have load it into a SINGLE flat file

    why 5 ole db sources?

    As i dont have any relation between the tables (they are just logging tables) i have to go for 5 different ole db sources (sql server tables with different columns in each table).

    Thanks,

    Sam.

    What is wrong with my proposed solutions?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 11 posts - 1 through 10 (of 10 total)

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