Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Multiple Ole db sources to a single flat file...
Add to briefcase
11 posts, Page 1 of 2
12»»

Multiple Ole db sources to a single flat file destination Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sunder.mekala
Posted Monday, February 18, 2013 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26
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.
Post #1421248
Andy Hyslop
Posted Monday, February 18, 2013 9:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 595, Visits: 2,143
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
Post #1421252
Phil Parkin
Posted Monday, February 18, 2013 9:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494
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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1421257
Andy Hyslop
Posted Monday, February 18, 2013 9:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 595, Visits: 2,143
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
Post #1421260
sunder.mekala
Posted Monday, February 18, 2013 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26
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?
Post #1421343
sunder.mekala
Posted Monday, February 18, 2013 1:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26
Sorry in the first statement i said there are keys , But they are no keys to join i mean no relationship between the tables.
Post #1421344
opc.three
Posted Tuesday, February 19, 2013 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421599
sunder.mekala
Posted Tuesday, February 19, 2013 10:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26
FYI, No Sorting requirements, and flat file formatting is delimeter, Thanks.
Post #1421752
Phil Parkin
Posted Tuesday, February 19, 2013 10:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494
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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1421760
sunder.mekala
Posted Tuesday, February 19, 2013 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26
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.
Post #1421782
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.