CODEWITHSUNDEEP
sql-serverssis
Kuldip.Das
Kuldip.Das

Reputation: 201

SSIS: Execute SQL task fails when trying to call one stored procedure from another

I am running a Execute SQL task in which I am storing a variable which calls a procedure:

exec load_storedproc1 parameter1, parameter2

Now this stored procedure has a statement in the end which calls another stored procedure:

exec load_storedproc2 parameter1, parameter2

The parameters which are supposed to be passed for both the stored procedures are the same.

When I execute the SQL task, it calls the first stored procedure which executes successfully but when it reaches the end statement of it, which is to call the storedproc2, it fails with the error message:

Error: Invalid column name 'column1'.
Error: Invalid column name 'column2'.
Error: Invalid column name 'column3'.

column1, column2, column3 are columns from the table used in storedproc2.

I feel somehow the Execute SQL task is not able to call the storedproc2 and it keeps failing. I am using dbname.schemaname.tablename everywhere in storedproc2.

Upvotes: 0

Views: 332

Answers (1)

Kuldip.Das
Kuldip.Das

Reputation: 201

I found the answer to my issue. When we are calling one stored procedure from another stored procedure, in case we are using temp tables, the names of all the temp tables in both the stored procedure should be different as the SQL task will treat both as a single session.

I was using temp tables in the stored procedures and few of the names across both the stored procedures were same. After changing them, its working fine now.

Upvotes: 1

Related Questions