DTS - Text File Source - using a Variable storing the file location

[Dugy]

Is it possible to pass a value to a DTS defining the path to a file, and somehow using that variable within the DTS transformation to say "the text file is here".

Your help much appreciated

Dougie

 

[quatermass]

Hi Dougie,

If you set up a global variable and use an initial activex to read the text file, you can then set the properties of the text file connection to use the global variable.


I have got an example of how to do this, but have not got it with me at the moment. I will post it when I am back in the office.



Kevin

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[JamesLean]

You can use a Dynamic Properties task to change details of any task at runtime. In your example, you could execute a task which created the dynamic filename using a SQL query (eg, I have used this where the filename will be a certain configuration of a date). --James

 

[Dugy]

Thanks Guys, exactly what I’m after.

Cool feature as well.

Dougie

 

[quatermass]

Hi Dougie,

The following is the code for the activex script:


START CODE
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
'declare variables
dim expect_path, tempPath, txtPath 'various paths: expected path (global variable), path and file name, from the text file
dim txtDB 'database name from text file
dim fso, f1, textstream 'objects for reading the text file
dim oConn 'connection object for the text file
dim stat 'status of package (success or fail)

'set up variables for use
expect_path = DTSGlobalVariables("the_path&quot.value
set fso = createobject("scripting.filesystemobject&quot

'check the existance of the file path.txt
tempPath = expect_path +"path.txt"
IF (fso.FileExists(tempPath)) THEN
set f1 = fso.getfile(tempPath)
set textstream = f1.openastextstream
txtPath = textstream.ReadLine + "data\"
else
stat = DTSTaskExecResult_Failure
end if

'check the existance of the file database.txt
tempPath = expect_path + "database.txt"
IF (fso.FileExists(tempPath)) THEN
set f1 = fso.getfile(tempPath)
set textstream = f1.openastextstream
txtDB = textstream.ReadLine
else
stat = DTSTaskExecResult_Failure
end if


if stat <> DTSTaskExecResult_Failure then
'change the name and location of the databases
Set oConn = DTSGlobalVariables.Parent.Connections(&quot;Microsoft Access&quot
oConn.DataSource = txtPath + txtDB
Set oConn = DTSGlobalVariables.Parent.Connections(&quot;Microsoft Access 2&quot
oConn.DataSource = txtPath +txtDB
Set oConn = DTSGlobalVariables.Parent.Connections(&quot;Microsoft Access 3&quot
oConn.DataSource = txtPath +txtDB

'report task success
stat = DTSTaskExecResult_Success
end if

Main = stat
End Function

END CODE **************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************