Convert DTS ActiveX Script on File Naming

Question

Convert DTS ActiveX Script on File Naming

Simon Evans

SSC Enthusiast

Points: 184
More actions
April 23, 2007 at 12:50 pm

#172090

I am a bit of a novice in DTS and SSIS but I recently found an ActiveX script in another forum showing me how to dynamically name a destination file to include a date component. The script is below:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
        ' Base Filename - N:\westminsterkings_yyyymmdd.lin
        Dim sFilename, oPkg, oConn
        Dim sYear, sMonth, sDay
        If Month(Now) > 10 Then sMonth = Month(Now) Else sMonth = "0" & Month(Now)
        If Day(Now) > 10 Then sDay = Day(Now) Else sDay = "0" & Day(Now)
        sFilename = "N:\Sprinter\sam\westminsterkings_" & Year(Now) & sMonth & sDay & ".lin"
        Set oPkg = DTSGlobalVariables.Parent
        Set oConn = oPkg.Connections(2)
        oConn.DataSource = sFileName
        Set oConn = Nothing
        Set oPkg = Nothing
        Main = DTSTaskExecResult_Success
End Function
I now want to implement a similar function in a SQL 2005 SSIS implementation, but I cannot get the script to work.
I am assuming that the problem lies in the DTSGlobalVariables and/or DTSTaskExecResult_Success objects not existing in SSIS.
Can anybody help me out and tell me how I can do the above in SSIS?
Many Thanks.
SE

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

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

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 1

A really good description of how to do this can be found at:

http://msdn2.microsoft.com/en-us/library/ms141214.aspx (this one is a little wordy - but gives you a detailed explination)

http://www.sqlnerd.com/ssis_dynamic_dates.htm (much better with screenshots and is easy to follow)

Keep in mind that Active X scripts have been included in SSIS for backward compatibility only - the new Script object replaces it for SSIS packages.

Catherine

Kindest Regards,

Catherine Eibner
cybner.com.au

Paula-129590 SSC-Addicted Points: 437 More actions · Answer 2

Hello,

can anyone tell me how I can get this to work with an excel file? I have sucessfully used this ActiveX script for .txt files but it just wont work with .xls. Any help will be greatly appreciated.

Thanks

SSIS Guy SSCommitted Points: 1667 More actions · Answer 3

Have you checked DTS to SSIS Migration tool called DTS xChange. It will handle many ActiveX releted issue and adds SSIS best practices to converted package.

Check DTS to SSIS Migration Homepage http://www.pragmaticworks.com/Products/Business-Intelligence/DTSxChange