SQL Server Performance

DTS ActiveX script - Can this code be shortened

Discussion in 'SQL Server DTS-Related Questions' started by Jaji03, Dec 5, 2007.

  1. Jaji03 New Member

    Hello all,
    I just completed my first DTS package [:D]. I wrote an activeX script in the first workflow properties section to avoid execution if certain requirements are'nt met. The whole package works wonders, but I'm just not comfortable with code because it was really hard for me to debug it when I was troubleshooting. I think that the code can be done where it's easier to follow. Perhaps case statements? Please take a look. Any advice will be greatly appreciated :)
    '**********************************************************************************************************************************************************************************************
    ' Visual Basic ActiveX Script
    '**********************************************************************************************************************************************************************************************
    Option Explicit
    Function Main()
    Dim FSO,objSourceFile,dteLoadDate,dteFileDate,strYear,strMonth,strDay,strDateLastMod,strCurDateLastMod,strFilePath,Conn,RecSet,strSQL,intCount
    Set FSO= CreateObject("Scripting.FileSystemObject")
    Set Conn=CreateObject("ADODB.Connection")
    Set RecSet=CreateObject("ADODB.Recordset")
    '**********************************************************************************************************************************************************************************************
    'This VBscript will do the following:
    '1. Check to see if day is a Friday.
    '2. Check to see if Source File is available
    '3. Determine if data in DB needs to be appended or replaced.
    '**********************************************************************************************************************************************************************************************
    'Store prior day's date. This is the date the file was loaded as text file.
    'dteLoadDate = InputBox("Enter load date mm/dd/yyyy","File Date")
    dteLoadDate = FormatDateTime(Now(), vbShortDate)

    'Store FileDate. This is the date that's within the source file.
    dteFileDate=DateAdd("D",-1,FormatDateTime(dteLoadDate, vbShortDate))
    'Breakdown date to use them for the file's path name
    strYear = Year(dteLoadDate)
    If Month(dteLoadDate) <10 Then
    strMonth="0" & Month(dteLoadDate)
    Else
    strMonth = Month(dteLoadDate)
    End If
    If Day(dteLoadDate) < 10 then
    strDay="0" & Day(dteLoadDate)
    Else
    strDay = Day(dteLoadDate)
    End If
    strFilePath="\UsvhTestTest_" & strMonth & "_" & strDay & "_" & strYear & ".txt"
    'On Fridays, only run if it's the 1st day of the month.
    If Weekday(dteLoadDate)=2 and Day(dteLoadDate)<>1 Then
    Main=DTSStepScriptResult_DontExecuteTask
    Else 'Date is Not a Friday OR it's Friday the 1st
    'Check to see if Source File exists. If source file is still not available after 5:00pm then still execute task and to enter status into log
    If FSO.FileExists(strFilePath) Then 'File avail. - make sute it's not in db already
    Set objSourceFile=FSO.GetFile(strFilePath) 'Set file to read last modified date
    strDateLastMod=objSourceFile.DateLastModified
    'Connect to SQL Server DB and make sure that records for date don't already exist.
    Conn.Open="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CL_MIS;Data Source=PhilDev"

    'SQL Statement will count number of records for specified date (FileDate) as a way to find out if Request data is already in Table
    strSQL= "SELECT COUNT(*) AS 'TCount' FROM [dbo].[t_STAGING] WHERE FileDate= '" & dteFileDate & "'"
    RecSet.Open strSQL, Conn,1,1
    intCount=RecSet.Fields("TCount")
    RecSet.Close
    If intCount > 0 Then 'File already in DB, Get Last modified date from source to make sure it didn't change

    'Compare date last modified with strCurDateLastMod (which is the date in the staging table)
    strSQL="SELECT TOP 1 LastModDate FROM [dbo].[t_STAGING] "
    RecSet.Open strSQL,Conn,1,1

    strCurDateLastMod=RecSet.Fields("LastModDate")
    If strDateLastMod=strCurDateLastMod Then 'File is the same, don't execute task
    Main=DTSStepScriptResult_DontExecuteTask
    Else 'File is different Execute Task
    DTSGlobalVariables("gblDteLoadDate").Value=dteLoadDate
    DTSGlobalVariables("gblDteFileDate").Value=dteFileDate
    DTSGlobalVariables("gblStrFilePath").Value=strFilePath
    DTSGlobalVariables("gblLastModDate").Value=strDateLastMod
    DTSGlobalVariables("gblSourceFileExist").Value="Y"
    DTSGlobalVariables("gblReloadFile").Value="Y"

    Main = DTSStepScriptResult_ExecuteTask
    End If
    Else 'No data in db, new file. Just declare global vars and proceed to task
    DTSGlobalVariables("gblDteLoadDate").Value=dteLoadDate
    DTSGlobalVariables("gblDteFileDate").Value=dteFileDate
    DTSGlobalVariables("gblStrFilePath").Value=strFilePath
    DTSGlobalVariables("gblLastModDate").Value=strDateLastMod
    DTSGlobalVariables("gblSourceFileExist").Value="Y"
    DTSGlobalVariables("gblReloadFile").Value="N"
    Main = DTSStepScriptResult_ExecuteTask
    End If

    Else 'File doesn't exists
    If Time() < #5:00:00 PM# Then 'If time is before 5:00pm, then dont execute because file is still not avail.
    Main=DTSStepScriptResult_DontExecuteTask
    Else 'Execute task even if file is not available so that process get documented in log
    DTSGlobalVariables("gblDteLoadDate").Value=dteLoadDate
    DTSGlobalVariables("gblDteFileDate").Value=dteFileDate
    DTSGlobalVariables("gblStrFilePath").Value=strFilePath
    DTSGlobalVariables("gblLastModDate").Value=strDateLastMod
    DTSGlobalVariables("gblSourceFileExist").Value="N"
    DTSGlobalVariables("gblReloadFile").Value="N"
    Main=DTSStepScriptResult_ExecuteTask
    End If 'End statement for time clause
    End If 'End statement for Source File Clause
    End If 'End statement for Friday Clause
    Set FSO=Nothing
    Set Conn=Nothing
    Set RecSet=Nothing
    End Function
  2. ranjitjain New Member

    Hi,
    I feel after storing value for dteLoadDate, next statement should be to check If Weekday(dteLoadDate)=2 and Day(dteLoadDate)<>1 Then
    If it's true then exit else add your code such as initialising strpath and setting FSO and other objects.
    You can set the strfilepath in one line as below
    strFilePath="\UsvhTestTest_" & right("0"&Month(dteLoadDate),2) & "_" & right("0"& Day(dteLoadDate),2) & "_" & Year(dteLoadDate) & ".txt"
    Also You are firing selet query which takes count of all rows and returns to check whether file exist, so instead of taking count of all rows which could take a while if records are many so fire this query instead "select top 1 1 as tcount from staging where lastdate=?"
    After this you are checking lastmodifieddate where you are simply taking first date without any filter, so is this correct?
  3. Jaji03 New Member

    Thank you. I modified my code to Select the TOP1. I left the date, the way it is because it may change later. And i figured it's easier to read. I really appreciate you feedback [Y]
  4. ranjitjain New Member

    Hi,
    You have not answered whether this query is correct?
    strSQL="SELECT TOP 1 LastModDate FROM [dbo].[t_STAGING] " because you are not using any order by or any where clause, simply picking up first record whichever comes first
  5. Jaji03 New Member

    Hello, it seems to work ok. I tested it a couple of times...It's probably because it's looking at a staging table and that table gets truncated every day. Thanks again for your help
  6. ashishpisces New Member

    It is giving error 'Path Not Found' at MoveFile line. The code works fine if I don't use the SQL bit. Please help me.
    <Option Explicit
    Function Main()
    Dim weekDay, vDay, vMonth, vYear, vDate, oFSO, sFileName, oFileName
    Dim wDay, wMonth, wYear, wDate, Conn,RecSet, strSQL, startDate
    Set Conn=CreateObject("ADODB.Connection")
    Set RecSet=CreateObject("ADODB.Recordset")
    Conn.Open="Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=data;user id = 'sa';password='forev'"
    strSQL = "select c1.datekey as 'stDate' from dimcalendar c1 where c1.isoweeknum = (select c2.isoweeknum-1 from dimcalendar c2 where c2.datekey = convert(datetime,convert(varchar,getdate(),103),103)) and c1.weekdaynum = 1and c1.calyear = datepart(yyyy,getdate())"
    RecSet.Open strSQL, Conn
    startDate=RecSet.Fields("stDate")

    vDay=RIGHT(RTRIM("0" & DAY(DATE()-7)),2)
    vMonth=RIGHT(RTRIM("0" & MONTH(DATE())),2)
    vYear=RIGHT(YEAR(DATE()),4)
    'vDate=weekDay & vYear & "-" & vMonth & "-" & vDay
    vDate = startDate
    wDay=RIGHT(RTRIM("0" & DAY(DATE()-1)),2)
    wMonth=RIGHT(RTRIM("0" & MONTH(DATE())),2)
    wYear=RIGHT(YEAR(DATE()),4)
    wDate=wYear & "-" & wMonth & "-" & wDay
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sFilename = "\xxx.comextractsGS3046_DATA_.txt"
    oFilename ="\xxx.comextractsGS3046_DATA_" & vDate & "_" & wDate & ".txt"
    oFSO.MoveFile sFilename,oFilename
    RecSet.Close
    Set Conn = Nothing
    Main = DTSTaskExecResult_Success
    End Function
    >
  7. ashishpisces New Member

    Managed to resolve it. Date returning from SQL query had slashes in it. Changed that and it is ok now.
  8. martins New Member

    Hi,
    I did not go through all the code in a lot of detail, but it looks very good for your first try [:D]
    The only thing I would possibly do differently is to load the file into a staging table and do all the sql stuff in a stored proc rather than then vb script. You might find that it will be quicker than doing all the data existence checks on a row-by-row basis...and it will shorten your vb script.
    Hope this helps.

Share This Page