SQL Agent job failure with SSIS package to Access DB

Question

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.

Polly

Odd, is the file open or locked in any way? – Iphigeniah

@Siyual: it's not. And since I'm able to run it outside of SQL Agent, that wouldn't seem to be the problem. I suppose it's possible somebody is opening it right when I run the agent job and at no other time, but it seems highly unlikely. – Polly

Well - here's a swag: nominally, connection string arguments are semicolon delimited and don't like quotes around arguments - even args with embedded blanks. Just try taking the quotes out. Wouldn't that be a kicker? – Diameter

have you tried by changing BufferTEMPstorage path and BlobTempstoragepath. sqlserverscribbles.com/2013/05/07/… ? – Sophistry

@p2k: giving my proxy account permissions in the C:\Users\SQLSERVERAGENT\AppData\Local\Temp directory did allow it to run. However, this feels like a hack. I don't have a dataflow task, so there's nowhere to change those variables (they don't seem to exist on a script task). – Polly

@Clay: it would, but as I stated in the question, I am able to run it just fine everywhere BUT via SQL Agent. If I truly just had a connection string syntax problem, then it shouldn't be running anywhere. – Polly

@p2k: I found this: alankoo.com/2012/09/strange-error-loading-excel-files-xlsx.html. So it appears that the OleDB/ODBC driver needs a temp directory and it uses the temp directory of SQL Agent. I think my best option is to set the TEMP directory to something else before connecting. Also a hack, but less hacky. – Polly

It seems like a memory issue, may be due to huge data in your source. Is there any other task running parallelly in your package? Can't you convert your package to use data flow task? – Sophistry

@p2k: the Access database is large and there's nothing I can do about that. It is not a dataflow task because the upstream schema can and does change. The script task is able to detect these changes and update the destination database accordingly. It also does a better job of converting data values, which may be corrupt or not handled well just by sending data through a simple dataflow task. – Polly

Answer 1

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.

Polly

Answer 2

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.

Sophistry

The package is running and doing other things, it just can't open a connection to this Access database as one of its intermediate steps. I can try doing some of these things, but they seem to be mostly irrelevant to my problem. – Polly

Your package is only failing from SQL Agent Job. So, may be focusing on SQL Agent service account settings like permissions/access can help. It doesn't seem like issue with proxy account (as per your description). – Sophistry

It's running (supposedly) as my proxy account, not as the SQL Agent service account, which I never use. – Polly

I have faced similar issues due to my agent account although I my job was configured with different proxy account. – Sophistry

I tried all of these things and I get the exact same result. – Polly

How about executing the package through windows task scheduler for testing (using .bat file)? – Sophistry