1

I have a dataflow that is used to do transformation of multiple flat files from given folder using for each loop container. I have a flat file again as output file. The problem is that every time I execute the the job only the last file that got transformed will be stored in destination file. Is there a way in SSIS I can create individual transformed output file instead on overwriting on same one over and over again?

For. eg. I have 5 flat files ,test_1.txt,test_2.txt,test_3.txt ,test4_.txt and test_5.txt in a folder. After the job ran I can only see the data from last file test_5.txt being transformed in my destination file.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Kate
  • 445
  • 3
  • 9
  • 22
  • I'd have 2 variables for my paths, one for source and one for destination. Set your foreach loop "Retrieve file name" set to "Name and Extension" and assign that to another variable. On each of the file connectors, source and destination, via expressions set the connection string to the path variable + your filename variable that was used in the for each loop. You'll probably have to set delay validation on the destination and source in the data flow. – Tim Mylott Jul 23 '19 at 19:03
  • @TimMylott, does this give me individual output file (not overwrite )? – Kate Jul 23 '19 at 20:29
  • That should do it if your source and destination directories are different. Without seeing or knowing how your specific package is designed I can't speak to if there is anything else you would need to change or reconfigure. – Tim Mylott Jul 23 '19 at 21:00
  • Hi@TimMylott I am still getting same error ?[SSIS.Pipeline] Error: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E. – Kate Jul 24 '19 at 01:14
  • Only thing I could think of is go into the data flow, select the flat file destination, in properties set delay validation = true. – Tim Mylott Jul 24 '19 at 12:59
  • @TimMylott , I did set delay validation = true for my flat file destination. I am not sure I am still getting same error . – Kate Jul 24 '19 at 13:28
  • I posted a working example I walked through. Have a look and maybe that will show where something might not be setup correctly. – Tim Mylott Jul 24 '19 at 15:31

2 Answers2

1

Here's steps on a working example I tested.

Variables

I have 3 variables defined:

enter image description here

  • FileName - To be used in the foreach loop
  • DestinationDir - where are the files going
  • SourceDir - where are the files I want to process

Foreach Loop Setup

I have a foreach loop configured as: enter image description here

  • Expression for "Directory" set to @[User::SourceDir]
  • Retrieve file name set to "Name and extension"

Then under the "Variable Mappings": enter image description here

That means as the foreach loop is iterating over the files in the directory it will be setting the "Name and extension" of the file its on to the variable @[User:FileName]

Data Flow Task

The I add a Data Flow Task inside the foreach loop: enter image description here

Then inside the DFT I have a simple Flat File Source to Flat File Destination. We'll just pass the contents of each file to new files:

enter image description here

During initial development I'll manually pick one file to walk through setting each of the source and destinations. Then come back and change the connection managers and set an expression on the ConnectionString.

Connection Manager Expressions

SourceFile Connection Manager: enter image description here

  • ConnectionString gets an expression as: @[User::SourceDir] + @[User::FileName]

DestinationFile Connection Manager: enter image description here

  • ConnectionString gets an expression as: @[User::DestinationDir] + @[User::FileName]

Testing

I have 2 test files in my source directory and no files in my destination: enter image description here

After I execute my package I get success and also get new files in my destination: enter image description here enter image description here

Tim Mylott
  • 2,553
  • 1
  • 5
  • 11
0

There are ways to do what you are asking in SSIS with variables and expressions but there is an easier way to accomplish it using command line.

Since you are just consolidating a text files into 1 you can use a command prompt to better handle your issue:

copy *.txt output.txt
KeithL
  • 5,348
  • 3
  • 19
  • 25