Execute Stored Procedure with parameters in SSIS as OLE DB Source

Question

I want to execute an Stored Procedure that requires parameters as an OLE DB source to later export it a 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
GO

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 to get to execute this SP.

Solution 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:

Solution 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.

Comments

[Larnu]

It makes sense that the statement can't be prepared; you don't set the values for any of those parameters. I bet if you tried to run that statement in SSMS it would fail too.

[Baldie47]

If I run it like that in SSMS it works, just passing the values for Endweek and Startweek, should I do it differently?

[Larnu]

So you're passing a bunch of NULL values for all of your parameters apart from @startweek and @endweek? Why even pass them if they are all NULL? You might as well set the default value to NULL and not pass any of them

[Larnu]

Also, it appears that @startweek and @endweek are clearly datetime values, so why are they declared as a varchar? If they are date and time values, then use a date and time datatype. Using a varchar is only going to cause you problems.

[Baldie47]

because if I try not to send them I get an error saying that is expecting the parameters that weren't supplied

[Larnu]

Which I why I stated about setting their default values in the SP: "You might as well set the default value to NULL and not pass any of them."

[Larnu]

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.

[Baldie47]

I added the errors

[Larnu]

... "Incorrect syntax near 'GO'.". there's your problem. GO is an SSMS keyword.

[Baldie47]

I removed the GO, and I'm having other issue, I'm putting it now

[Baldie47]

yes, I actually read it, but I forgot to remove the GO when replicating the error for the screenshot, the one I posted now is the one I was actually dealing with, sorry for that

[Larnu]

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."."

[Baldie47]

this one I don't understand :( I have google it but I don't get what I need to do now

[Larnu]

Using EXECUTE to redefine a single result set

[Hadi]

Check the following answer it may helps stackoverflow.com/questions/48083262/…

[Will]

I would suggest this will help you: stackoverflow.com/questions/7610491/…

[Baldie47]

I've tried based on the examples and still get errors: i.imgur.com/VeWHIIX.png

[Baldie47]

this kinda seems to work to continue: i.imgur.com/8AmzwF3.png however I don't know how to pass the parameters I need for startweek and endweek

[Baldie47]

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]

@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]

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.