如何在ssis存储过程中传递值动态值

I have a question about SSIS: please tell me how to load a stored procedure result into a table using a SSIS package.

我有一个关于SSIS的问题:请告诉我如何使用SSIS包将存储过程结果加载到表中。

I want to load the result of a stored procedure into another table.

我想将存储过程的结果加载到另一个表中。

I have tried in ExecuteSQL task like this:

我试过像这样的ExecuteSQL任务:

insert into abctest123([lat], [long], [city], [state], [add])

    EXEC GECODE_U 
         @Address = '1234 N. Main Street',
         @City = 'Santa Ana',
         @State = 'CA'

This query is working fine using Execute SQL Task in SSIS.

使用SSIS中的执行SQL任务,此查询正常工作。

The address and city and state values will change frequently. Therefore, I created variables to pass the values dynamicaly to SSIS.

地址,城市和州的价值会经常变化。因此,我创建了变量来动态地将值传递给SSIS。

I tried like this - I created 3 variables address, city, state:

我试过这样 - 我创建了3个变量地址,城市,州:

insert into abctest123([lat], [long], [city], [state], [add])
    EXEC GECODE_U  ? , ? ,?

After that in Execute SQL using parameter mapping, I assign the 3 variables created, but I get this error:

之后在使用参数映射的Execute SQL中,我分配了3个创建的变量,但是我得到了这个错误:

[Execute SQL Task] Error: Executing the query "insert into abctest123( [lat] ,[long] ..."
failed with the following error:
Incorrect syntax near '@P2'.
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

[执行SQL任务]错误:执行查询“插入到abctest123([lat],[long] ...”失败,出现以下错误:'@ P2'附近的语法不正确。可能的失败原因:查询问题,“ ResultSet“属性未正确设置,参数设置不正确或连接未正确建立。

Please tell me how to pass dynamic values and solve this issue in SSIS.

请告诉我如何在SSIS中传递动态值并解决此问题。

2 个解决方案

#1

In the Execute SQL Task Editor - set the Result Set property as Full Result Set Then, In the Result Set Tab - accordingly assign the result to one/multiple variables

在执行SQL任务编辑器中 - 将结果集属性设置为完整结果集然后,在结果集选项卡中 - 相应地将结果分配给一个/多个变量

#2

You need to add them to the option parameter mapping inside your Execute SQL Task.

您需要将它们添加到Execute SQL Task中的选项参数映射中。

You can do it like this:

你可以这样做:

Create table first:

首先创建表:

CREATE TABLE [dbo].[testtest](
[Adress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL
) ON [PRIMARY]

Then create your SP:

然后创建你的SP:

CREATE PROCEDURE SP_NAME
 @Adress nvarchar(50), @City nvarchar(50), @State nvarchar(50) AS

BEGIN

insert into dbo.Testtest (Adress,City,[State])
select @Adress,@City,@State 
END

Then make your SSIS-Package with an Execute SQL Task, and create your 3 variables called Adress,City,State. Next inside your Execute SQL Task you do like this:

然后使用执行SQL任务创建SSIS包,并创建名为Adress,City,State的3个变量。接下来你的执行SQL任务你喜欢这样:

When you have done that, you should be able to write your expression inside the SQL Statement, like this:

完成后,您应该能够在SQL语句中编写表达式,如下所示:

Then you should get this result inside your table:

然后你应该在你的表中得到这个结果:

#1