在SQL Server表转储SSIS用户变量名称和值 [英] Dump SSIS USER variable name and value in SQL Server table

问题描述

目的：获取是在SSIS包中的所有用户变量，写变量名称以及它们在SQL Server 2008中的表值。

有什么我想：我有一个小的脚本的任务的工作，以显示变量名和它们的值。该脚本如下

 使用系统; 
使用System.Data这; 
使用Microsoft.SqlServer.Dts.Runtime;使用System.Windows.Forms的
; 
命名空间ST_81ec2398155247148a7dad513f3be99d.csproj 
 {
 [System.AddIn.AddIn（ScriptMain版本=1.0，出版商=，说明=）] 
公部分类ScriptMain：Microsoft.SqlServer.Dts.Tasks.ScriptTask.VS​​TARTScriptObjectModelBase 
 {
 
＃地区VSTA生成的代码
枚举ScriptResults 
 {
 =成功Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success，
失败= Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure 
}; 
 #endregion 
 
公共无效的主要（）
 {
 
 
 Microsoft.SqlServer.Dts.Runtime.Application应用=新的微软。 SqlServer.Dts.Runtime.Application（）; 
包pkg = app.LoadPackage（
 @C：\package.dtsx，
 NULL）; 
变量pkgVars = pkg.Variables; 
 
的foreach（在pkgVars可变pkgVar）
 {
如果（pkgVar.Namespace.ToString（）==用户）
 {
的MessageBox。展（pkgVar.Name）; 
 MessageBox.Show（pkgVar.Value.ToString（））; 
} 
} 
 Console.Read（）; 
} 
} 
 
} 
  

有什么需要做：我要借此和转储的值到数据库表。我试图在脚本部件作为这方面的工作，但由于缺乏.NET脚本的知识，我还没有任何接近终点线得到。这是我在组件端完成。

 使用系统; 
使用System.Data这; 
使用Microsoft.SqlServer.Dts.Pipeline.Wrapper; 
使用Microsoft.SqlServer.Dts.Runtime.Wrapper;使用Microsoft.SqlServer.Dts 
;使用System.Windows.Forms的
; 
 
 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] 
公共类ScriptMain：UserComponent 
 {
 
公共覆盖无效CreateNewOutputRows（）
 {
 
＃区域类变量
 IDTSVa​​riables100变量; 
 #endregion 
 
变量= NULL; 
 
 this.VariableDispenser.GetVariables（出变量）; 
的foreach（变量变量myVar的）
 {
 MessageBox.Show（myVar.Value.ToString（））; 
如果（myVar.Namespace ==用户）
 {
 Output0Buffer.ColumnName = myVar.Value.ToString（）; 
} 
} 
} 
 
} 
  

解决方案

我能想到的，现在解决这个问题的两种方法。

1. 使用同样的脚本任务值插入到数据库


2. 使用foreach循环枚举SSIS包变量（如你问）

表脚本插入变量的名称和值是

  CREATE TABLE [DBO]。[ SSISVariables] 
（
 [名] [VARCHAR（50）NULL，
 [超值] [VARCHAR（50）NULL 
）
  

1.使用脚本任务，写下面的代码

  [System.AddIn.AddIn（ScriptMain版本=1.0，出版商=，说明=）] 
公部分类ScriptMain：Microsoft.SqlServer.Dts.Tasks.ScriptTask.VS​​TARTScriptObjectModelBase 
 {
私人静态字符串m_connectionString = @数据源=服务器名称; 
初始目录=实践;集成安全性= TRUE  
公共无效的主要（）
 {
名单，LT; SSISVariable> _coll =新的List< SSISVariable>（）; 
 Microsoft.SqlServer.Dts.Runtime.Application应用=新Microsoft.SqlServer.Dts.Runtime.Application（）; 
包pkg = app.LoadPackage（PackageLocation，NULL）; 
变量pkgVars = pkg.Variables; 
 
的foreach（在pkgVars可变pkgVar）
 {
如果（pkgVar.Namespace.ToString（）==用户）
 {
 _coll。加入（新SSISVariable（）
 {
名称= pkgVar.Name.ToString（），
瓦尔= pkgVar .Value.ToString（）
}）; 
} 
} 
 InsertIntoTable（_coll）; 
 Dts.TaskResult =（INT）ScriptResults.Success; 
} 
 
公共无效InsertIntoTable（列表< SSISVariable> _collDetails）
 {使用
（SqlConnection的康恩=新的SqlConnection（m_connectionString））
 {
 conn.Open（）; 
的foreach（在_collDetails VAR项）
 {
的SqlCommand命令=新的SqlCommand（插入到SSISVariables值（@名，@值），conn）;在
 command.Parameters.Add（@名，SqlDbType.VarChar）.value的= item.Name; 
 
 command.Parameters.Add（@值，SqlDbType.VarChar）.value的= item.Val; 
 command.ExecuteNonQuery（）; 
} 
} 
} 
} 
 
公共类SSISVariable 
 {
公共字符串名称{;组; } 
公共字符串的Val {获得;组; } 
} 
  

说明：在此，我要创建具有的属性名称的类和Val。使用你的代码检索包变量及其值，并将其存储在集合中列表< SSISVariable> 。然后通过这个集合的方法（ InsertIntoTable ），它只需插入通过集合枚举值插入数据库

请注意：

 有一个与上面的代码的性能问题，因为对于每一个变量
即时命中数据库和插入value.You可以使用
 [TVP] [ 1]（SQL Server 2008中）或存储过程这需要
 XML（SQL Server 2005中）作为输入。 
  

2，使用foreach循环

设计

第1步：创建一个变量 VariableCollection 这类型的 System.Object的另有
变量项目这类型的字符串来存储来自foreach循环中的结果。

步骤2：对于第一个脚本任务

VariableCollection 是用来存储变量的名称和它的值

步骤3：里面的脚本任务主要方法编写以下代码

 公共无效的主要（）
 {
 ArrayList的_coll =新的ArrayList（）; 
 
 Microsoft.SqlServer.Dts.Runtime.Application应用=新Microsoft.SqlServer.Dts.Runtime.Application（）; 
包pkg = app.LoadPackage（你的包位置，NULL）; 
 
变量pkgVars = pkg.Variables; 
的foreach（在pkgVars可变pkgVar）
 {
如果（pkgVar.Namespace.ToString（）==用户）
 {
 _coll.Add（串。的concat（pkgVar.Name，'，'，pkgVar.Value））; 
} 
} 
 Dts.Variables [用户:: VariableCollection]值= _coll。 
 // TODO：添加您的代码在这里
 Dts.TaskResult =（INT）ScriptResults.Success; 
} 
  

步骤4：拖动一个foreach循环，并在表达式中使用从Foreach源变量枚举器

第五步：Foreach循环枚举一个变量的值，并将其存储用户::项目

步骤6：里面的foreach循环阻力脚本任务，并选择变量

 只读变量用户::项目
  

第7步：写下面的代码在main方法

 公共无效的主要（）
 {
字符串名称=的String.Empty; 
的String [] variableCollection; 
 variableCollection = Dts.Variables [用户::项目] Value.ToString（）斯普利特（''）。; 
使用（SqlConnection的康恩=新的SqlConnection（m_connectionString））
 {
 conn.Open（）; 
的SqlCommand命令=新的SqlCommand（插入到SSISVariables值（@名，@值），conn）;在
 command.Parameters.Add（@名，SqlDbType.VarChar）。价值= variableCollection [0]; 
 
 command.Parameters.Add（@值，SqlDbType.VarChar）.value的= variableCollection [1]; 
 
 command.ExecuteNonQuery（）; 
} 
 // TODO：在此处$添加您的代码为B $ B Dts.TaskResult =（INT）ScriptResults.Success; 
} 
  

说明：在foreach循环中，我只能列举一个变量。所以逻辑是，我需要以某种方式传递变量的名称和它的值写入一个变量。而对于这一点，我已经连在一起的名称和值

  string.Concat（pkgVar.Name，'，'，pkgVar.Value）
  

脚本任务foreach循环简单​​地拆分变量，并将其存储到字符串的数组，然后ü可以使用数组索引来访问的名称和值，并将其存储在数据库中。

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();
    }
    }

    }

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 .

1. Use the same script task to insert the values into the Database
2. using Foreach loop to enumerate the ssis package variables ( as you have asked )

Table Script for inserting the variable name and value is

CREATE TABLE [dbo].[SSISVariables]
(
[Name] [varchar](50) NULL,
[Value] [varchar](50) NULL
)

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; }
   }

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 .

Note :

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.Using ForEach Loop

Design

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

Step 2: For the 1st script task .

VariableCollection is used to store the Variable name and its value

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;
    }

Step 4: Drag a Foreach loop and in the expression use Foreach from Variable Enumerator

Step 5:Foreach loop enumerates the value and stores it in a variable User::Item

Step 6:Inside the foreach loop drag a script task and select the variable

readonly variables   User::Item 

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;
    }

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 )

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用户变量名称和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！