Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple Ole db sources to a single flat file destination Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:28 PM
Points: 16, Visits: 48
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
Posted Monday, February 18, 2013 9:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 689, Visits: 2,756
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
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: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421257
Posted Monday, February 18, 2013 9:16 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:31 AM
Points: 689, Visits: 2,756
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
Posted Monday, February 18, 2013 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:28 PM
Points: 16, Visits: 48
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
Posted Monday, February 18, 2013 1:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:28 PM
Points: 16, Visits: 48
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
Posted Tuesday, February 19, 2013 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1421599
Posted Tuesday, February 19, 2013 10:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:28 PM
Points: 16, Visits: 48
FYI, No Sorting requirements, and flat file formatting is delimeter, Thanks.
Post #1421752
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: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421760
Posted Tuesday, February 19, 2013 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:28 PM
Points: 16, Visits: 48
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 12»»

Permissions Expand / Collapse