Solved

Problems with DTS package execution using COM

Posted on 2006-07-18
5
928 Views
Last Modified: 2012-08-13
Hello Experts,

I have a DTS package on my SQL server.  The package reads a text file and imports it into an SQL database.  During the import one of the fields is assigned a value from my GlobalVariable (ActiveX script in DTS)  The package runs fine from Enterprise Manager, but when I try run it from ASP.NET  I get the following error:



Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80020101): Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Invalid procedure call or argument: 'DTSDestination'

Error on Line 18
) (mscorlib (80004005): Missing parameter does not have a default value.
Parameter name: parameters)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

My code in ASP.NET is very simple, here it is.

 Dim dtsPack As New DTS.Package
        dtsPack.LoadFromSQLServer("int-vip-02", "warranty", "warranty", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, Nothing, Nothing, Nothing, "warranty_DCX_Claims_Import", Nothing)

        Dim fileClaims As DTS.Connection
        Dim fileVerbatims As DTS.Connection
        fileClaims = dtsPack.Connections.Item("Claims_File")
        fileVerbatims = dtsPack.Connections.Item("Verbatims_File")
        fileClaims.DataSource = "\\int-vip-02\warranty$\DCX\DCX_INTERIORS_Claims.txt"
        fileVerbatims.DataSource = "\\int-vip-02\warranty$\DCX\DCX_INTERIORS_Verbatims.txt"


        Dim varDivision As DTS.GlobalVariable
        Dim varFRCode As DTS.GlobalVariable
       varDivision = dtsPack.GlobalVariables.Item("Division")
       varFRCode = dtsPack.GlobalVariables.Item("DCX_FR_Code")
        varDivision.Value = "INTERIORS"
        varFRCode.Value = "X"

        dtsPack.Execute()

As you can tell, the DTS package reads the file names in no problem, when I comment out the GlobalVariable code everything works, but the GlobalVariables don't want to go further then the error message that I get.  The message shows up in the SQL log.

Thanks for any help to this.

0
Comment
Question by:tomasz_k
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17136606
Well my first suggestion would be to do it differently - use a stored procedure, but that may not be an option for you.

Are there any other bits of ActiveX code that use the DTSDestination function or is that the only one?

are the IIS and SQL Server on the same box? If not does it run OK from the IIS box? Possibly it doesn't have a required library installed.


It certainly is  strange error and my other suggestion would be to make sure all SP's are up to date.

0
 

Author Comment

by:tomasz_k
ID: 17137739
The ActiveX function has about 6 destination columns, and they all are using DTSDestination.  5 of the columns get data from the import file, and they work fine, ony when I try to assign a GlobalVariable dynamically to my other DTSDestination the error occurs.  If I assign a GlobalVariable within ActiveX the DTS runs without any issues.  I'm thinking it's the way I call or assign the GlobalVariable in ASP.NET, but I'm not able to figure out what's wrong with it.
0
 

Author Comment

by:tomasz_k
ID: 17138210
I have just solved my own problem.  Instead of trying to change the current GlobalVariables in DTS.  I deleted them, and assigned new variables in ASP.NET like so:

        dtsPack.GlobalVariables.AddGlobalVariable("Division", "INTERIORS")
        dtsPack.GlobalVariables.AddGlobalVariable("DCX_FR_Code", "X")

So far it's working no problem, but thanks anyway.
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 17168285
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why is "packages element is not declared"? 2 139
Entity Framework 3 48
Name space syntax error 12 44
Host asp.net pages 5 26
AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now