用于平面文件源的 sql server 数据工具动态文件名 [英] sql server data tools dynamic file name for flat file source

问题描述

我有一个 ssis 包，它可以将足够简单的数据从文本文件导入到数据库表中.导入文件名需要配置为动态源，因为文件名会发生变化.所以我的文件名将具有以下模式:bookNames_Shopping_05_02_2016.txt --> bookNames_Shopping_ 将恒定，但日期戳将改变.

我设置了一个名为 filename 的变量，并且在连接管理器的属性窗口中，我将 ConnectionString 属性设置为 @[User::filename].如何设置文件名变量以读取连接管理器指向的文件夹中的文件名?附言我知道它可以在 For Each 循环中完成，但由于我没有多个文件，只有一个需要处理的文件，我 **不想 ** 不想使用 for each 循环.

文件的完整路径:\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt

解决方案

应用循环的替代方法是使用表达式.假设正在读取的单个文本文件的名称中包含 SSIS 包将处理它的同一日期，您可以执行以下操作:

1. 创建一个名为FileName"的字符串类型的用户变量.
2. 创建一个名为bookNames_Shopping"的新连接管理器，指向当前的完整路径\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt".
3. 选择刚刚创建的新连接管理器并为其打开解决方案资源管理器.
4. 选择表达式.当属性表达式编辑器打开时，转到属性列并从下拉列表中选择连接字符串.在同一行的表达式"下的下一列中，选择浏览"按钮.这将打开表达式生成器窗口.
5. 在表达式:"下方的文本框中，输入以下内容:

<块引用>

`"\XYZYUC3312B6\SHARE\Bound\" + @[用户::文件名] + "_" +
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm", GETDATE()), 2) + "_" +RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd", GETDATE()), 2) + "_" +(DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + ".txt"

选择左下角的评估表达式"按钮.如果这有效，它应该显示您的完整文件路径，包括当前日期的串联.

<块引用>

\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_04_2016.txt

选择确定"按钮，一切就绪.我在 SQL Server 2008R2 下的 BIDS 中运行了它.如果您使用的是 SQL Server 2012，那么在 Visual Studio 2010 中，您可以直接为 FileName 变量设置相同的表达式.

从这里开始，只需在我们的数据流中应用 bookNames_Shopping 连接字符串.

I've got a ssis package that does a simple enough data import from a text file into a database table. The import file name needs to be configured as a dynamic source since the file name will change. So my file name will have following pattern: bookNames_Shopping_05_02_2016.txt --> bookNames_Shopping_ will be constant but the date stamp will change.

I set up a variable called filename, and in the connection manager's properties window, I set the ConnectionString property to @[User::filename]. How do I set the filename variable to read the file name that is in the folder that the connection manager is pointed to? P.S. I know it can be done inside a For Each loop but since I don't have multiple files but a single file that needs to be processed, I **don'**t want to use the for each loop.

Full path for the file: \XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt

解决方案

An alternative to applying a loop is using an expression. Assuming the single text file being read contains in its name the same date in which the SSIS package will process it, you can do the following:

1. Create a User variable called "FileName" of type String.
2. Create a new Connection Manager called "bookNames_Shopping" pointing to the current full path "\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt".
3. Select the new Connection Manager just created and open Solution Explorer for it.
4. Select Expressions. When the Property Expressions Editor opens, go to the Property column and select Connection String from the drop-down. In the next column under Expression on the same line, select the browse button. This will open the Expression Builder window.
5. In the text box just below "Expression:", enter the following:

`"\XYZYUC3312B6\SHARE\Bound\" + @[User::FileName] + "_" +
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "_" + (DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + ".txt"

Select the Evaluate Expression button in the lower left. If this works, it should display your full file path including a concatenation of the current date.

\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_04_2016.txt

Select OK button and you're all set. I ran this in BIDS under SQL Server 2008R2. If you're using SQL Server 2012, then in Visual Studio 2010, you can set up the same expression directly for the FileName variable instead.

From this point, just apply the bookNames_Shopping connection string in our dataflow.

这篇关于用于平面文件源的 sql server 数据工具动态文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！