Sql – SSIS Stored Procedure Call

sqlsql serverssisstored-procedures

I'm trying to call a simple stored procedure which would return a list of names in normal test format, all in a single line. I'm passing it two parameters, but no matter how i setup the call, either within a OLE DB Source Editor, or within an execute SQL task.
There must be something i'm missing with my SQL statement b/c i keep getting an error.

My SQL command text is

EXEC [dbo].[spGetEmployerIdCSV]  ?,  ?

The parameters I'm passing are listed exactly as they are declared in the stored procedure, @IDType and @IDNumber, which are mapped to predefined variables.

Every time I try to run it from either task type, I get a

The EXEC SQL construct or statement is not supported.

What is the best way to run a stored procedure within SSIS?

Thank you.

Best Answer

I cannot recreate your issue.

I created a control flow with the proc already in existence.

control flow

I have my execute sql task configured as

exec sql task

My parameters tab shows

parameter mapping

When I click run, the package goes green.

My initial assumption was that you had signaled that you were using a stored procedure and were erroneously providing the EXEC part. I had done something similar with SSRS but even updating the IsQueryStoredProcedure to True, via Expression, I could not regenerate your error message.

If you are doing something else/different/in addition to what I have shown in the Execute SQL Task, could you amend your question to describe what all functionality the procedure should show.

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Sql-server – How to call a Stored Procedure in SSIS

In the Data Flow, the OLE DB Command can be used to execute a SQL statement for each row in a dataflow - (MSDN documentation)

Alternatively, you can store the source result set in a variable of data type object and use a Foreach Loop container in the Control Flow (example here).

Best Answer

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

Sql-server – How to call a Stored Procedure in SSIS

Related Topic