change Source data file

[DebbieChapman]

I've found some code on the net that shows me how to change the source data file of a Bulk insert Task, Does anyone know How to change the source data file for an Excel 8.0 connection ?

This is the code I've got

Dim opkg, oBulkInsert
Set oPkg = DTSGlobalVariables.Parent

set oBulkInsert = oPkg.Tasks("DTS_BulkInsertTask_1&quot.CustomerTask

oBulkInsert.DataFile = DTSGlobalVariables("sFileName&quot.Value

Set oBulkInsert = Nothing
Set oPkg = Nothing

 

[JohnDTampaBay]

Here is some code taken from

http://www.sqldts.com

to change the filename of a text connection. It should be easy enough to modify for an Excel connection. Good luck!

Option Explicit

Function Main()
	Dim oConn, sFilename

	' Filename format - exyymmdd.log
	sFilename = "ex" & Right(Year(Now()), 2)
	If Month(Now()) < 10 Then sFilename = sFilename & &quot;0&quot; & _
	    Month(Now()) Else sFilename = sFilename & Month(Now())
	If Day(Now()) < 10 Then sFilename = sFilename & _
	    &quot;0&quot; & Day(Now()) Else sFilename = sFilename & Day(Now())
	sFilename = DTSGlobalVariables(&quot;LogFilePath&quot;).Value & _
	    sFilename & &quot;.log&quot;

	Set oConn = DTSGlobalVariables.Parent.Connections(&quot;Text File (Source)&quot;)
	oConn.DataSource = sFilename

	Set oConn = Nothing

	Main = DTSTaskExecResult_Success
End Function

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.