Calling Stored Procedure from SSIS

Hi

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi

I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS?

|||

Hi,

Using a sequence container only ensures that all the tasks in the container are executed simultaneously and only comes out of the after completing all the tasks in container. If you have any task to do only after completing all the 5 transfer task then use sequence container otherwise it's an overhead.

Thanks

Mohit

|||

We don't have any such requirement,as this DTS is used to transfer data to 5 different tables. I think it would better not to use it. I wanted to use it as the SSIS is looking very odd with 1 Execute SQl Task and on success 5 Data Flow Task.

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Thanks. This works fine. I have another question related to SSIS and WSE 3.0. I have to use custom application within the script task but whenever the script tries to initialize my custom class which uses WSE policy, it fails. What i need to know is if there is any adaptor for WSE 3.0 for SSIS? if not then how can you load wse3policyCache.config file within SSIS so that the custom dll can refer to policy?

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

You can provide a config file to the SSIS package host, in which you can inlcude WSE settings. If you just use the MS tools to run packages then target the folowing with a config file including the WSE 3 stuff-

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

and have the wse3policyCache.config under the same location. When i run the task SSIS is not able to load the cache config file. Then i have even tried copying policies segment from config file and replacing the policay element mentioned above, but this again didn't work.

Note: The policy and config files work fine in .net wrapper application outside SSIS