Reccurent SSIS SQL Task with for loop

yosafat saragih 21 Reputation points
2021-03-01T07:28:39.193+00:00

I want to loop through date said 2000-01-01 to 2000-12-31 and i put the date as variable (said start and stop). I want to recursively call insert query which structured like this:

FOR date in range (start,stop):
INSERT INTO TABLE A (SELECT ...)
ON CONFLICT (CONSTRAINT)
DO UPDATE (...)
WHEN
DATE + INTERVAL 1 DAY

I wonder if i can use SSIS Loop container and loop through day using this.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,437 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-01T09:23:08.463+00:00

    Hi @yosafat saragih ,

    We can store date and sql query in variables.

    Then we can use variable in For Loop and set SQLSourceType as Variable in Execute SQL Task.

    Please refer to the following links and pictures:
    1.SSIS For Loop Container with Date Variable
    2.Parameter SQL Insert into Statement SSIS Package

    72963-cf.png
    72917-forloop.png
    72918-est.png
    72919-expressionbuilder.png

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.