Hi
I’m sure this is a simple thing, but I can’t find anything when I look through my enterprise manager import tool.
I basically need to get only specific records from the file (otherwise the table would grow quite large quite quickly) but as far as I can tell the DTS/import is designed to get all records.
I might be inclined to solve this problem through pre-filtering so the data fed to DTS/import is just what you want to import. There are lots of ways to do this, but my favorite is ‘awk’, which is traditionally a unix tool but windows ports can be found.
The following awk command means “using a pipe as the input field delimiter, print column 3, 8 and 9 from each line with pipes in between”:
awk -F'|' '{print $3"|"$8"|"$9}' yourinputdata.txt > yourfiltereddata.txt
The shell in windows may or may not require different escaping and quoting of the various characters (like pipe, for example); I don’t know all the shells’ various rules on that and always have to experiment, myself.
ETA: I did a little DTS experimentation one time about 8 years ago and if I recall correctly, you can have one of your import steps be to run an external command like this, so once you have the exact process figured out it can be automated.
IT has been a few years since I have used DTS, but I am almost positive that it had some form of filtering capability. Unfortunately, we use 2005 now, so I can’t go look for you.
This article may help, but you have to register. I didn’t, so I don’t know how helpful the answer is, but it was a very similar question to yours. Looks like a paid subscription, but with a 7 day free trial.
I don’t know what level of comfort you have with writing T-SQL code, but I would probably load the entire file into a “staging table”, and write a stored procedure to load the data into the final destination. You can do the filtering in the stored procedure. I am pretty sure that DTS can call stored procedures, so it would all still be controlled in one package.
The more I think about it, the more I feel like this has to be true. Lobsang, I would recommend looking for a way to do this in DTS directly and only use a solution like mine if necessary.
Yeah you want to do that. File -> Temp Table -> Filter -> Real Table -> Drop Temp Table
I haven’t worked with DTS in some time, but as I recall, you can pre-filter data with it. If you’ve got access to SQL 2005, you can also run SSIS against a 2000 box to clean your data.
However, if you’re at the level of trying to use the import/export wizard, you’ve got a long learning curve ahead for either of those options.
If I were doing this, I’d probably import the whole file into a temporary staging table, query for the desired rows, and insert them into your main table.
[on preview]Or what they said.[/op]
Well, I’ve poked around briefly, and I think that DTS doesn’t have filtering of its own, though it can leverage SQL filtering if that’s available on the data source. The plain ‘text file’ data source option doesn’t support this.
You might be able to set up a text ODBC connection string or something similar, and use that as your data source - ODBC text supports simple SQL ‘where’ clauses if I remember clearly.
Otherwise, I’d recommend the ‘filter the file manually into a new text file, and THEN import it’ option.
So, did you ever get this working?