SSIS包中的XML配置文件错误

I've been working with SSIS packages and I'm currently stuck in this problem. I've been developing using a connection String that I wrote in a Package Variable and that value goes to the Connection Mannager through an Expression. Something like this: @[User::Connection]

我一直在使用SSIS包,我目前陷入了这个问题。我一直在使用我在Package Variable中编写的连接String进行开发,该值通过Expression传递给Connection Mannager。像这样:@ [User :: Connection]

It was working great for debugging. Since I needed to call some stored procedures inside Script tasks and Script Components, I would just call the Package variable as the string connection to the database. Since I'm new to the ETL world (less than a month) and I've been doing some packages (6 actually), it was just recently I read that I should save my ConnectionString in a xml file and store things like user, password there(Since production server will probbably have different user account, as mentioned here and here)

它非常适合调试。因为我需要在Script任务和脚本组件中调用一些存储过程,所以我只是将Package变量称为与数据库的字符串连接。由于我是ETL世界的新手(不到一个月)而且我一直在做一些软件包(实际上是6个),最近我才读到我应该将我的ConnectionString保存在xml文件中并存储用户,密码那里(因为生产服务器可能会有不同的用户帐户,如此处和此处所述)

Now, I've tried to do what it says, but it doesn't work for me. So I created a new SSIS package to be testing around, it contains a Script task and its calling a simple Stored Procedure. The package Configuration contains only the ConnectionString property from the Connection Manager, and it looks something like this:

现在,我试图按照它说的做,但它对我不起作用。所以我创建了一个新的SSIS包来进行测试,它包含一个脚本任务,并调用一个简单的存储过程。包配置仅包含Connection Manager中的ConnectionString属性,它看起来像这样:

<?xml version="1.0" ?> 
- <DTSConfiguration>
- <DTSConfigurationHeading>
  <DTSConfigurationFileInfo GeneratedBy="JohnSmith" GeneratedFromPackageName="Package" GeneratedFromPackageID="{2C421533-C76C-4583-841C-367C50DB61C1}" GeneratedDate="17/01/2017 --:--:-- p.m." /> 
  </DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="\Package.Connections[OLEDB_CONNECTION].Properties[ConnectionString]" ValueType="String">
  <ConfiguredValue>Data Source=ServerName;User ID=UserName;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Auto Translate=False;Application Name=SSIS-Package-{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}ServerName.DatabaseName.UserName;Use Encryption for Data=False;Password=abc123;</ConfiguredValue> 
  </Configuration>
  </DTSConfiguration>

I added the Password at the end, just as the link at the start suggested, and tried to get the connetionString like this in the Script task:

我在最后添加了密码,就像开头建议的链接一样,并尝试在脚本任务中获取这样的connetionString:

String cn = Dts.Connections["OLEDB_CONNECTION"].ConnectionString;

and then calling it in the Stored Procedure, like this:

然后在存储过程中调用它,如下所示:

 using (OleDbConnection con = new OleDbConnection(cn))
 {
     using (OleDbCommand cmd = new OleDbCommand("dbo.usp_sgn_conseguirNumDoc", con))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@var_CODDOC", OleDbType.VarChar, 3).Value = "001"; 
         cmd.Parameters.Add("@var_NUMDOC", OleDbType.BigInt).Direction = ParameterDirection.Output;
         con.Open();
         cmd.ExecuteNonQuery();
         result = Int64.Parse(cmd.Parameters["@var_NUMDOC"].Value.ToString());
     }
  }

but it fails in con.Open() so when I check the ConnectionString, its the same but without the Password value that I wrote. Its like it doesnt read it from the xml configuration file. When I checked the Message Tab in the Error List Window, I see this:

但它在con.Open()失败,所以当我检查ConnectionString时,它是相同的但没有我写的密码值。就像它没有从xml配置文件中读取它。当我在错误列表窗口中检查消息选项卡时,我看到:

<DTS:ConnectionManager>
<DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Property DTS:Name="ObjectName">OLEDB_CONNECTION</DTS:Property>
<DTS:Property DTS:Name="DTSID">{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}</DTS:Property>
<DTS:Property DTS:Name="Description"></DTS:Property>
<DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager>
<DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Password DTS:Name="Password" Sensitive="1"></DTS:Password>
<DTS:Property DTS:Name="ConnectionString">Data Source=ServerName;User ID=UserName;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Auto Translate=False;Application Name=SSIS-Package-{C24F1A69-A9A3-40D4-8A1E-7EA61E0EB57D}ServerName.DatabaseName.UserName;Use Encryption for Data=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

Im out of ideas on what to do, any help will be appreciated

我不知道该怎么做,任何帮助将不胜感激

Im using Visual Studio 2008 and SQL Server 2008.

我使用的是Visual Studio 2008和SQL Server 2008。

1 个解决方案

#1

Finally, after a couple of days busting my head to the desk, managed to do it. It's a silly solution, but it wouldn't have crossed my mind in a thousand years! this question helped me, not the accepted answer but this part right here:

最后,经过几天把头撞到桌子后,设法做到了。这是一个愚蠢的解决方案,但它在一千年内不会超越我的想法!这个问题对我有帮助,而不是接受的答案,但这一部分就在这里:

You can store the password in the configuration string by going to properties and adding password=yourpassword but very important put a space before 'password' word and after ';'

您可以通过转到属性并添加password = yourpassword将密码存储在配置字符串中,但非常重要的是在'password'字之前和';'之后加上一个空格

The fact that it worked blew my mind, something so simple and yet hard to find. After that, in a Script task I would use

它起作用的事实引起了我的注意,这是一件如此简单而又难以找到的事情。之后,在我将使用的脚本任务中

String cn = Dts.Connections["OLEDB_CONNECTION"].ConnectionString;

and inside a Script Component (Data Flow Task) I had to configure the connection Manager tab (gave it the same name: OLEDB_CONNECTION), and then inside the script, I would use:

在脚本组件(数据流任务)中,我必须配置连接管理器选项卡(给它相同的名称:OLEDB_CONNECTION),然后在脚本中,我会使用:

String cn = This.Connections.OLEDBCONNECTION.ConnectionString;

and it worked just fine!

它运作得很好!

#1