Post reply

DTS with dynamically changing source File name

  • 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

  • January 10, 2007 at 11:07 am

    #682484

    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

  • January 11, 2007 at 5:11 am

    #682652

    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.

  • January 11, 2007 at 10:12 am

    #682730

    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.

     

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

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