Getting error connecting to source

[Eyespi20]

When running the following code, I get an error "Microsoft Data Transformation Services (DTS) Package connection ('Text File(Source)')was not found. I do have a text source connection defined in the package. What did I do wrong?

Here is my code

Function Main()

	Const DTSSQLStgFlag_UseTrustedConnection = 256
	
	Dim oPKG, oStep
	Dim i, bStatus
	Dim sFolder

	' path must end in \
	sFolder = "\\Ttc_datamart\Econz\NotifierCsvFiles\"

	Dim fso, f, f1, fc, s
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set f = fso.GetFolder(sFolder)
	Set fc = f.Files

	For Each f1 in fc
		if right(lcase(f1.name),4)= ".csv" then
			Set oPKG = CreateObject("DTS.Package")
			'oPKG.LoadFromSQLServer "Africa", , ,DTSSQLStgFlag_UseTrustedConnection,  , , ,  "EconUpdate"
			oPKG.LoadFromSQLServer "Africa","sa", "xxxx",DTSSQLStgFlag_Default,  , , ,  "EconUpdate"
			Set cn = oPKG.Connections("Text File (Source)")
			cn.DataSource = sFolder & f1.name 
			oPKG.Execute

			bStatus = true
			For Each oStep In oPkg.Steps
				If oStep.ExecutionResult =DTSStepExecResult_Failure Then
					bStatus = True
				End If
			Next
			oPKG.Uninitialize()
			Set oPKG = Nothing

			if not bStatus then
				MsgBox "An error happened while importing " & sFolder & f1.name & ". The error(s) are logged in " &sFolder & replace(lcase(f1.name),".csv","_error.log")
			else
				Set oPKG = CreateObject("DTS.Package")
				oPKG.LoadFromSQLServer "africa", "sa","xxxx", DTSSQLStgFlag_Default,  , , ,  "Read TRI Text files into econupdate"
				Set cn = oPKG.Connections("Text File (Source)")
				cn.DataSource = sFolder & f1.name 
				oPKG.Execute
				For Each oStep In oPkg.Steps
					If oStep.ExecutionResult =DTSStepExecResult_Failure Then
						bStatus = True
					End If
				Next
				if not bStatus then
					MsgBox "An unknown error happened while importing " & sFolder & f1.name 
				end if 
				oPKG.Uninitialize()
				Set oPKG = Nothing
			end if
		end if
	Next

	Main = DTSTaskExecResult_Success
End Function

Thanks!

Margaret

 

[rac2]

I am not familiar with the DTS.Package interface, but "Text File (Source)" seems an odd value for the parameter in this line. It seems like it should be the name of an actual connection rather than the description of the type of connection.

Set cn = oPKG.Connections("Text File (Source)")

What is the name of your "text source connection defined in the package". Maybe you named it AfricaSource. Then

Set cn = oPKG.Connections("AfricaSource")

 

[Eyespi20]

I've tried it with the "name" given the source as well and get the same error. The text files are not on Africa -- that's the SQL server name.

Thanks

Margaret

 

[gradley]

Hi Margaret,

Are you running this interactively or from SQl Server Agent.

I notice you are using a UNC naming convention. Is it possible the package (based on the service account if "SQL Agent" or current user if interactive) does not have permissions to this network drive?

 

[Eyespi20]

I'm initially running it from enterprise manager, but want to schedule it to run independently.

I'm the DBA and have all rights. The job itself is running under SA priveliges.

Margaret