SSIS For Loop ODBC

Question

I’m extracting data from a table in Oracle.

I have an ODBC connection manager to the Oracle database and the query for extraction should include a where clause because the table contain transactional data and there is no reason to extract it all every time.

I want initialize the table once and do it in with a For Loop which will iterate the whole table.

Since it’s an ODBC connection I can’t just put a where clause because I need to use a variable hence I realized I need to parameterize the DataFlow task and write my query at the sqlcommand property containing the ODBC source. The property value is:

SELECT * 
FROM DDC.DDC_SALES_TBL 
WHERE trunc(CALDAY) between to_date('"+ @[User::vstart]+"','MM/DD/YYYY') 
and  to_date('"+ @[User::vstop]+"','MM/DD/YYYY')

Where the @vstart and @vstop are variables containing the ‘from/to’ dates to be extracted based on a DATEADD function and another variable (@vcount) which supposed to be the iterator as follows:

(DT_WSTR, 2) MONTH( DATEADD( "day", @[User::vcount] , GETDATE()  )  )+"/"+
(DT_WSTR, 2) DAY( DATEADD( "day", @[User::vcount] , GETDATE()  )  )+"/"+
(DT_WSTR, 4) YEAR( DATEADD( "day", @[User::vcount] , GETDATE()  )  )

What’s happening is that the first iteration works fine but the second one generates an error and the package fails.

I marked the variable as EvaluateAsExpression=True

I also marked the DelayValidation=True in both the For Loop and the DataFlow tasks.

The errors are:

(1)Data Flow Task:Error: SQLSTATE: HY010, Message: [Microsoft][ODBC Driver Manager] Function sequence error; (2) Data Flow Task:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "ODBC Source.Outputs[ODBC Source Output]" failed because error code 0xC020F450 occurred, and the error row disposition on "ODBC Source" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (3) Data Flow Task:Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ODBC Source returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Please assist.

Can you put a breakpoint on the foreach loop container and check the value of the variables before the beginning of each loop? — Mark Wojciechowicz
I'm reading from a remote machine. Due to the COVID-19 issue I'm working from home and i can't execute the package via VS directly, but I'm deploying it to SQL server and run in from the integration services catalog — Rabers
By the way, i guess that the variable does increase as stated at the loop definition. What i don't know is why the new variable value, that should update the variable accordingly doesn't affect the DataFlow sqlcommand property which depends on the variable — Rabers
i was able to execute it via VS. used a breakpoint and the loop works fine. the first iteration goes well, then the value of the variable updates BUT it fails. the 'sqlcommand' i putted in the DataFlow property doesn't show when i debug it — Rabers
Can you put the sql command in a variable and then set the dataflow with that variable. You should then be able to see the command — Mark Wojciechowicz

Rabers Rabers · Accepted Answer · 2020-04-24T21:35:14

I don't know why initially i didn't use OLEDB, as I thought it doesn't work. What i tried was to use create an OLEDB via oracle driver and the connection manager worked so i used it. As this way you can parameterize the source directly and the loop worked just fine. Don't know what cause the conflict with the OBDC source but that's my workaround. I didn't find a way to setup the sqlcommand property in ODBC source and using it in a loop which should change the the command every iteration. It crashed after the first iteration ni matter what i tried.

Thanks,

SSIS For Loop ODBC

2 Answers