Multiple Ole db sources to a single flat file destination

Question

Multiple Ole db sources to a single flat file destination

sunder.mekala

SSC Eights!

Points: 850
More actions
February 18, 2013 at 8:49 am

#289483

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.

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

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

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

sunder.mekala SSC Eights! Points: 850 More actions · Answer 4

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?

sunder.mekala SSC Eights! Points: 850 More actions · Answer 5

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

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

sunder.mekala SSC Eights! Points: 850 More actions · Answer 7

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 8

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sunder.mekala SSC Eights! Points: 850 More actions · Answer 9

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.

Phil Parkin SSC Guru Points: 246987 More actions · Answer 10

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel