通过 SSIS 从变量插入数据到 SQL Server

2021-12-30 00:00:00 web-services sql-server foreach-loop-container ssis

我已经使用了简单的 Web 服务来添加两个数字，并且得到了 xml 格式的结果，该结果已存储在名为 my_data 的变量中，现在我必须将该结果插入名为 Data_result 的 sql 表中写入在oledb源代码编辑器中查询，但无法使用变量插入数据.我只想将我的Web服务计算的所有结果存储在sql表中.

I have consumed simple web service for addition of two numbers and I am getting the result in xml format that I have stored in variable supposed named as my_data, now I have to insert that result in my sql table named as Data_result by writing query in oledb source editor, but unable to insert data using variable.I just want to store all the result calculated by my web service in sql table.

由于我是 SSIS 的新手，非常感谢任何帮助

Since I am newbie to SSIS, any help is greatly appreciated

当我尝试通过以下查询命令插入数据时:

When I am trying to insert the data by the following query command:

"INSERT INTO Data_Result(Result) SELECT '"+@USER::my_data+"'"

它给出了错误:

数据流任务中的错误 [OLE DB 源 [55]]:SQL 命令没有返回列转换

Error at Data Flow Task [OLE DB Source [55]]: No Column Transformation was return by SQL Command

--------根据给出的解决方案更新了错误解释

--------Updated explanation of errors based on the solution given

执行查询时出错如果我放置没有 "" 引号的表达式，则会出现以下错误

Error for executing query If I place the expression without the "" quotes then I get following error

如果我将查询放在双引号中，则会显示以下错误

If I place the query in double quotes then following error is shown

如果我从 User::Data Variable 中删除 User 并将查询放在双引号中，那么我会得到以下屏幕尽管该表达式已计算但在我尝试在 Execute sql Task 的表达式列中搜索变量时进一步处理此已计算的表达式后，我无法找到新创建的变量，如下所示

And if I remove User from User::Data Variable and place the query in double quotes then I get following screen although the expression evaluates but after proceeding further on this evaluated expression when I am trying to search for the variable in expression column of Execute sql Task, then I am unable to locate the newly created variable, as shown below

------------根据其他查询更新问题---------

------------Updated question according to other query----------

这是我整个工作流程的图片

Here is the picture of my whole work flow

这是我在集合选项卡下的每个循环容器中所做的

This is what I have did inside the for each loop container under collection tab

下面的设置是我在变量映射选项卡之间完成的，

And this below setting I have done between Variable mapping tab,

在下面的屏幕截图中，我使用执行 SQL 任务"通过插入查询将从 Web 服务任务获得的数据输入到数据库中，但无法触发正确的插入查询，

And in below screen shot, I am using Execute SQL Task to enter my data obtained from web service task into database using an insert query, but unable to fire proper insert query,

下面是我的 XML 文件，

And below is my XML file,

<?xml version="1.0" encoding="utf-16"?>

瑞诗玛

1988-09-23T00:00:00,

1988-09-23T00:00:00 ,

我从网络服务收到的这个名字和生日我想把它插入到数据库表中

This name and birthdate I received from web service I want to insert that into database table

--------------更新了 foreach 循环容器的问题--------

--------------Updated question for foreach loop container--------

下面是我使用的foreach循环容器，

Below is the foreach loop container I am using,

但是我仍然没有将节点值放入变量中，而且我想使用对象类型变量或者它可以使用字符串类型的变量.

But still I am not getting node value into variable and also I am suppose to make use of object type variable or it can work with string type of variable.

推荐答案

我建议您在另一个变量中构建此 SQL 语句，如下所述:

I suggest you build this SQL statement in another variable, as explained here:

http://consultingblogs.emc.com/jamiethomson/存档/2005/12/09/2480.aspx

然后在执行 SQL 语句中使用该变量

Then use that variable in your execute SQL statement

通常您会尝试参数化查询，但在这种情况下(和许多其他情况一样)它不起作用.

Normally you would try and parameterise the query but in this case (as in many others) it won't work.

这里有一些更详细的说明:

Here are some more detailed instructions:

A.生成动态SQL

  1. 创建一个新的字符串类型的 SSIS 变量
  2. 在属性中，将其评估为表达式"设置为 TRUE

  3. 在属性中，单击表达式"并按如下方式构建表达式:

  1. Create a new SSIS variable of type string
  2. In properties, set it's 'Evaluate as Expression' to TRUE

  3. In properties, click on 'Expression' and build an expression as follows:

"INSERT INTO Data_Result(Result) VALUES ('"+@[USER::my_data]+"')"

按评估并验证输出是否是有效的 SQL(您甚至可能希望将其粘贴到 SSMS 中并进行测试)

Press Evaluate and verify that the output is valid SQL (you may even wish to paste it into SSMS and test)

B.获取执行 SQL 任务以运行您的 SQL:

B. Get an Execute SQL task to run your SQL:

  1. 在您的页面上放置一个执行 SQL 任务.设置它的连接
  2. 突出显示执行 SQL 任务，转到属性并单击表达式
  3. 选择属性SQLStatementSource".在表达式中，选择您在步骤 A 中构建的变量，然后按评估.
  4. 对所有内容按 OK

现在 my_data 中发生的任何更改都将自动应用于您的执行 SQL 任务

Now whatever change occurs in my_data will automatically be applied to your execute SQL task

先试试这个，然后回复我.

Try that for starters and get back to me.

相关文章