SSIS Foreach循环容器动态文件名和路径，然后解压缩文件 [英] SSIS Foreach loop container dynamic file name and path , then unzip files

问题描述

我有一个包含多个文件的文件夹，名称为

I have a folder having multiple files with the name as

P04_20140326_1234.zip
P04_20130324_58714.zip
P04_20130808_jurhet.zip
P04_20130815_85893.zip
etc

名称的格式为P04_systemdate _ *.zip.

The name is in the format P04_systemdate_*.zip.

我想选择名称中包含currentdate的文件夹，然后先将其解压缩，然后将提取的文件中的数据加载到表中.例如:名为A.txt的文件进入表A，命名为B的文件进入表B，等等...

I want to pick the folder containing currentdate in the name and unzip it first and load the data from extracted file into the table.eg : file named as A.txt goes into table A, filenamed as B goes into table B and so on...

推荐答案

我想您已经完成了以下工作:

I guess you have already done the following:

1. 添加Data Flow
2. 在数据流中，添加flat file source和Ole_DB destination
3. 配置flat file source指向您的文件之一，并连接所有适当的列，以便数据从文件流到数据库.

1. Add a Data Flow
2. Inside the data flow, add a flat file source, and Ole_DB destination
3. Configure the flat file source to point to one of your files and connect all the appropriate columns so that data flows from file to database.

如果所有这些都已经起作用，那么让我们做一次For-Each循环

If all of this is already working, then let's do the For-Each loop

1. 创建一个变量(默认为程序包根级别)，并将其命名为字符串类型的CsvFileName
2. 添加ForEach loop(不是For循环)
3. 将循环类型更改为Foreach File Enumerator
4. 设置您的文件夹路径并查找* .csv
5. 在变量映射下，添加变量"User :: CsvFileName"，并将索引设置为0-这意味着从Foreach循环返回的所有文件名都将显示在变量中.
6. 在连接管理器"(底部)中，右键单击FlatFileSource，然后选择属性
7. 将DelayValidation设置为真"
8. 单击表达式"，然后单击省略号
9. 设置ConnectionString属性以使用"CsvFileName"变量

1. Create a variable (default to package root level) and call it CsvFileName of type string
2. Add a ForEach loop (not a For loop)
3. Change loop type to be a Foreach File Enumerator
4. Set your folder path and look for *.csv
5. Under Variable mappings, add the variable "User::CsvFileName" variable, and set the index to 0 - this means that all file names returned from the Foreach loop will show up in the variable.
6. In the Connection Managers (bottom) right click on the FlatFileSource, and choose properties
7. Set the DelayValidation to "True"
8. Click on Expressions, and then click on the ellipsis
9. Set the ConnectionString property to use the "CsvFileName" variable

运行它.这应该加载所有文件.现在，如果您只想将其限制为某个日期，则可以执行以下操作:

Run it. This should load all files. Now, if you just want to restrict it to a date here's what you do:

1. 创建一个名为"FilterDate"的变量
2. 将值设置为要设置的日期(例如20140322)
3. 在ForEach循环中，转到集合"，然后单击表达式"，然后单击省略号
4. 将FileSpec属性设置为"*" + @[User::FilterDate] + "*.csv"

1. Create a variable called "FilterDate"
2. Set the value to whichever date you want to set (20140322, for example)
3. In the ForEach loop, go to Collections, and then click on Expressions, then click the ellipsis
4. Set the FileSpec property to be "*" + @[User::FilterDate] + "*.csv"

现在它将仅过滤所需的文件.

Now it will only filter the files that you want.

这篇关于SSIS Foreach循环容器动态文件名和路径，然后解压缩文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！