DTS Execute from Com Object (Workgroup Version)

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

JayStang wrote:

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts

DTS And VBScript

I have an application written in C#, which allows the user to load their destination schema (in xml format) and writer their transformation expressions either in VBScript or in T-SQL. It uses DTS to do the ETL job.

Everything works great except with one problem. If there is a hypen ("-") in any of the destination attributes, the transformation fails with syntax error. It happens only with VBScript.

Here is my transformation:

"Function Main()

DTSDestination(\"Field-1\") = #01/01/2004#

End Function"

If I replace the hypen with underscore, it works without any problem.

Does anyone have any idea about this problem?Did you try to put the field name between brackets?
[field-1]|||Tried with \"[Field-1]\" and it did not do the magic|||Perhaps a hyphen is an invalid character for a field name. Use underscores and your problem is solved. You shouldn't be using '-'s in field names, anyway.|||In general hypen ('-'s) are not allowed in a field. But if you enclose in [], it is a valid chr (atleast in SQLServer!).

As my application is a B2B application and schema is maintained by an organization, I don't have any control. Hence I can not modify the field name.

BTW, I tried the same this with Enterprise Manager, it works.

DTS and Interbase/Firebird

I have written several DTS pakages to copy data from an Interbase/Firebird db to SQL Server. They execute under Enterrpise Manager perfectly but when scheduled return an error.

I am aware that permissions are a normal source of these problems but I have hopefully excluded them from teh equation by using a local admin/domain/sysadmin user throughout for SEM, SQLServerAgent and even the SQLServerAgentProxy.

The problem appears to be related to the interaction between the Microsoft OLE DB Provider for ODBC Drivers and the Interbase/Firebird Driver. I have checked several forums and the MSDN website but few people seem to have experienced this type of problem in this environment.

Error msgs:

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Easysoft][InterBase]unavailable database
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

OR

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:unavailable database
Step Error code: 80040E4D
Step Error Help File:
Step Error Help Context ID:0

My Setup:

MS SQL Server 2000 with SP3
MDAC 2.8 RTM
Interbase v6 /Firebird 1.03 db Server

Have tried the following driver/connectors:

Firebird ODBC v1.02.00.41
Easysoft ODBC v2.01.00.01
XTG Interbase6 ODBC v1.00.00.15

I have even tried OLE DB providers for Interbase/Firebird but they won't schedule either giving me an SQLCODE=-904

Any insight/help/sympathy appreciated. I have struggled with this single issue for nearly a week.I finally resolved the issue and wanted to post it so that someone else might benefit.

There is a difference between the way Enterprise Manager 'interfaces' with the ODBC connections setup on the machine and the way SQL Server Agent does when a scheduled job is executed. Not entirely sure what the difference is I think that the difference is that SQL Enterprise Manager assumes / defaults to a TCP/IP connection whereas the SQL Server Agent assumes / defaults to a named pipe connection.

So if you are using Interbase make sure you specify a TCP/IP connection in the ODBC setup, e.g. 127.0.0.1:C:\Interbase\db\test.gdb

DTL job error log details -- sql server 2000

Hi
In job history for a DTS job I get percent complete and not futher details
when the job fails. There is no log written anywhere.
How to debug such issues?
Tks
MangeshYou can try by clicking 'Show Step details' in top right corner of the job
history section. That will show you step wise details of the result
execution. As far as logging is concerned, you can log each step's details in
a directory of your choice. This can be done while you are creating the job
steps.
ReportFAQGuy
"Mangesh Deshpande" wrote:
> Hi
> In job history for a DTS job I get percent complete and not futher details
> when the job fails. There is no log written anywhere.
> How to debug such issues?
> Tks
> Mangesh