SSIS For Loop Container with Date Variable

Question

I want to create a monthly package that executes a daily query at ODBC and writes an output file.

More specifically the query must be first executed for the first day of the previous month (e.g. '01/11/2018') then the next one ('02/11/2018') until the last day of the previous month ('30/11/2018').

The date variables are currently saved as Strings and I also want to have a string variable with Oracle date format to be inserted into the query. How should it be organised? Is there a way that I could use the string variables in the expressions?

Answer 1

Break it into parts as follows:

1. Declare variables to store previous month start and end date as follows:

start_date(datetime) = (DT_DATE)((DT_WSTR,4)YEAR(DATEADD("MM",-1,GETDATE()))+"-"+RIGHT("0"+(DT_WSTR,2)MONTH(DATEADD("MM",-1,GETDATE())),2)+"-01")

end_date(datetime) = DATEADD("D", -(DAY(GETDATE())),GETDATE())

2. Declare variable Counter(datetime)

3. Create a For loop container as follows :

4. Rest of the Data Flow Task should be there within For loop container, which will create output file. You can use the variable Counter in SQL to parameterize it