February 20, 2002 at 9:20 am
I need to import several text files into a database.
I have created a script that will create a collection of the files, but I need assistance for the code that will run each file in the collection through the rest of the DTS Steps.
Function Main()
Dim objFSO, objFolder, objFile, colFiles
Set objFSO = CreateObject ("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder (DTSGlobalVariables("gvFilePathRoot").Value)
Set colFiles = objFolder.Files
If colFiles.Count > 0 Then
For Each objFile in colFiles
If UCase(Right(objFile, 4)) = ".TXT" Then
objFile.Copy "Destiantion"
'I wan to run the first text file in the colection through the rest of the DTS Steps here.
Next
Else
MsgBox "File not found"
End If
Set colFiles = nothing
Set objFolder = nothing
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Any thoughts?? Or a completly different way to accomplish this?
Edited by - jgee on 02/21/2002 08:41:16 AM
-JG
February 20, 2002 at 9:46 am
What format are the text files in?
I would try to do this using bcp.
Treat the import as a separate step to anything else and have the staging table the same format as the text file. In this way you can change the import method without affecting anything else.
Import into a separate database and once the import is complete use stored procedures for checking the dta and merging to the production tables.
Get the source to produce some summary info (at least a record count) so that you can check the data imported against what should be there.
Cursors never.
DTS - only when needed and never to control.
February 20, 2002 at 10:06 am
Are these different formats of files? Do the names changes? If there is some pattern here you can do it. Look at the FileSystemObject. It will allow you to get a collection of all files and you can then work with them
I might also suggest building different packages for each type of file and calling them for that type fo file only.
Steve Jones
February 20, 2002 at 10:09 am
Can I use a wildcard with BCP to import all the text files into the table?
-JG
February 20, 2002 at 10:13 am
Steve,
They are all *.txt, and they are named sequntialy after a date i.e 01012001 (being mmddyyyy)
Through FSO I am able to place them all in a file collection. Then I need to take each one out of the collection and start processing them through a multi-step DTS package.
-JG
February 20, 2002 at 10:17 am
I do this as well and have some writing going on for this. I have a separate package that does the import for my files since all of them are the same format, but different names.
Two choices:
1. rename each file to a standard "name" and then call the package to import it. You can do this in an activeX script.
2. Load the package and alter the connection source to match the name of the file in a loop and then execute it.
Ref:http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp
Steve Jones
February 20, 2002 at 10:46 am
When I have the file collection.
If colFiles.Count > 0 ThenFor Each objFile in colFilesIf UCase(Right(objFile,4)) ".TXT" Then
What would be the proper SQL Syntax after my Then to: BCP/Insert the txt files into tmpTable? Using the ActiveX Script?
-JG
February 20, 2002 at 1:17 pm
Not sure about the syntax for the BCP. Guess you'd need a shell of some type.
To execute another package:
Dim oPkg, intError
Set oPKG = CreateObject("DTS.Package")
oPKG.LoadFromSQLServer strServerName, , , 256, , , , strPackageName
intError = oPKG.Execute
Steve Jones
February 20, 2002 at 1:48 pm
What about the syntax for the Object BulkInsertTask?
Could I do somthing similar to what you are performing with opening another package?
Would it be something like?:
Dim oTask
Set oPKG = CreateObject("DTS.BulkInsert Task")
oTask.Execute ????????What wuold be the parameters to define?????
-JG
February 20, 2002 at 3:30 pm
Perhaps. You'd have to have a reference to the existing package and then add an object to it.
Is there some reason you do not want to call another package?
Steve Jones
February 21, 2002 at 7:59 am
I can not get calling another package to work.
When I call the name of the other pacakge to execute i get:
The Specified DTS Package('NAME = 'Step2';ID.VersionID = {[not specified]}.{[not specified]}') does not exist Do I need to supply the GUID and Version ID along with the name?
-JG
February 21, 2002 at 11:18 am
Is the pacakge on the same server as this is executing? That is the exact code I am using and I place the name of the package in a global variable that is loaded into the strPackageName var.
Can you post the whole ActiveX script that runs the package? One thing I noted is that the password on a pacakge sometimes causes issues. the 2nd pacakge I run doesn't have a pwd.
Steve Jones
February 21, 2002 at 12:41 pm
Yes, both pkg are on the same server. Here is the code:
Main = DTSTaskExecResult_Success
Dim oPkg, sServer, sPkgName
Set oPkg = CreateObject("DTS.Package")
sServer = "labsql1"
sPkgName = "Step2"
oPKG.LoadFromSQLServer sServer, , , 256, , , , sPkgName
oPkg.Execute
oPkg.Uninitialize
Set oPkg = Nothing
-JG
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply