将变量值插入到表中

I have several variables in an SSIS package that I would like inserting into a table.

在SSIS包中有几个变量，我想将它们插入到表中。

example:-

例子:-

@financialMonth, @Status, @Comments

The Variables have been populated along the way with values based on lookups, filename, dates, etc, and I want to store them in a results table.

在此过程中，使用基于查找、文件名、日期等的值填充变量，我希望将它们存储在结果表中。

Is using the execute SQL task the way to do this ?

使用execute SQL任务是这样做的吗?

Do I need to call a sproc and pass those variales as parameters ?

我是否需要调用sproc并将这些变量作为参数传递?

I've tried putting the following T-SQL into the SQLStatement property

我尝试将下面的T-SQL放入SQLStatement属性中。

INSERT INTO FilesProcessed 
   (ProcessedOn, ProviderCode, FinancialMonth, 
   FileName, Status, Comments) 
SELECT     GETDATE(), 'ABC' , 201006, 
   'ABC_201005_Testology.csv', 
   'Imported','Success'

I tried hardcoding the values above to get it to work

我尝试硬编码上面的值以使它工作

These are the columns on the table I'm inserting into

这些是我要插入的表上的列

Column_name     Type         Computed  Length
fileID          int          no          4
ProcessedOn     datetime     no          8
ProviderCode    nchar        no          6
FinancialMonth  int          no          4
FileName        nvarchar     no        510
Status          nvarchar     no         40
Comments        nvarchar     no        510

This is the Expression code that feeds the SQLStatementSource property

这是提供SQLStatementSource属性的表达式代码。

"INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, 
  FileName, Status, Comments) SELECT     GETDATE() AS ProcessedOn, '"
  + @[User::providerCode] + "' , " 
  + (DT_STR,6,1252)@[User::financialMonth] + ", '" 
  + @[User::fileName] + "', 'Imported' AS Status,'Successfully' AS Comments "

Unfortunately I'm missing something, and can't quite get it to work.

不幸的是，我错过了一些东西，无法让它发挥作用。

The Error message I'm getting is ... Error: 0xC002F210 at Log entry in FilesProcessed, Execute SQL Task: Executing the query "INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, FileName, Status, Comments) SELECT
GETDATE(), 'ABC' , 201006, 'DAG_201005_Testology.csv', 'Imported','Successfully'" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I2)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

我得到的错误信息是……错误:0xC002F210在文件处理的日志条目处执行SQL任务:执行查询“插入到文件处理(ProcessedOn, ProviderCode, FinancialMonth, FileName, Status, Comments)选择GETDATE()， 'ABC'， 201006， 'DAG_201005_Testology。csv'、' import '、' successful '失败，出现以下错误:“将结果提取到类型变量(DBTYPE_I2)时发生错误”。可能的故障原因:查询出现问题，“ResultSet”属性设置不正确，参数设置不正确，或者连接建立不正确。

Please

请

a). Advise whether the Execute SQL Task is the way to do what I want to do.

a).建议执行SQL任务是否是我想要做的事情的方式。

b). Give me any pointers or pitfalls to look out for and check.

b).给我任何提示或陷阱，让我去寻找和检查。

Thanks in advance.

提前谢谢。

4 个解决方案

#1

OK, here is what I did.

这就是我所做的。

I created an Execute SQL task and configured, thus :-

我创建了一个执行SQL任务并配置，因此:-

General Tab
  ConnectionType = OLE DB
  SQLSourceType = Direct Input
  SQLStatement = (left blank)
  BypassPrepare = True
  ResultSet = None

Parameter Mapping
  (none - leave blank)

Result Set
  (none - leave blank)

Expressions
  SQLStatementSource = "INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, FileName, Status, Comments) SELECT GETDATE(), '" + @[User::providerCode] + "' , " + (DT_STR,6,1252)@[User::financialMonth] + ", '" + @[User::fileName] + "', 'Import - Success', '" + @[User::fileComments] + "'"

Then as long as I set up the variables and populate them in the variables window (the Expression editor will not let you save an expression that references a variable that does not exist. Keep notepad handy to store the contents while you go back and edit the variables window, and add new variables in ;)

然后，只要我设置好变量并在变量窗口中填充它们(表达式编辑器不会让您保存引用不存在的变量的表达式。当您返回并编辑变量窗口并在其中添加新变量时，请将记事本放在手边以存储内容;

Build the expression slowly, using the Parse expression button regularly to check.

缓慢构建表达式，使用Parse表达式按钮定期检查。

#2

make sure that the data types of the VALUES match the destination column data types.

确保值的数据类型与目标列数据类型匹配。

see: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e8f82288-b980-40a7-83a6-914e217f247d/

见:http://social.msdn.microsoft.com/forums/en - us/sqlintegrationservices/thread/e8f82288 b980 - 40 - a7 - 83 - a6 - 914 - e217f247d/

#3

A couple of speculative suggestions

一些推测性的建议

The Error message says An error occurred while extracting the result into a variable of type (DBTYPE_I2). But this is a straight insert statement. There shouldn't be a result except for rows affected. Do you have any parameter mappings erroneously set to Output?

错误消息指出，在将结果提取到类型变量(DBTYPE_I2)时发生错误。但这是一个直接的插入语句。除了受影响的行之外，不应该有结果。是否有任何参数映射被错误地设置为输出?
What if you try and run the SQL Query from the error message directly in management studio? Does that give you an error?

如果您尝试在management studio中直接从错误消息中运行SQL查询，会怎么样?这会给你一个错误吗?

#4

In the above table definition FinancialMonth as int datatype as

在上表中，金融月定义为int数据类型

FinancialMonth int no 4

FinancialMonth int 4号

while inseting casting as

而插图铸造

(DT_STR,6,1252)@[User::financialMonth]

(DT_STR 6 1252)@[用户:financialMonth):

I feel it's purely a datatype mismatch with the target table definition.

我觉得这纯粹是与目标表定义不匹配的数据类型。

thanks

谢谢

prav

#1