July 9, 2002 at 3:32 pm
Setting Sql 7. DTS Designer.
I create a new connection, go to Connection properties, and specify an input file. That approach worked fine until my latest project. For that project the name of the (source) file varies from run-to-run! Can I pass the input filename in as a Global variable and have DTS use it? Or is there a better approach?
TIA,
Bill
July 9, 2002 at 6:27 pm
Sure you can...I do it and it works slick...
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim responsesuccess
responsesuccess = 0
responsesuccess = ImportFile(DTSGlobalVariables("gvfilepath").Value)
If responsesuccess <> 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Function ImportFiles(filepath, fileext)
'
'Accepts: filepath - string variable to hold full path to file (pass in global variable "gvfilepath")
'fileext - global variable holding extension of files we are looking to import, eg. ".asc"
'
'Returns: 1 if successful
' 0 if fails.
ImportFiles = 0
Dim fso
Dim objFolder
Dim colFiles
Dim objFile
Dim objPackage
Dim objConn
Dim i'counter used for test purposes
blnLogIntotblCDRImportBatch = 1
Set objPackage = CreateObject("DTS.Package")
objPackage.LoadFromSQLServer "your server name here","","","256",,,,"name of package that does the actual import of the file"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objfolder = fso.GetFolder(filepath)
Set colFiles = objfolder.Files
i = 0
If colfiles.Count > 0 Then
For Each objfile in colfiles
If UCase(Left(objfile.name,2)) = "X5" Then
If UCase(Right(objFile.name,4)) = ".ASC" Then
'Check file size - if greater than 0 then import to db table:
If objFile.Size > 0 Then
'Run package here and load table:
objPackage.GlobalVariables.Item("gvcurrentimportfile").Value = filepath & "\" & objfile.Name
objPackage.GlobalVariables.Item("gvfileid").Value = i
Set objConn = objPackage.Connections("Connection 1")
objConn.DataSource = objPackage.GlobalVariables.Item("gvcurrentimportfile").Value
objPackage.Execute
i = i + 1
End If
End If
End If
Next
End If
'MsgBox "Total Files Processed: " & Cstr(i)
objPackage.Uninitialize()
Set objPackage = Nothing
Set fso = Nothing
Set objfolder = Nothing
Set colFiles = Nothing
Set objFile = Nothing
Set objConn = Nothing
ImportFiles = 1
End Function 'ImportFiles()
quote:
Setting Sql 7. DTS Designer.I create a new connection, go to Connection properties, and specify an input file. That approach worked fine until my latest project. For that project the name of the (source) file varies from run-to-run! Can I pass the input filename in as a Global variable and have DTS use it? Or is there a better approach?
TIA,
Bill
Michael Weiss
Michael Weiss
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply