SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Ole db sources to a single flat file destination


Multiple Ole db sources to a single flat file destination

Author
Message
sunder.mekala
sunder.mekala
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
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.
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)

Group: General Forum Members
Points: 977 Visits: 3037
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18109 Visits: 20388
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)

Group: General Forum Members
Points: 977 Visits: 3037
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
sunder.mekala
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
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
sunder.mekala
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
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
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14799 Visits: 14396
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
sunder.mekala
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
FYI, No Sorting requirements, and flat file formatting is delimeter, Thanks.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18109 Visits: 20388
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
sunder.mekala
sunder.mekala
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 55
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search