import several access tables to temp file in ssis.
Posted on 2011-02-19
I am setting up an ssis package. I have 4 tables in an access database for import into one sql table. Insert data from each table (some columns will be different on each table) into a temp table - create aggregate on some columns - insert the aggregated data into the temp table, then export all to the main table in SQL.
So far, I've set up a for each loop container and set up a connection to the database that contains the files with a variable filename and added this variable to the container. Then, added the dataflow task for each table in the for loop container. I would then added an execute sql task to each dataflow task in the container to get the specific data into the temp table (I did not try this yet - guess I would create the temjp table first, then append to it).
This is where I am stalled. For each access table importing, I need to count lines per group number and sum column values, then insert the computed columns to the temp table then when all is done, export the finished temp table to the SQL table. Any suggestions /examples would be helpful since I am new to ssis. Am I going about this the right way? Need help with this asap.