November 1, 2007 at 11:52 am
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
November 2, 2007 at 9:30 am
Nobody on this forum knows anything about ActiveX Script error. Any help would be appreciated.
thanks
November 3, 2007 at 7:10 pm
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?
November 4, 2007 at 8:35 am
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
November 5, 2007 at 10:22 pm
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
November 5, 2007 at 10:29 pm
June 23, 2008 at 2:57 pm
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
June 23, 2008 at 10:05 pm
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.
Maninder
www.dbanation.com
June 24, 2008 at 12:11 am
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
June 24, 2008 at 1:27 pm
Thanks for all the info!!
will look more into it
Ash
September 15, 2008 at 8:53 am
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
September 15, 2008 at 9:35 am
Please elaborate on the problems you are experiencing.
If not sensitive info, how about posting the active script
September 16, 2008 at 2:44 am
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
September 17, 2008 at 5:54 am
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
September 17, 2008 at 11:42 pm
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