SSIS Package Stubborn Flat File Connection Manager

1
votes

I receive a pipe-delimited flat file each week that has 50 columns. I am trying to use SSIS to take that file, delete the last 3 columns, then insert the remaining data into a new pipe-delimited flat file. At first I thought this would be very simple, but I've got a stubborn flat file connection manager. It keeps reverting back to the inbound file layout with the extra columns, and also keeps going back to a comma delimited file when the outbound file needs to be pipe delimited.

The way I'm "deleting" the unneeded columns is by just removing them from the inbound flat file connection manager, so they aren't listed in the output columns from the flat file source, and they don't show to be on the input columns of the flat file destination.

The file name of both files is dynamic...not sure if that's having something to do with it.

I have delay validation set to true for both, but I'm not sure what else to try. I've also tried deleting all of it and adding back in the connection managers and the files.

Is there some issue with having 2 flat file connection managers, one for the source and one for the destination? Is there a setting I'm missing?

2
ssis
As far as I know, you cannot alter the column setup in a flat file. Why not create a flat file connection with the layout you require (i.e. minus the 3 columns). Then (1) write to the new file. (2) Delete the unwanted file (3) Rename the new flatfile with the old filename? – MiguelH

2 Answers

2
votes
  1. Delete your connection managers and Source/Destination (effectively start over)
  2. Add Flat File Source (FFSRC) with new FF Connection
  3. Set up FFSRC as needed (pipe delimited, headers, etc) - don't delete any rows
  4. After clicking "OK", you're back in the Data Flow. Right click on your Flat File Source, and click "Show Advanced Editor"
    advanced editor option
  5. Go to "Input and Output Properties" tab, then expand the FFSRC Output/Output Columns. Click a column, then click "Remove Column".
    remove column
  6. Add your Flat File Destination (FFDST) with a new connection manager, and map the inputs.

Your destination shouldn't have those columns now.

If the Flat File connection seems to be resetting because of dynamic names, look into supplying them as expressions/variables.

To do that, click on the Connection Manager node for your source/destination (not the data flow node), then in Properties, expand Expressions. You will want to make the ConnectionString dynamic via a variable.

0
votes

What do you mean by

It keeps reverting back to the inbound file layout with the extra columns, and also keeps going back to a comma delimited file when the outbound file needs to be pipe delimited.

Have you tried directly referencing the file, then setup the type of file (ragged right, delimited or fixed width), then applying the expression to the connectionstring property of the connection manager?

I suggest ragged right, I can specify whatever 'column' and width I want to.