通过SSIS将数据从变量插入SQL Server [英] Inserting Data into SQL Server from variables via 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变量中删除User并将查询放在双引号中，那么我将得到以下屏幕 尽管表达式求值，但是当我尝试在Execute sql Task的expression列中搜索变量时，在对该求值表达式进行进一步处理之后，我无法找到新创建的变量，如下所示

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"?>

Reshma

1988-09-23T00:00:00 ，

1988-09-23T00:00:00 ,

我从Web服务收到的这个名字和生日(我想将其插入数据库表中)

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

A. Generate the dynamic 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. 在所有内容上按确定

现在，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.

这篇关于通过SSIS将数据从变量插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！