You really should push back, use the right tool for the job rather than just say that it's "not an option" for you.
Developer9 wrote:I am getting below error when I use the Exec statement in SQL
Where exactly in the stage are you trying to execute the procedure?
How exactly do you have everything set? Without
details people won't be able to provide cogent help. And recognize you cannot execute a procedure
that returns data in either the Before or After SQL areas as there's basically nowhere for the data to go. As a source, you need to select data for delivery into the job, only the Stored Procedure stage allows a procedure to be a source and to drive records into a job.
Read the
Connectivity Guide for ODBC manual, in particular where it discusses the Connector stage and where you should use something else. For example:
The following table lists the scenarios where you might want to use one of the stages other than the ODBC Connector stage.
Table 4. Scenarios where you might use a stage other than the ODBC Connector stage
Code: Select all
Goal Stage to use instead of the ODBC Connector stage
Use stored procedures. Stored Procedure (preferred)
ODBC
Then swap the ODBC Connector for the Stored Procedure stage.