CODEWITHSUNDEEP
sql-serverssisparallel-processing
Martin Isaksen
Martin Isaksen

Reputation: 130

SSIS - Parallel execution of same package

I'm trying to speed up my ETL time by running the same package in parallel but I'm having issues finding a good way to save the data to destination.

SETUP:

  1. Order table in OLTP.
  2. Fact table partitioned by month.
  3. Need PostedDate (time when order was paid for) and ShipDate.
  4. Order can ship the following month.
  5. Since this is a new datawarehouse, I am trying to do each month in parallel to build the historical data.
  6. Using SSIS 2014.

GOAL:

  1. An ETL process that runs in parallel for each month of data extracted from Order table.

WHAT I HAVE DONE SO FAR:

  1. Created a stored procedure that loops through each month.
  2. The SP Creates a staging table for that period dynamically with a unique name.

    StagingTable_'Month'

  3. It then creates and executes an SSIS package instance of my ETL process.
  4. In the package I am trying to save the data to the correct staging table, but I cannot find a way to do this dynamically.

The idea was to be able to create each month in parallel and then switch each partition in all at once. Instead of doing it in bulks because of memory capacity.

Anyone have any ideas?

Upvotes: 1

Views: 1208

Answers (1)

Rachel Ambler
Rachel Ambler

Reputation: 1594

Create a user variable within a container to store the destination table name then use an expression in your destination to use this variable instead of the table name itself.

Upvotes: 1

Related Questions