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 |
|
capella07
Starting Member
46 Posts |
 Posted - 2007-09-10 : 15:13:34
|
| I'm trying to create a DTS package that does a number of things:1) Go to a specified folder and find all files in that folder (they will be all .txt files)2) Iterate through the files and parse out parts of the file name in each file (the parts of the file name determine what table and what record in that table the text goes in to)3) Write to the correct table and record based on step 2 above4) Move all of the files in the folder to another folderMy biggest stumbling block is step two. I'm trying to accomplish that in an ActiveX script task using VBScript. I'm very new to programming so I'm learning how to work with files, file names, and the text in them.I successfully wrote text from a text file in to a table in the DTS; plus, while learning about file handling, in VS2005 I created a module in a console app that finds all files in a folder and in a message box displays parts of the file name.I did that stuff using FileSystemObject, but VS2005's Intellisense doesn't appear to support VBScripting, so I could only figure out what to do based on Internet searches.I just need help figuring out how, in VBScript, to find all files in a given folder, parse out parts of the file name, and put the text in each of the files in tables based on the file name information.Or if anyone knows of a better way to accomplish what I'm trying to do, please let me know.Anyone's help would be greatly appreciated!Thanks=====================================f u cn rd ths, u cn gt a gd jb n prgrmng |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-11 : 12:53:11
|
| Do you want to do this in dts? It's simple in t-sql.Next simplest is to call a dts package from t-sql settnig the file and table names as global parameters.>> determine what table and what record in that table the text goes in toWhat are you actually doing?If the tables have different formats then you will have to constrinct the data transform task at run time (not difficult but I suspect it's not something you've done before).Sounds like you exopect the files to have a single row and you want to update a row in the table?If you're doing things in vbscript it's a lot easier to build it in VB first then you can look at the object models. Converting to vbscript is usually easy.To do this sort of thing in t-sql seehttp://www.nigelrivett.net/SQLTsql/s_ProcessAllFilesInDir.htmlhttp://www.nigelrivett.net/SQLTsql/ImportTextFiles.html==========================================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. |
 |
|
|
|
|
|
|
|
How to import text file to table in DTS