DTS with dynamically changing source File name

Question

DTS with dynamically changing source File name

jcollins

SSC-Addicted

Points: 481
More actions
January 10, 2007 at 8:30 am

#68172

We need to read a sybase file called eCallByCallStatYYYYMMDD
Example: eCallByCallStat20070109 for Jan 09, 2007 data.
Every day the telephony software creates a file for the previous
days events and stores them in a new table on sysbase.
Problem: We need to create 1 DTS package to read this file.
The source file name will change everyday.
How would you suggest solving this delima.
Thanks,
Jeff

jcollins

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

Use an Active X Script task to determine the name of the file, check if the file exists via the FileSystem Object, & set the DataSource of your source file object.

This example assumes your source file object is named "SybaseFile".

Function Main()

Dim dtPreviousDate

Dim strFileDate

Dim strFilePath

Dim objFSO

Dim Result

dtPreviousDate = DateAdd("d", -1 , Date() )

strFileDate = Year(dtPreviousDate) & Right("0" & Month(dtPreviousDate), 2) & Right("0" & Day(dtPreviousDate), 2)

strFilePath = "c:\eCallByCallStat" & strFileDate

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFilePath) Then

DTSGlobalVariables.Parent.Connections("SybaseFile").DataSource = strFilePath

Result = DTSTaskExecResult_Success

Else

Result = DTSTaskExecResult_Failure

End If

Set objFSO = Nothing

Main = Result

End Function

Pete Brown SSC Eights! Points: 984 More actions · Answer 2

Consider using a Dynamic Properties Task in your DTS package.

Within this task you can formulate, via an SQL statement, your filename (i.e. standard description + yesterdays formatted date and then set the generated filename as the 'Source' property for your input task.

We do this in reverse of your requirements, we need to generate a file from our DataWarehouse and email it to a customer. This file has a standard name + todays date. The whole job is one DTS package and works totally automatically.

gnana krishnan SSC Enthusiast Points: 160 More actions · Answer 3

you can use declare it as a global paramter and pass the filename as a parameter to the DTS while calling dtsrun (commandline).

so, you can write a simple wrapper program that generates the filename / reads the filename from a configuration file and pass it to the DTS as a global paramter.