SSIS读取脚本任务上的平面文件连接

I'm working on a 2008 SSIS in which I need to read a flat file so that I can access its content (which has 3 directories paths), so I can store those 3 paths into variables.

我正在做一个2008年的SSIS，我需要读取一个平面文件，这样我就可以访问它的内容(它有3个目录路径)，所以我可以将这3条路径存储到变量中。

The flat file would be in 3 different servers, according to the instance I'm working on (dev,qa,production), so I can't just write the path into a variable because I'd have to rewrite that value every time I'd need to deploy the solution in a different instance.

平面文件将在3个不同的服务器上，根据我正在开发的实例(dev,qa,production)，所以我不能将路径写入一个变量，因为每次我需要在不同的实例中部署解决方案时，我必须重写这个值。

Something I've tried on the past is to read a flat file using the Directory.GetCurrentDirectory(), but I couldn't debug that and using the F5/run package on VS2008 didn't work (I've read that it doesn't work on VS but once you deploy the package it works fine, but I have no means to prove it but to try).

我试着在过去是读一个平面文件使用Directory.GetCurrentDirectory(),但我不能调试和使用F5 /运行包在VS2008中不起作用(我读过,它不工作VS但是一旦部署包效果很好,但是我没有办法证明,但尝试)。

So, I figured out that, If I can read the path saved on a flat file connection and save it in a string variable, I could modify the connection string value in the .config file once the package is deployed, and read its contents like a normal flat file.

因此，我发现，如果我可以读取保存在平面文件连接上的路径并将其保存在一个字符串变量中，那么在部署包之后，我可以修改.config文件中的连接字符串值，并像读取普通平面文件一样读取它的内容。

My problem is that I can't figure out how to read the connection string value, and I couldn't find anything online that pointed me in the right direction.

我的问题是我不知道如何读取连接字符串值，我在网上找不到任何指向正确方向的东西。

Thanks in advance.

提前谢谢。

3 个解决方案

#1

You'd want something like a C# Script task. You can modify the connection string dynamically there. Within the script you'd modify the value of (if I recall correctly) Dts.Connections.["YourConnection"].ConnectionString.

您需要类似c#脚本任务的东西。您可以在那里动态地修改连接字符串。在脚本中，您将修改(如果我没记错的话)ds . connections的值。

#2

To access connection managers informations from script tasks you can use Dts.Connections property, just declare a string variable, and read the connectionstring property:

要从脚本任务访问连接管理器信息，可以使用Dts。连接属性，只需声明一个字符串变量，并读取connectionstring属性:

string cs;
cs = Dts.Connections["myFlatFileConnection"].ConnectionString;

Reference:

参考:

According to this Microsoft Docs article:

根据这篇微软文档的文章:

"Connection managers provide access to data sources that have been configured in the package. For more information. The Script task can access these connection managers through the Connections property of the Dts object. Each connection manager in the Connections collection stores information about how to connect to the underlying data source." Read more (+examples)

“连接管理器提供对已在包中配置的数据源的访问。为更多的信息。脚本任务可以通过Dts对象的Connections属性访问这些连接管理器。连接集合中的每个连接管理器都存储关于如何连接底层数据源的信息。阅读更多(+例子)

#3

Since nothing seemed to work, I ended up doing the following:

由于一切似乎都不奏效，我最后做了以下事情:

Inserted the values I needed in a parameters table in the database
将所需的值插入到数据库中的parameters表中
generated an Execute SQL Task
生成执行SQL任务。
assigned the results of that task to the variables
将任务的结果分配给变量

It took me all day but I finally got it.

我花了一整天的时间才把它弄到手。

I followed this thread for references.

我沿着这条线寻找参考。

#1