Transform Data Task
-
I am using a Transform Data Task to import data from a text file into my database. In the properties section of the source text file, I have selected "Fixed Field" as my file format, and I've manually set the column definitions. There are over 400 columns in this text file. After the file is imported, I have a very complex VB Script that manipulates the data and puts in its proper places, specifically referencing the column numbers from the text file in the VB code.
Now the rub: The company providing me with this text file wants to add some more columns to the middle of it's data file, as the needs of the program have changed slightly. I have been telling them that any new data must be added to the end of the text file, as entering anything in the middle of the text file would completely screw up all of my column definitions, forcing me to re-establish the columns, and in turn (ugh) rewrite the VB script; but that it is easy to add new column references on to the end of the text file. They said they had a hard time believing that this was true. I agree with them in that adding new fields to the end of the data file does compromise the "readability" of the text field itself; and that it would be much better to put the new data in the middle, if it could be relatively painlessly done.
So I'm asking: Can you insert columns of data into a text file in this situation, and name the new columns in such a way that will maintain the integrity of the column definitions as they previously existed?
Thanks
deek
-
Try this problem with a simple fixed length format file of let say two cols then add a thrid col to the middle of the file you'll see that when you open the text file source you will have to fix the fixed length properties to cater for the new col. when you click ok it will ask if you want to delete your transofrms say no. open the data pump and gol to the transforms tab and you should be able to edit and move your transforms around.
-
well the only way i'm up for doing this readjustment is if i can get SQL to automatically re-route the references of every column to the right of the insert, in this case, approximately 400 columns.
if there's a way to do this, i'd like to learn it, considering that i will need to add new columns to this text file regularly; and to re-reference 400+ columns, as well as re-write the associated VB code every single time i make a minor adjustment, would be incalculably tedious.
any other suggestions?
-
Do you get any sort of format file or header record that can be used to determine the columns?
If so, and you're using SQL2K, have a look at the properties of the Text file connection and the DataPump task. You might be able to manipulate/rebuild the column list based on the formatting info.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
from what i've seen you going to have re-reference some of the old cols but you won't have re-write any of the code only edit which which cols they point too.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply