在 SQL Server 表中转储 SSIS USER 变量名称和值 [英] Dump SSIS USER variable name and value in SQL Server table
问题描述
用途:获取 SSIS 包中的所有用户变量，并将变量名称及其值写入 SQL Server 2008 表中.
Purpose: Fetch all the user variables that are in the SSIS package and write the variable name and their values in a SQL Server 2008 table.
我尝试了什么:我有一个小脚本任务"来显示变量名称及其值.脚本如下.
What have I tried: I got a small "Script Task" working to Display the variable name and their value. The script is as follows.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_81ec2398155247148a7dad513f3be99d.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = app.LoadPackage(
@"C:package.dtsx",
null);
Variables pkgVars = pkg.Variables;
foreach (Variable pkgVar in pkgVars)
{
if (pkgVar.Namespace.ToString() == "User")
{
MessageBox.Show(pkgVar.Name);
MessageBox.Show(pkgVar.Value.ToString());
}
}
Console.Read();
}
}
}
需要做的事情:我需要接受这个并将值转储到数据库表中.我正在尝试为此开发一个脚本组件，但由于缺乏.net 脚本知识，我还没有接近终点线.这就是我在组件方面所做的.
What needs to be done: I need to take this and dump the values to a database table. I am trying to work on a script component for this, but due to lack of knowledge of .net scripting, I havent gotten anywhere close to the finish line. This is what I have done on the component side.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts;
using System.Windows.Forms;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
#region Class Variables
IDTSVariables100 variables;
#endregion
variables = null;
this.VariableDispenser.GetVariables(out variables);
foreach(Variable myVar in variables)
{
MessageBox.Show(myVar.Value.ToString());
if (myVar.Namespace == "User")
{
Output0Buffer.ColumnName = myVar.Value.ToString();
}
}
}
}
推荐答案
我现在能想到两种方法来解决这个问题.
I can think of 2 ways of solving this issue right now .
- 使用相同的脚本任务将值插入数据库
- 使用 Foreach 循环枚举 ssis 包变量(如您所问)
插入变量名和值的表格脚本是
Table Script for inserting the variable name and value is
CREATE TABLE [dbo].[SSISVariables]
(
[Name] [varchar](50) NULL,
[Value] [varchar](50) NULL
)
1.使用脚本任务并编写如下代码
1.Use Script task and write the below code
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
private static string m_connectionString = @"Data Source=Server Name;
Initial Catalog=Practice;Integrated Security=True";
public void Main()
{
List<SSISVariable> _coll = new List<SSISVariable>();
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = app.LoadPackage(PackageLocation,Null);
Variables pkgVars = pkg.Variables;
foreach (Variable pkgVar in pkgVars)
{
if (pkgVar.Namespace.ToString() == "User")
{
_coll.Add(new SSISVariable ()
{
Name =pkgVar.Name.ToString(),
Val =pkgVar .Value.ToString ()
});
}
}
InsertIntoTable(_coll);
Dts.TaskResult = (int)ScriptResults.Success;
}
public void InsertIntoTable(List<SSISVariable> _collDetails)
{
using (SqlConnection conn = new SqlConnection(m_connectionString))
{
conn.Open();
foreach (var item in _collDetails )
{
SqlCommand command = new SqlCommand("Insert into SSISVariables values (@name,@value)", conn);
command.Parameters.Add("@name", SqlDbType.VarChar).Value = item.Name ;
command.Parameters.Add("@value", SqlDbType.VarChar).Value = item.Val ;
command.ExecuteNonQuery();
}
}
}
}
public class SSISVariable
{
public string Name { get; set; }
public string Val { get; set; }
}
说明:在此，我正在创建一个具有属性 Name 和 Val 的类.使用您的代码检索包变量及其值并将它们存储在集合 List 中.然后将此集合传递给一个方法(InsertIntoTable)，该方法只是插入值通过集合枚举进入数据库.
Explanation:In this ,i'm creating a class which has properties Name and Val . Retrieve the package variables and their value using your code and store them in a collection List<SSISVariable> .Then pass this collection to a method (InsertIntoTable) which simply inserts the value into the database by enumerating through the collection .
注意:
There is a performance issue with the above code ,as for every variable
im hitting the database and inserting the value.You can use
[TVP][1]( SQL Server 2008) or stored procedure which takes
xml( sql server 2005) as input.
2.使用 ForEach 循环
2.Using ForEach Loop
设计
第 1 步:创建一个类型为 System.Object 的变量 VariableCollection 和另一个Item 类型的变量 String 用于存储 Foreach 循环的结果
Step 1: create a Variable VariableCollection which is of type System.Object and another
variable Item which is of type String to store the result from Foreach loop
第 2 步:第一个脚本任务.
Step 2: For the 1st script task .
VariableCollection 用于存储变量名及其值
VariableCollection is used to store the Variable name and its value
步骤 3:在脚本任务 Main Method 中编写以下代码
Step 3: Inside the script task Main Method write the following code
public void Main()
{
ArrayList _coll = new ArrayList();
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = app.LoadPackage(Your Package Location,null);
Variables pkgVars = pkg.Variables;
foreach (Variable pkgVar in pkgVars)
{
if (pkgVar.Namespace.ToString() == "User")
{
_coll.Add(string.Concat ( pkgVar.Name,',',pkgVar.Value ));
}
}
Dts.Variables["User::VariableCollection"].Value = _coll;
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
}
第 4 步:拖动 Foreach 循环并在表达式中使用 Foreach from Variable Enumerator
Step 4: Drag a Foreach loop and in the expression use Foreach from Variable Enumerator
第五步:Foreach循环枚举值并将其存储在一个变量User::Item
Step 5:Foreach loop enumerates the value and stores it in a variable User::Item
第 6 步:在 foreach 循环中拖动脚本任务并选择变量
Step 6:Inside the foreach loop drag a script task and select the variable
readonly variables User::Item
第七步:在main方法中编写如下代码
step 7: Write the below code in the main method
public void Main()
{
string name = string.Empty;
string[] variableCollection;
variableCollection = Dts.Variables["User::Item"].Value.ToString().Split(',');
using (SqlConnection conn = new SqlConnection(m_connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand("Insert into SSISVariables values (@name,@value)", conn);
command.Parameters.Add("@name", SqlDbType.VarChar).Value = variableCollection[0];
command.Parameters.Add("@value", SqlDbType.VarChar).Value = variableCollection[1];
command.ExecuteNonQuery();
}
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
}
说明:在 Foreach 循环中，我只能枚举一个变量.所以逻辑是，我需要以某种方式将变量名称及其值传递给一个变量.为此，我将名称和值连接起来
Explanation : In the Foreach loop ,i can only enumerate one variable .So the logic is, i need to somehow pass the variable name and its value into one variable .And for this ,i have concatenated name and value
string.Concat ( pkgVar.Name,',',pkgVar.Value )
foreach 循环中的脚本任务只是将变量拆分并将其存储到字符串数组中，然后您可以使用数组索引访问名称和值并将其存储在数据库中
Script task in the foreach loop simply splits the variable and stores it into a array of string and then u can access the name and value using array index and store it in the database
这篇关于在 SQL Server 表中转储 SSIS USER 变量名称和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
