Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How can I change the source data file for a Bulk Insert Task
By Darren Green
Version 7.0+/2000
Level Beginner

To change the name of the source data file at runtime you can use an ActiveX Script Task. In the following example the new filename is read from a global variable called ImportFilename. Please ensure you change the bulk insert task's name value ("DTSTask_DTSBulkInsertTask_1") to match that in your package.

' Pkg 231
Option Explicit

Function Main()

	Dim oPkg, oBulkInsert
	Set oPkg = DTSGlobalVariables.Parent
	Set oBulkInsert = oPkg.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTask

	oBulkInsert.DataFile = DTSGlobalVariables("ImportFilename").Value

	Set oBulkInsert = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

If you are using SQL Server 2000 then a similar approach can be taken using Dynamic Properties Task.