SSIS Stored Procedure Call

Question

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.

Did you do a parameter mapping? Follow this http://www.sqlis.com/post/The-Execute-SQL-Task.aspx — JonH, Jul 22 '13 at 12:23

score 6 · Accepted Answer · answered Jul 22 '13 at 19:56

6

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.

answered Jul 22 '13 at 19:56

billinkc

59,250
9
102
159

Thank you billinkc. I really appreciate your help with this. I found the issue was, i was trying to initially import a varchar(max) into my variable to then pass it into the sproc. I found that apparently SSIS and (max) length settings DO NOT work well together at all. It caused my entire data import to fail, therefore not allowing the rest of the data to flow. Once i hard set the length, it works just fine, using the stored procedure query assistance from you. Thank you again! – Paynetech Jul 24 '13 at 13:13
Yes, this would cause issues as SSIS has its own data types. Probably used DT_STR, which is a null-terminated ANSI/MBCS character string with a maximum length of 8000 characters. (If a column value contains additional null terminators, the string will be truncated at the occurrence of the first null.) SQL Server varchar(max) stores a maximum of 2 147 483 647 (2GB) - quite a bit more! – Thronk Nov 01 '13 at 18:54

score 2 · Answer 2 · answered Jul 22 '13 at 12:33

2

Did you specify output parameters? For 2 in / 1 out your SQL code will look like:

EXEC [dbo].[spGetEmployerIdCSV] ?, ?, ? OUTPUT

ResultSet has to be set to none!

answered Jul 22 '13 at 12:33

makciook

1,537
10
19

1

This doesn't seem to work either. Is your answer example for use within an Execute SQL Task, or from within a Data Flow Task / OLE DB Source Editor? If I set it up like this in the Source Editor, it simply refuses to take any more parameters. If from within SQL Task it states "The EXEC SQL construct or statement is not supported." – Paynetech Jul 22 '13 at 14:07

score 1 · Answer 3 · answered Jun 24 '14 at 10:15

I had the same problem.

When you execute the task check the 'Progress' tab; this will give you a 'fully fledged' error details.

In my case I didn't map the parameter which I created in the SQL Task to the actual one in the Stored Procedure.

So, double click the SQL Task, click on Parameter Mapping on the left hand side, then Create the parameters and their respective mappings. Here is a screenshot (in version 2012):

enter image description here

score 0 · Answer 4 · answered Oct 24 '19 at 15:24

I faced with a similar issue after upgrading to SSDT for VS 2013 (the problem was with lookup element). Fixed by using this answer:

EXEC ('dbo.MyStoredProcedure')
WITH RESULT SETS
  (
    (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
    )
  )

score -3 · Answer 5 · answered Jan 07 '14 at 23:59

-3

use the same command you use to run the stored procedure in MySQL workbench

call ();

USE this command in Execute SQL Task

answered Jan 07 '14 at 23:59

Abhi

92
2
11

This is not tagged as MySQL – Hituptony Jun 22 '15 at 21:30

SSIS Stored Procedure Call

5 Answers5

Linked