question

tredd68 avatar image
tredd68 asked

Multi-step OLE DB generated errors

I have built a SSIS package that imports a flat file to a SQL table as follows: 1. SQL Task to Create a table in SQL. 2. Data Flow task that pumps the data from a flat file into this SQL table. 3. SQL Task that adds new columns to the SQL Table. 4. SQL Task that executes a stored proc. I am using parameters in SSIS to populate some variables or fields in my SQL Table. The user will open the variables window under SSIS and input values for the variable "When" and "Where". He/she then kicks off the package. It works fine if I am just passing 1 parameter to the stored proc but if I attempt to pass 2 parameters to the stored proc, I get an error stating: Multi-step OLE DB generated errors. I have googled all over and have found similar issues but not quite a fix for my issue. The variables are Strings with Input and 0, -1.
ssisparameters
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I acually ended up setting the Parameter Name column for first variable to @When and second variable to @Where. It now works.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
How you pas those parameters to the stored proc and how you refer them when assigning variables in the assign variables tab. FOR OLE DB you have to pass the variables as question marks e.g. `EXEC myProc ?, ?`. Then in the "Assign Variables" as parameter name you have to specify the number (position) of the parameter starting from 0. Numbers are increasing from left to right and from top to bottom. So first parameter name is 0 and second one 1.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HelloFOFO avatar image
HelloFOFO answered
Once, I got a similar situation. The param cannot be copied,u must new a param. Maybe this helps , or not
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.