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.

Answer 1

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:

Answer 2

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.