blasto_max (10/30/2013)
Matt Miller (#4) (10/30/2013)
Two ways to do this:1. the SSIS route - use the flat file connector to make SSIS aware of the generic content. then use a conditional split based on column1 to define out multiple mappings to multiple destinations based on your column1 values.
2. SSIS + SQL route. still use the flat file to dump the data into staging (which matches the generic input columns you have). Then run a series of INSERT statements which filter based on the value of column1, e.g.
Insert into MyDest19 (col1, col2, col3, col4, etc....)
Select col1, col2, col3, col4, etc....
from mystagingtable
where column1='Dest19'
can't i just somehow execute my big .SQL files instead ?
I suppose it's possible with enough RAM and a idle server, but it's very inefficient and will always be subject to running into resource issues like you've already seen when you have a big day or something changes. You could possibly look at using a flat file OUTPUT and devise some custom streaming solution to build up the SQL statement in a file rather than memory but at that point you really aren't using SSIS at all.
Just for giggles I dummied up a version of option 1. above. It took about 6 minutes to wire it up, 6 more minutes to shake out a few data type bugs. My first run of a 5.2M row (600MB) file took 53 seconds to load into 2 tables based on a filter, and that's before I've done any tweaks to turn on bulk inserts. Single row inserts will be running for literally hours on that kind of operation.
SQL Server is designed to perform best on set operations (i.e. bunches of rows at a time). If you can design your process in that way - you will be better off in the long run.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?