Error In While Execting ActiveX Scrip In SSIS

Nov 30, 2007

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

View 4 Replies


ADVERTISEMENT

Error In ActiveX Script While Execting

Nov 30, 2007

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.

View 2 Replies View Related

SSIS ActiveX Script Will NOT Error

Oct 26, 2006

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?

View 5 Replies View Related

About ActiveX Script Error In Ssis Package In SQL Server 2005

Feb 27, 2007

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

View 2 Replies View Related

ActiveX In SSIS

Nov 19, 2007

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.

View 1 Replies View Related

SSiS ActiveX Conversion

Apr 8, 2008

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

View 6 Replies View Related

ActiveX Transformations Gone From SSIS?

May 29, 2007

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 Related

SSIS - Activex Script In Transformation.

Feb 6, 2008

I 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

View 9 Replies View Related

ActiveX Script Task - SSIS

Apr 3, 2006

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 Related

Activex Script Not Working After Migrating To SSIS

Jan 3, 2008

Hi,
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

View 3 Replies View Related

ActiveX Script Transformation Column In SSIS But How???

May 15, 2006

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,

View 7 Replies View Related

Outputting Status To Job Log From SSIS ActiveX Component

Apr 29, 2008



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.

View 7 Replies View Related

[ActiveX Script Task] Error: Retrieving The File Name For A Component Failed With Error Code 0x001B6438.

Jul 28, 2006

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.







View 8 Replies View Related

Scrip Help

Jan 16, 2004

I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.

Here is the logic that I wanna have:

READ OPCSHTO
GET CUST_CODE + LOC_CODE
IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
GO TO READ OPCSHTO

ELSE
DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
DO ADD TO DPTORGANIZATIONS

View 6 Replies View Related

DTS To SSIS Conversion (ActiveX Script Has Compilation Errors)

Oct 12, 2015

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

View 0 Replies View Related

Integration Services :: DTS ActiveX Script To SSIS Conversion

Oct 13, 2015

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?

View 2 Replies View Related

How To Scrip Linked Servers

Sep 24, 2007

thanks

=============================
http://www.sqlserverstudy.com

View 2 Replies View Related

DTS Error (ActiveX)

Apr 28, 2005

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.

View 1 Replies View Related

ActiveX Error

Oct 17, 2003

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

View 5 Replies View Related

Activex Error And Dts

Oct 6, 2005

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

View 1 Replies View Related

How To Retrieve Global Variables In An ActiveX Script Task Using VBScript In SSIS

Oct 27, 2006

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

View 1 Replies View Related

ActiveX Script In A SSIS Package - Calling An FSO To Create/manipulate Files

Jul 3, 2007

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

View 1 Replies View Related

Generate Db Scrip Including Data In Tables

Oct 10, 2007

i'm not able to generate a sql script with the data in the tables! using sql express.
i need this script to ganerate the same database with its datas in a new sql server.
thanks in advance

View 2 Replies View Related

ActiveX Script Running DTS Causes Error

Apr 14, 2005

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

View 2 Replies View Related

ActiveX Script Task] Error:

Jan 27, 2006

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,

 

 

View 2 Replies View Related

How Can I Check File Size Equal Zero Or Not With Scrip Task ?

Mar 15, 2007

Dear all,

Could you tell me which code of vb scrip that i can check file size as follow ?

Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")

If FSO.FileExists("C: emplcbseg.log") Then
Set Fyl = FSO.GetFile("C: emplcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if

Thanks

Best regards,

Kusuma

View 8 Replies View Related

Exit Activex Script Without Giving An Error?

Feb 25, 2005

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"

View 2 Replies View Related

Activex Script - DTSGlobalVariables.Parent - Error

Sep 5, 2006

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,

View 3 Replies View Related

Sytax Error On Line 4 In DTS ActiveX Script (vbscript)

Oct 1, 2007

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

View 6 Replies View Related

ActiveX

Apr 23, 2001

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

View 2 Replies View Related

ActiveX

Jan 29, 2004

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

View 3 Replies View Related

Is Xml ,activeX Necessary For A DBA?

Sep 26, 2006

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.

View 8 Replies View Related

OLEDB Destination Error In SSIS Package Not Returning Error Column/desc

Aug 1, 2006

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

View 21 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved