Call Stored Procedure in SSIS via OLEDB Source with multi value parameter

0
votes

I am trying to call a stored procedure that has 3 input parameters. The stored proc has multiple joins, populates multiple temp tables etc. I need to call this SP from one database and populate another database table in SQL server. I am trying to use OLEDB Source SQL Command

I have also created 3 variables in SSIS package(same variables are in stored proc parameters)

     @date string
     @productType string
     @Flag int32

      Exec getDetails
       ?,?,?

also mapped parameters tab
Parameter-Date variables:user::Date
ProductType-variables:user::ProductType
Flag -variables:user::Flag

First parameter is a date field-get specific date data second parameter-product type that has 20 different possible values(we want to pull all product types for one date ) third -just a value either 1 or 0. I also need to map columns from SP to destination table. some columns are unnecessary from sproc and mapping should be done. I am getting error as

Exception-HRESULT:0Xc020204A An OLEDB record is availab.e-Source Microsoft SQL Sever Native Client 11.0 HResult-0*80004005

Error at data flow task(OLEDB Source)-Unable to retrieve column information from the data source. Make sure your target table in database is available. Need help loading data from this stored proc to a destination table.

2
visual-studio-2015ssissql-server-data-tools
Have you tried to hard code the parameters to see if its an issue with the variables themselves? - Ian-Fogelman
In ssms i am able to execute sproc as exec getDetails’20180705’,’apple;orange;mango;pear;cherry’, 1 dont want to add all 20 values here. How do i assign all the possible 20 values to a parameters in ssis? Should u seperate with commas or semi colon? Single quote is not required if i recall - Brita
What about when you hardcore them in ssis? - Ian-Fogelman
Will try with hard coding all possible values and run - Brita
I googled your error and this was the first response: social.msdn.microsoft.com/Forums/expression/en-US/…. It's possible your SP doesn't return columns, or has inconsistent columns returned. If your SP has a few different SELECT statements in it, or if it selects from a temp table. this might be the issue. Please clarify - Nick.McDermaid

2 Answers

0
votes

Declare @date string, @productType string, @Flag int32

set @date = ? set @productType = ? set @Flag = ?

Exec getDetails @date, productType, @flag

Please make sure map the variable as in same sequece

0
votes

Thanks for all your response. Step1 I actually used a different method creating another stored proc and called the other sp(called sp cant be modified). Created a temp table, inserted result of called sp into a temp table and populated the result sets of temp into target table. - passed possible values to productType, flag was passed value as 1, and date was selected as max Step 2-created SSIS package, used execute sql task to call sproc

It worked