My ActiveX code wrote:
Function Main()
msgbox "Hello" & DTSTaskExecResult_Failure & DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
End Function
However - it displays "Hello10" - but SSIS will not throw an error - it executes successfully.
Any ideas?
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
I migrated the DTS from 2000, and the migrated SSIS which includes ActiveX script is 11KB, takes 00:00.125 running. I rewrote the SSIS using some new features provided in BIDS, and the new package is 50KB, takes 00:00:6.016 running. Is that normal or maybe because of the efficiency of my code?
thanks.
Can you assist me with converting the code below to VB ?
Function Main()
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:FTPOUTGOINGFTP_MarkOff.xls",,,,""
objExcel.Workbooks(1).SaveAs "C:FTPOUTGOINGFTPMarkoff.xls",,"password"
objExcel.Workbooks.Close
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
In good old fashioned DTS there was the ability to perform custom transformations using activeX / vbscripty type language - does this still exist or are we stuck with the derived column editor?
View 3 Replies View RelatedI have a flatfile source to which different flatfiles will be passed as input,this is connected to an OLEDB destination which changes along with the sourcefile.
But when the new file is given as input, the OLEDB mappings are not getting refreshed.It is showing an error.
Actually this was implemented in DTS, and they have used an activex script for the transformation.
what shd I use in SSIS?
Please help me..
Sharmada
Within a SQL 2000 DTS Package I have an ActiveX Script that would go within my transform tasks and update the queries by concatenating a "Where" clause with a date from a database table. This way I could keep track of when the last time I updated the table so that I could only bring down the rows since the last run. How can this be done within SSIS? I've been looking and I'm getting confused. Any help would be greatly appreciated.
View 2 Replies View RelatedHi,
In my script 'm using
Set oPkg = DTSGlobalVariables.Parent
Set oBeginStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")
I guess becoz of using DTSGlobalVariables.Parent in activex,the script is not working.
Can anyone please suggest a solution to run it in SSIS.
Thanks in Advance
Dear all,
I€™ve made a SSIS package which might take source columns from a plain text file and copy them to the Sql table.
A long time ago, when you did the process I did by dts and that stuff included a pump task which had ActiveX Script transform column with VbScript stuff inside so that, how do I for to do the same with SSIS??
I€™ve got a couple of tasks: Flat File Source and OleDb Source Destination but it€™s useless at all for that goal.
Does anyone have ever used or faced this hended?
Thanks for any input,
I have an ActiveX component in my SSIS package, and it is written in VB. Something is going wrong, and I'm not sure what. I works fine when run from Visual Studio, but when I move it to our server and try to run it from a job there, it fails. I'd like to know WHERE in the component it is failing, so I'd like to send output to something as it goes along, so I can see how far it is getting.
How do I send text to an output or log file?
I can't use MsgBox, because, of course, when it runs as a job, it is putting that message box up on the server, where there is no-one to respond to it, so it hangs. I'm in the process of converting it from a DTS to SSIS, and it does have several instances of MsgBox now. And it's locking up -- on one of them.
I have logging turned on for the job step, and it is writing to a dbo.sysdtslog90 table, but all it tells me is that it is starting that ActiveX script task.
What can I replace the MsgBox with, so that it outputs somewhere to a file? Is there a simple command, like WScript.Echo or Console.WriteLine (neither of which I can get to work)?
Thank you for any help you can provide.
This error occurs when the ActiveX task tries to execute:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.
Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.
I am re-writing old DTS packages(from sql 2005) to convert them to SSIS packages(sql 2014) and in one of the script task, the old activex script does not run.
The script is :
'************************
' Visual Basic ActiveX Script
'************************
Function Main()
mydate = now()
yrs = ""
mth = ""
mth = Month(mydate) - 1
yrs = Right(Year(mydate), 4)
If Month(mydate) = 1 Then mth = "12"
[code].....
Not sure how to proceed forward? I am quite new to package migration
I am re-writing old DTS packages(from sql 2005) to convert them to SSIS packages(sql 2014) and in one of the script task, the old activex script does not run.
The script is :
'************************
'Â Visual Basic ActiveX Script
'************************
Function Main()
   mydate = now()
   yrs = ""
   mth = ""
   mth = Month(mydate) - 1
[Code] ...
Not sure how to proceed forward? What is the SSIS counterpart of above script, step by step?
Hello,
I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such:
set objConn = Server.CreateObject("ADODB.Connection")
I get this as a resolution:
http://support.microsoft.com/default.aspx?scid=kb;en-us;201740
But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas?
Thanks.
I get this error msg when I run my ActiveX script in a DTS package.
Err number: 429
Err Message: ActiveX component can't create object
When I Set crApplication = CreateObject("CrystalRuntime.Application.9")
if Err.Number <>0 then
'I get the message here
ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access
I am tying to run this DTS:
the job has an activex part which uses :
"Set fso = CreateObject("Scripting.FileSystemObject")"
this row gives me the following error:
"ActiveX Scripting encountered a Run Time Error during the execution of the script. "
the dts worked when I run it local on my station but when I try running it on the server this error comes
can u help???
10x
I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:
==========================================
Function Main()
Response.Expires=-1
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False
objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing
End Function
=========================================
I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).
My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.
Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...
Michael
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
Hello All,
I have an ActiveX script that runs DTS pachkages. In the event that a package fails I want to display the error message. I found the following code (in bold) for doing this but I keep getting an error message as follows.
Type mismatch 'oSoStep.GetExecutionErrorInfo'
All help appreciated.
Thanks, Gary
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error1Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.SearsCDCC_Transfer (1).dtsx00
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
-Thanks N Regards,
Kanthi.
Hi,
I'm trying to reset the below Global varialbe in SSIS - Activex script.
Function Main()
DTSGlobalVariables("GxvFilename").value =null
Main = DTSTaskExecResult_Success
End Function
Getting the below Error when executing the package.
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x060339E4
Please provide me solution for this.
Thanks,
I am using ActiveX Script tasks to branch out on the different tasks that I have within the DTS. When I want a particular branch of tasks not to be executed, I give a "Main = DTSStepScriptResult_DontExecuteTask"
This seems to be working fine in terms of functionality, but there is an error that is displayed saying "Task reported failure on execution".
How do I get rid of this? I need to exit the task, and still just get one message box at the end of it all that happily reads "Succesfully completed tasks"
Hi,
I migrated DTS From sql server 2000 to 2005 .
But i have problemes with the ativex :
Error: 0xC0048006 at Loop action, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x000F5544.
In DTS ActiveX scripting it was common to get a reference to the package itself using the following line of code:
Dim package = DTSGlobalVariables.Parent
This was used to navigate over the DTS object model , to loop.
Someone can help me to know haw I can do the same thing with the SSIS.
Regards,
I am getting an Error on Line 4 of sample code, any idea, where?
if isdate(mid(DTSSource("Col016"), 5, 2) & "/" &
right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)) then
DTSDestination("DOB") = mid(DTSSource("Col016"), 5, 2) &
"/" & right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)
else
DTSDestination("DOB") = NULL
end if
How do I use ActiveX script in DTS to do a 'Save As' on an excel file and
convert that to a tab delimited text file ?
Help is much appreciated.
Hayes
I have to put some ActiveX code in the first step of a DTS package, which will search thru all the files in some folder and if it finds a file with filename starting with "Test" (like TestFile.txt), the script will rename it and then use it for transforming data to SQL tables.
In VB Dir$ function could have been used, what should I use in ActiveX?
Thanks a lot for your help
hello friends, i heard from my colleagues that xml and activex are necessary for DBA
is it so? how could we use in our daily activities?
can any one provide me good links for this concepts please?
thank you very much.
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
Any help is appreciated.
Thanks
Derrick
Hi all,
can anyone tell me if an oleDb connection (provider is Jet 4.0 to Access database) can be enlisted in a Distributed Transaction?
The goal is to copy data from SqlServer to Access within a transaction.
Pier
I have tired for this!
When I use SSIS for extract data from ssas, that means,I use mdx query.
then random error occured.
Hope some one can understand my poor English....
And the Error Info show below.
Code Snippet
Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".
Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.
Hi there,
Can I be able to call a Activex DLL from Sp or from triggers.
Sachi
Looking for a answer..
I'm attempting to write my first activex script. Not familiar with VB script so bear with what might seem like a simple question.
I'm trying to use an activex script in a dts to do a transformation. I have all the columns as straight copies except for one which I'm doing an activex script. What I'm trying to do is if the date is less than 1900-01-01 I want to default it to 1900-01-01. Every once in awhile a date comes across from the source file with a date of 0001-01-01 which sql doesn't like so I want to default it to 1900-01-01 instead. The script I've created parces fine but when I run the dts I get an error that the activex script encountered an invalid data value for source column LLAI. I don't understand why. any suggestions would be greatly appreciated.
Here is the script.
Function Main()
If DTSSource("LLAI") >= "1900-01-01" Then
DTSDestination("LastActivityISODate") = DTSSource("LLAI")
Else
DTSDestination("LastActivityISODate") = "1900-01-01"
End If
Main = DTSTransformStat_OK
End Function