FlatFile output changes problem

[JScannell]

I wonder if any of you have had this problem. I initially assign a flat file target to my DataFlow. At the time of the initial placement, some columns are in a certain position of the output row. I decide to make some changes so I add Data Conversion and Derived Column steps. Of course, this places the new columns at the end of the FlatFile mappings area. Here's where things get gnarly.

First of all, the actual placement of the new fields is to be somewhere else in the output, like where their source field used to be. So I delete the line between the original and connect the new. When I run it, I get both. So I have to go back and try to figure out how to rearrange things. Finally, all I can do is delete the FlatFile step and install a new one. But I still have the sequencing problem. How do I rearrange either the input fields to the FlatFile step, or change the output's order?

Thanks in advance,
Jerry

Jerry Scannell

 

[MDXer]

I believe to rearrange the ordinal position of the column not only will you need to delete your destination but also the connection from your connection mamanger. The definition of the flat file is contained within the connection manager and not the destination, the destination only exposes the connection. You can try to modify your connection manager but in the past I have had issue with rearranging info contained in the connection. If not delete connection file and recreate.

 

[JScannell]

That's what I was afraid of. Is it possible to modify the potential output columns before adding in the new flatfile target so everything will be in the desired sequence?

I don't mind deleting and readding the flatfile step if I have to, but there is still the column sequencing issue. From what I can see, as DataConversion and DerivedColumn steps are added, the final output column set goes in that order. and I need to somehow change it.

Thanks,

Jerry Scannell

 

[MDXer]

in your dataflow buffer the column order really is not relevant. create your flat file with the proper column order and make sure your dataflow columns map correctly to your flat file columns. Typically using the exact same name will accomplish this automatically.

 

[JScannell]

You said: "create your flat file with the proper column order and make sure your dataflow columns map correctly to your flat file columns"

How do you create a flat file connection object with columns in a user-specified order? It always seems to take the order of the fields in the DataReader source, followed by the DataConversion column, and then the DerivedColumn (since that's the order of my DataFlow objects).

Thanks,

Jerry Scannell

 

[MDXer]

After walking through the process, sorry it has been a while since I've done this type of task, it is not as straight forward as I believed but I will be opening a suggestion with MS.

It appears you have 2 options.

1) Create a New Flat File Connection in connection Manager. This will open the editor allowing you to add your path and file information. The advanced tab will have no columns if you do this as a new connection manager. Input your columns in the order you need and assign the data type info. Yes this really will suck if you have a lot of columns.

The second option would be to edit the xml directly. I have been trying to do this as an example but can't really devote the needed time. I'll try to prove this out tonight and document it.

 

[JScannell]

Voila! Your option 1 did the trick.

I had been moving a FlatFile Destination onto the DataFlow area first. It wouldn't let you assign columns that way. I had to connect my last transformation to it first. That is what caused the fixed column ordering. By designing the connection object outside of the DataFlow, allowed me to order things the way I wanted to.

The only caveat with it is you have to know the actual spelling of all the fields (from the DataReader, DataConversion, and DerivedColumn) before you start. But once you do that you can create your own desired output order by creating the connection manager separately.

The other sort of annoying thing was that I created the Connection with tab-delimited columns, but when I assigned the FlatFile to it it changed it to comma-delimited. I had to go back into the connection manager and change it. I don't know why that happened.

Thanks so much for coming up with that solution.


Jerry Scannell

 

[MDXer]

No Problem