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

ssis data flow transform question Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 9:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:44 AM
Points: 288, Visits: 578
Hi
I have one large table in one database and i am am going to move the data and turn it into a normalised relational database with a number of tables.

As all the data is coming from a single table is there a way of just importing it once (say into a recordset or something) and then running other data flow tasks from that recordset?

and is there a pause transform that will wait till one item is finished before starting?

Thanks in advance
Post #1369163
Posted Friday, October 5, 2012 10:40 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:14 AM
Points: 405, Visits: 1,138
your best solution would be to create multiple dataflows in the control flow area of your package.

create your first dataflow to move the data to the single table on your new server.

the create the remaining data flows within a sequence container and use the control flow expressions to completetion to ensure your data flows then execute in the correct order .....

alternatively, you may just want to have a second data flow reading of the single table and use conditional splits to split the data into logic paths........

(I don't know your data or it's complexity but is an alternative). Hope that helps


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1369195
Posted Friday, October 5, 2012 1:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 5,446, Visits: 7,616
You might want to look into the use of .RAW files in SSIS. They're basically SSIS's version of temp tables. What they do is pull off a single system into a file (local to the SSIS server is best) in a high-speed access version of the data (you won't be able to read it directly).

That raw can then be manipulated repeatedly. My personal use for it is usually when dealing with multiple warehouses, where I need to do heavy transforms and then port the results to multiple targets. First I'll do all the transforms into the raw, and then start pumping it out to the target systems. The reason for this is so I can checkpoint the transformation, keeping me from having to hit the source system more than once in case of a connection failure or somesuch.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1369280
Posted Monday, October 8, 2012 2:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:44 AM
Points: 288, Visits: 578
thanks very much for the replies guys

ill look into both methods
Post #1369673
Posted Monday, October 8, 2012 7:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:15 PM
Points: 3,148, Visits: 794
You can also use a multicast to direct the recordset to multiple Destination objects in one Data Flow.

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1369797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse