Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Agent job failure with SSIS package to Access DB

I have an SSIS package that runs a script task (mostly, and a few other things). The script task connects to an Access database using an OleDB connection. This is the Microsoft Jet 4.0 connection. I have the drivers installed. But it won't run in SQL Agent via a proxy account. It will run fine directly from Visual Studio and from the package store. In fact, it runs fine in both of those places when I log in as the special account that the proxy is tied to. But when I run via SQL Server Agent, I get the dreaded "Unspecifed Error" OleDbException.

Relevant code from script task:

// class field
private string accessConnectionStringTemplate = "Data Source=\"{0}\";Provider=Microsoft.Jet.OLEDB.4.0;";

// in method that connects to database
Print(file, "Connection string: " + string.Format(accessConnectionStringTemplate, file.FileName));
// outputs: Data Source = "\Path\To\File";Provider=Microsoft.Jet.OLEDB.4.0"
using(access = new OleDbConnection(string.Format(accessConnectionStringTemplate, file.FileName))) {
     access.Open();
     // other code
}

The error messages via SQL Agent job history:

Started:  12:35:10 PM
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description: Exception: Unspecified error
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000000
   Source: Import Files Main
   Description:    at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.ImportFile(AccessFile file, DateTime startRecordDate, DateTime endRecordDate, List`1 accessTables, Boolean includeTransactionTables, List`1 specifiedTableList)
   at ST_cc0028a4b56242909c2eae546a807995.csproj.ScriptMain.Main()
End Error
Error: 2016-11-03 12:35:33.51
   Code: 0x00000006
   Source: Import Files 
   Description: The script returned a failure result.
End Error

Some things I've made sure of:

  • The Access drivers are installed and work on the server that SQL Agent is on. I verified this by running the package in VS as both my account and the proxy's account, with no issues.
  • The proxy account has access to the file in question. Again, verified by logging into the server as the proxy's account. The file is on a network share, but the path is specified as a UNC path.
  • The proxy account has access to other databases that are part of this operation, to rule out any other potential sources of error.
  • Running the package from the package store (via SSMS) as both my account and the proxy's account works. I did this on the database server to make sure.

In other questions I've seen on the internet about this, it's usually an issue with the drivers. In this case, I'm not sure how it could be.

I'm happy to provide additional information to help other diagnose. I myself am utterly unsure as to why this isn't working.

like image 410
siride Avatar asked Nov 03 '16 18:11

siride


People also ask

Why is my SSIS package failing?

Reasons that the package may have failed are as follows: The user account that is used to run the package under SQL Server Agent differs from the original package author. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

How do I grant access to SQL Agent jobs?

Using SQL Server Management StudioIn Object Explorer, expand a server. Expand Security, and then expand Logins. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.

How do you troubleshoot errors in a SQL Server Agent job?

To resolve the problem, follow these steps: Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account. Stop and then start the SQL Server Agent service. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.

How do I troubleshoot a failed SSIS package?

The main techniques for troubleshooting deployed packages are as follows: Catch and handle package errors by using event handlers. Capture bad data by using error outputs. Track the steps of package execution by using logging.


2 Answers

It turns out that the problem was that the Jet provider was trying to write to the SQL Agent user's temp directory, even though the task was being run with impersonation as a different user. This appears to be a feature of the Windows impersonation system, which does not change the user profile, only the user token. I ended up with this code:

var tempPath = Path.GetTempPath().Replace("\\SQLSERVERAGENT\\", "\\" + Environment.UserName + "\\");
Environment.SetEnvironmentVariable("TEMP", tempPath);
Environment.SetEnvironmentVariable("TMP", tempPath);

It's not ideal, but it works. It means that I don't have to give permissions to the SQL Agent's temp directory. Only this code has to change.

Sadly, there appears to be no way to change where the ODBC driver puts its temporary files.

EDIT: I also had this issue with a regular data flow-based package with an Excel source. In that case, I had no choice but to grant access to the SQL Agent's temp directory for my proxy user's account. If I can come up with a workaround there too, I will post it.

like image 133
siride Avatar answered Sep 18 '22 15:09

siride


I would suggest to try few things:

  1. Try to execute your package with cmd mode i.e. using the dtexce.exe syntax from SQL Agent (using both 32 bit and 64 bit option).

  2. Add the Service Account (Account SQL Agent is running through) to the DCOM component for Integration Service. If you are allowed, change the SQL Agent Service account to Proxy account (for testing).

  3. Do everything using Proxy account i.e. deploy the package using Proxy Account and make the job owner to Proxy Account (in SQL Agent). Create the job using Proxy Account.

  4. Check the Window event viewer if you have any error related to your proxy account or SQL Agent Service account.

  5. If you are using SQL Server 2012 or higher deploy the package, try it with Integration Services Catalog.

like image 37
p2k Avatar answered Sep 18 '22 15:09

p2k