How to Loop (ForEach) thru "Copy data" and execute Stored Procedure once?

Question

I am using a variable with Array to insert multiple csv files using "ForEach".

Currently, I am testing with 1 and 2 as a Default value.

I was able to get the "ForEach" working, but now the issue is, because my other activities (CopyData and Stored Procedure) are all inside ForEach, it copies data two times (because I have 1 & 2 as a Default value).

This is current two activities inside "ForEach":

1. The "CopyData", basically, brings data from a csv file and inserts data into a Staging table.

It truncates data inside the Staging table first before inserting data from each csv file.

2.The "Stored Procedure" inserts data from the Staging table into Production table.

So the dilemma is that if I locate the Stored Procedure activity outside the ForEach, it only inserts data of one of two csv files.

How do I set up Activities properly so that it would not insert data by the number of files (this time two) and put data into Production table at once/properly (not twice nor only data of one csv file)?

Thanks.

Answer 1

Hey,

1. use a script task outside the for each activity to truncate the staging data
2. within for each, within copy activity no need to use a pre copy script and let all the files data get appended within the staging data
3. Use the Sproc activity outside ,post foreach to have a 1 time load from staging to destination table