DTS Conversion Error

  • We are converting our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:

    '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    DTSGlobalVariables.Parent.Steps("Record Stats").DisableStep = False 'Record Stats

    if (DTSGlobalVariables("FILE_EXISTS").Value > 0) then

    'right click on task, go to workflow, options to see task name

    DTSGlobalVariables.Parent.Steps("SET VALID FILENAMES").ExecutionStatus = DTSStepExecStat_Waiting ''call SET_VALID_FILENAMES

    DTSGlobalVariables.Parent.Steps("Record Stats").DisableStep = True 'do not run l

    end if

    Main = DTSTaskExecResult_Success

    End Function

    Error I get Is:

    [ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x000A39BC.

    Thank You

  • Nobody on this forum knows anything about ActiveX Script error. Any help would be appreciated.

    thanks

  • Have u saved ur DTS before converting it to SSIS? I am just asking because all DTS can't be converted to SSIS or some of them give lot of problem. If its a smal DTS package with few tasks then why not create a SSIS package with same DTS functionality?

  • yes , i have saved my dts than converted to SSIS. I just want to know how to appy this script or create new script same as this in SSIS. I have no experience in VB.net or Active X

    Thanks

  • I'd think you need to remove the references to the DTS package steps

    DTSGlobalVariables.Parent.Steps as these don't get carried over in the DTS to SSIS migration

  • what if the activeX script uses something like this:

    connectionstring = DTSGlobalVariables("ConnStr").Value

    obj.open connectionstring

    The activeX does not have DTSGlobalVariables.parent - but it is still not functional. Any help would be appreciated

  • General Suggestion: DTS object model is not supported in SSIS like Dim packageind= DTSGlobalVariables.Parent and replace all NULL values with " " in the script for 2005.

  • Hey,

    I would suggest that you start by looking at what to convert from your ActiveX script into .NET

    This is going to take you some time to do, and a whole lot of googling...

    Some advice:

    -First establish what your ActiveX script is trying to do IN THE CONTEXT of your package (Is it simply trying to change connectionstrings or something more elaborate)

    - Very often, its simply to handle changes in filenames etc. In such cases, you can get away from the Activex script alltogether (For example using a for each filename task will dynamically change your filename on your connectionstring for you)

    - If not, try and conceptualise what you are going to achieve in your SSIS package, and then google it. In all likelihood, someone has written something that you can use as a baseline.

    Happy googling!

    ~PD

  • Thanks for all the info!!

    will look more into it

    Ash

  • saw u'r post ........... i am also facing same problem? did u get any solution to this. even i am also not having experience in VB, .net. I am a core java resource 🙁

    Please help, i had invested whole day in this error

    Thanks in advance

  • Please elaborate on the problems you are experiencing.

    If not sensitive info, how about posting the active script

  • Do you really need to convert your DTS to SSIS at this time?

    You can download the DTS Designer component and run DTS Designer in SQL Server 2005 and 2008. This means you can keep your DTS packages for may more years, but still take advantage of the new SQL Server releases.

    Ultimately you will need to replace DTS with SSIS but you can do it when convenient, not when you want to upgrade SQL Server.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • the script is as follows

    '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    ' (DefineTheGVs)

    Option Explicit

    Function Main()

    Dim Stuff1

    Dim fso

    Dim fold

    Dim pkg

    Dim stpContinuePkg

    Dim stpExitbadDirectory

    Dim sFilename

    Dim Ofwrite

    ' First thing we need to do is to check if our directories are valid.

    SET pkg = DTSGlobalVariables.Parent

    SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_3")

    SET stpExitBadDirectory = pkg.Steps("DTSStep_DTSActiveScriptTask_2")

    DTSGlobalVariables("gv_TotalRecordsLoaded").Value = 0

    DTSGlobalVariables("gv_FileCheckErrors").Value = ""

    'We use the FileSystemObject to do our

    'Folder manipulation

    set fso = CREATEOBJECT("Scripting.FileSystemObject")

    'Here we check to make sure the Source folder for the files exists

    if fso.FolderExists(DTSGlobalVariables("gv_FileLocation").Value) <> "True" then

    DTSGlobalVariables("gv_FileCheckErrors").Value = CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_

    " " & "Source File directory Not Found"

    end if

    'Here we check to make sure the Archive folder for the files exists

    if fso.FolderExists(DTSGlobalVariables("gv_ArchiveLocation").Value) <> "True" then

    DTSGlobalVariables("gv_FileCheckErrors").Value = CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_

    " " & "Archive File directory Not Found"

    end if

    'We predefined the GlobalVariable gv_FileCheckErrors = "" which

    'has a length of 2 so we check to see if it has expanded. If it has then we

    'know we had an error and we disable the step that would

    'allow us to continue in the package and enable the step

    'that takes us out and handles the errors we encountered

    If len(DTSGlobalVariables("gv_FileCheckErrors").Value) > 2 Then

    stpContinuePkg.DisableStep = True

    stpExitBadDirectory.DisableStep = False

    Else

    stpContinuePkg.DisableStep = False

    stpExitBadDirectory.DisableStep = True

    end if

    'Creating Log File and assigning the log file to gv_LogFileName

    sFilename = DTSGlobalVariables("gv_LogFileLocation") & "d_ccrs_obligor_stg_ld_" & Right(Year(Now()), 4)

    If Month(Now()) < 10 Then sFilename = sFilename & _

    "0" & Month(Now()) Else sFilename = sFilename & Month(Now())

    If Day(Now()) < 10 Then sFilename = sFilename & _

    "0" & Day(Now()) Else sFilename = sFilename & Day(Now())

    DTSGlobalVariables("gv_LogFileName").Value =sFilename & ".log"

    Set Ofwrite = fso.OpenTextFile(DTSGlobalVariables("gv_LogFileName"), 8, True)

    Ofwrite.WriteLine("====================================================================================================================================================================================" )

    Ofwrite.WriteLine("Load Stage Table Job Started at : " & now() )

    Ofwrite.WriteLine("====================================================================================================================================================================================" )

    Ofwrite.Close

    SET Ofwrite = NOTHING

    SET fso = NOTHING

    Main = DTSTaskExecResult_Success

    End Function

  • From what I can understand out of your active script task it does the following things:

    a) Checks to see if a folder exists

    b) Creates a new logfile on the existent folder

    c) Transfers data to the logfile

    If this is correct, its really simple in SSIS.

    a) Have a scripting task to validate that the folder exists. If exists works in scripting tasks

    b) Have a filename variable which you populate if a) has been met

    c) Add the filename variable to your connectionstring in the connectionmanager

    d) Have a dataflow task which transfers the data

    TADA

Viewing 15 posts - 1 through 15 (of 24 total)

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