Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2007-07-10 : 22:25:54
|
| Guys,I was using Bulk insert to Import Fixed length Text file into tables without any issues.Recent requirement came in to process files which are not fixed length Like below"aaaa","bbbb""aaaa""aaaa","aabb","aa"Now I can't use Bulk insert in that situation. So I read about DTS, so I need to ask couple of questions.DTS is that the best way to move forward in that situation?If yes then How can we make a DTS which will deal with Multiple files with Differant Names?I read that you have to have one standard file name which could be assigned to one DTS packge?Please advice.Thanks in advance.SKR |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-11 : 12:34:12
|
| >> How can we make a DTS which will deal with Multiple files with Differant Names?You can rename the file before the package runs so that it always works on the same name.Or you can change the package at run time to pick up a different filename - I usually set up a global variable for the file name and call the package with that filename - the filename loaded in the package is the name in the global variable.But using bulk insert you can insert into a single column table then parse the data from there - that would be the simplest way.seehttp://www.nigelrivett.net/SQLTsql/ImportTextFiles.html(could be a temp table rather than global tem table - I probably did it before bulk insert was available then changed it).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2007-07-11 : 17:05:50
|
| Thanks nr...I think it is best to use DTS then.. I created DTS on SQL Server..Text files want to process (a.csv, b.csv, c.csv)Dynamic Properties->TextFile->sql Server (Text file has default file attached as a.csv)Dynamic properties has String Global Variable (FileName) set..EXEC master.dbo.xp_cmdshell 'dtsrun /S NPLSQL01 /U sa /P password /N myImportDTS1 /G {8924A67A-9AB3-4AEE-90E1-6B442C423BD4} /A FileName:8=d:\MYIMPORT\c.CSV /W 0'In above DTS, I am trying to execute C.CSV, but actually it DTS is processing default file (a.csv), it looks like it is not accepting Global Variable.Am I missing something?Thanks in advanceSKR |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2007-07-11 : 19:57:23
|
| I got that working now..Thanks.. Wasn't mapping Global variables properly..Thanks guys for your help...SKR |
|
|
|
|
|
|
DTS and Variable Length Text File ? Possible?