PRB: Error Message "Unspecified Error" When Executing a DTS Package Modified Dynamically (238367)

SYMPTOMS

When Executing a DTS (Data Transformation Server) package modified dynamically in code, you might see the following error message:

Unspecified Error" -2147958789 from package.Execute method.

The DTS ScriptPackage tool also fails with the same error when you try to script that package.

CAUSE

The ODBC Connection string is not set properly. If you load that package in DTS Designer and then execute, you get a more detailed error message:

[Microsoft] [ODBC Driver Manager], data source name not found and no default driver specified

WORKAROUND

Set the ODBC connection string in the extended properties:

oConn.ConnectionProperties("Extended Properties").Value 
= "Driver={SQL Server};Server=(local);Uid=<User ID>;Pwd=<Strong Password>;Database=myDB"

MORE INFORMATION

For an OLEDB connection, you can set the connection properties as given below:

    oConnection.DataSource = "." 

    oConnection.UserID = <User ID> 

    oConnection.Catalog = "myDB"

However, for an ODBC connection, the properties are set in "Extended Properties" as given below:

oConnection.ConnectionProperties("Extended Properties") 
= "Driver={SQL Server};Server=(local);Uid=<User ID>;Pwd=;Database=myDB"

Note You must change User ID <User ID> and password <Strong Password> to the correct values. Make sure that User ID has the appropriate permissions to perform this operation on the database.

For additional information, please see the "Connection Object (DTS)" and "OLEDBProperties Collection (DTS)" topics in SQL Server Books Online.