Execute Stored Procedure with parameters in SSIS OLE DB Source component

Question

I want to execute a Stored Procedure that requires parameters as an OLE DB source, to export it to an Excel file from SSIS.

The SP generates a set of data that I would like to export to the Excel file.

This is the code that I run for the SP. (running it like this produces the result I want)

DECLARE @RC int
DECLARE @startweek varchar(20)
DECLARE @endweek varchar(20)
DECLARE @payroll varchar(30)
DECLARE @job varchar(25)
DECLARE @job_to varchar(25)
DECLARE @manager varchar(30)
DECLARE @office varchar(100)
DECLARE @pu varchar(6)
DECLARE @pu_to varchar(6)
DECLARE @task varchar(25)
DECLARE @task_to varchar(25)
DECLARE @Prj_pu varchar(6)
DECLARE @Prj_pu_to varchar(6)

SET @endweek = dateadd (
    d
    ,-((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7)
    ,getdate()
    )

SET @startweek = DATEADD(WEEK, -25, @endweek)

EXECUTE @RC = dbo.TIME_lynx_extract
   @startweek
  ,@endweek 
  ,@payroll
  ,@job
  ,@job_to
  ,@manager
  ,@office
  ,@pu
  ,@pu_to
  ,@task
  ,@task_to
  ,@Prj_pu
  ,@Prj_pu_to

I'm not sure if the formatting for the run is the proper one though.

This is a picture of the setup:

These are the errors of the yellow background section:

Exception from HRESULT: 0xC020204A

Error at Data Flow Task [OLE DB Source [37]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXECUTE SP_EXECUTESQL @STR_SQL' in procedure 'TIME_lynx_extract' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.".

Error at Data Flow Task [OLE DB Source [37]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Also, if I try executing basic queries on the OLE DB source there it works, so the connections with database seems to be ok.

The main problem now is how to execute this SP correctly within SSIS.

IN regards to the problem, there are errors at the bottom of the print screen, which you haven't included in your question. We really need those. — Thom A
– Thom A ♦, Commented Aug 20, 2019 at 9:55
And that one also tells you the problem... I'll emphasis the parts: ""The metadata could not be determined because statement 'EXECUTE SP_EXECUTESQL @STR_SQL' in procedure 'TIME_lynx_extract' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set."." — Thom A
– Thom A ♦, Commented Aug 20, 2019 at 10:32
Check the following answer it may helps stackoverflow.com/questions/48083262/… — Hadi
– Hadi, Commented Aug 20, 2019 at 11:23
I would suggest this will help you: stackoverflow.com/questions/7610491/… — Will
– Will, Commented Aug 20, 2019 at 11:30

digital.aaron · Accepted Answer · 2019-08-20 15:40:55Z

4

It's not too difficult to use parameters with your EXEC in the DFT.

First, you'll want to make sure you have SSIS variables for all of your input parameter values.

Specifically, make all of these [User::{var_name}] and populate them (populating the values is out of the scope of this answer):

DECLARE @startweek varchar(20)
DECLARE @endweek varchar(20)
DECLARE @payroll varchar(30)
DECLARE @job varchar(25)
DECLARE @job_to varchar(25)
DECLARE @manager varchar(30)
DECLARE @office varchar(100)
DECLARE @pu varchar(6)
DECLARE @pu_to varchar(6)
DECLARE @task varchar(25)
DECLARE @task_to varchar(25)
DECLARE @Prj_pu varchar(6)
DECLARE @Prj_pu_to varchar(6)

Also, set your @endweek and @startweek in an Execute SQL Task before your DFT.

Now that you have all of your SSIS variable prepared, go back into your OLE DB Source task, and edit your query. You'll want to remove the return variable, and change all of your input parameters, like this:

EXECUTE [dbo].[TIME_lynx_extract] ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
WITH RESULT SETS(
(
worker_reference NVARCHAR(50),
placement_reference NVARCHAR(10),
worker_name NVARCHAR(50),
job_title NVARCHAR(100),
authorising_line_manager NVARCHAR(100),
timesheet_date DATETIME,
company_agent_name NVARCHAR(100),
type_of_worker NVARCHAR(100),
week_number NVARCHAR(10),
hours_worked NVARCHAR(10),
rate_description NVARCHAR(100),
rate_per_hour NVARCHAR(10),
job NVARCHAR(50),
work_stage NVARCHAR(100),
project_name NVARCHAR(100),
location NVARCHAR(100)
))

Once you do that, hit the Parameters button next to the query window. SSIS should prepopulate the list with Parameter0 through Parameter12. What you want to do is go through and change all those ParameterX names to your input parameter names. Then choose the corresponding SSIS variable to use for each parameter.

Here's what my Set Query Parameters window looks like when I do this for my test proc:

answered Aug 20, 2019 at 15:40

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baldie47 Over a year ago

With this I was able to make it work, I'm now generating the xlsx file that I needed :) just need to figure out how to set password on it and send it over e-mail, but thank you! this takes me a lot closer!

Arpit Chinmay Over a year ago

@Baldie47 Do you mind explaining what are all those variables you've used inside WITH RESULT SETS(). Are those the column header's for the table returned?

Baldie47 Over a year ago

No, those are the parameters you need to pass to the stored procedure (that one required a lot of them, that's why it looks crowded) all those params come from SSIS from previous steps.

renaissanceMan · Accepted Answer · 2021-09-15 22:03:30Z

Ok, I'm going to answer this for Execute SQL Task task in ssis. These examples worked in VS 2017 Enterprise. You might think that when you add Execute SQL Task the syntax for ResultSet and Parameters would be the same, regardless of how you connect to the database, but it isn't. If you use an OLEDB database connection to ms sql you use one syntax, and if you use ADO.Net you use another syntax for the Parameter's and the ResultSet.

If you are using OLEDB to connect to MS Sql and Single row ResultSet:

Your Parameter is Input and you specify 0 as the Parameter name. You can have a Parameter length. Then your MS Sql is: set @myvar = (select mycol from mytable where xwherecol = ?) The question mark ? is where your parameter substitutes in. If you have multiple Input Parameters just load one in each row of Parameter Mapping with ParameterName's of 0,1,2,... and then use multiple question marks.

OLEDB ResultSet will take a named result so set your ResultName to xxxx and then in your final ms sql select from Execute SQL Task:

select @myvar as xxxx and @myvar will be placed in the xxxx ResultSet variable

If you are using ADO.Net to connect to ms sql and Single row ResultSet:

Your Parameter is Input and here you specify the name of your parameter (I'm going to use xyz (NO @) for my parameter name in the Parameter Mapping pane). Your MS Sql is:

set @myvar = (select mycol from mytable as xwherecol = @xyz) and you don't declare @xyz in the ms sql for your Execute SQL Task

For ResultSet, ADO.Net won't take named ResultSet's, so in ResultSet your ResultName is just 0,1,2 ... for each result that you want to return. Then your final select will just assign your selected column to the ResultSet in the order you listed them.

Not intuitive but I hope this helps. This example assumes you are connecting to ms sql, if you were connecting to Oracle or Teradata I'm not sure if what I describe above would work. The parms and the resultset might work, the sql syntax obviously would be different.

Collectives™ on Stack Overflow

Execute Stored Procedure with parameters in SSIS OLE DB Source component

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related