从SSIS包中我应该如何以varchar（max）的形式执行存储在表中的sql脚本？

I'm storing large (varchar(max)) SQL scripts in a table. I'd like to execute the scripts in an SSIS package.

我将大型(varchar(max))SQL脚本存储在表中。我想在SSIS包中执行脚本。

Looking at other posts on this site it's easy enough to get the varchar(max) into an object variable. But then what to do? Is there a way for an Execute Sql Task (SQLSourceType of Variable) to specify an Object variable rather than a String variable?

查看此站点上的其他帖子,很容易将varchar(max)转换为对象变量。但接下来该怎么办?有没有办法让Execute Sql Task(变量的SQLSourceType)指定一个Object变量而不是一个String变量?

Is there an approach that will work?

有一种方法可行吗?

1 个解决方案

#1

Here's how I might approach it:

以下是我如何处理它:

Add a Data Flow task to your control flow
- Add a Source (ADO.NET) that connects to your database
- Create a Package level Object variable (for the next step)
- Add a Recordset destination that populates your data into the Object variable created in the previous step

向控制流添加数据流任务添加连接到数据库的源(ADO.NET)创建包级别对象变量(用于下一步)添加一个Recordset目标,将数据填充到上一个创建的Object变量中步

Back on the control flow:
- Create a package level String variable for the "current" query (see next step)
- Add a For Each ADO.NET enumerator
  - Connect the previous Data Flow task to the For Each task
  - Configure the For Each to use the Object variable as a source, and to store the column index with the SQL into the String variable
- Add an Execute SQL task inside the For Each task
  - Configure it to execute a SQL Command from Variable, and pick the string variable containing the current query

返回控制流:为“当前”查询创建包级别字符串变量(请参阅下一步)添加For Each ADO.NET枚举器将以前的数据流任务连接到For Each任务配置For Each以使用Object变量作为源,并将带有SQL的列索引存储到String变量中为For Each任务添加执行SQL任务将其配置为从Variable执行SQL命令,并选择包含当前查询的字符串变量

Basically it will collect the queries from the table, then for each collected query, assign it to a variable, and then the Execute SQL command can pull the command text from that variable.

基本上它将从表中收集查询,然后对于每个收集的查询,将其分配给变量,然后执行SQL命令可以从该变量中提取命令文本。

#1