March 19, 2012 at 12:45 pm
I am having a problem running a SSIS package as a Job. The package is on a Windows 2008 R2 server with SQL Server 2008 R2 installed. Development of the package was done on this server also.
If I
1) run the package from BIDS, it runs fine.
2) I imported the package to Integration Services File System and it runs fine from there.
3) Run the x86 version of DTEXE from the command line, it runs fine.
4) If I run it from a job, it starts the job but returns a validation error. The error is listed below.
The package it reading from a Timberline 9.7 Pervasive SQL database using a system DSN with the “Timberline Data” driver and writing the results to a Excel file on the server. The DSN is setup on the SSIS server. The DSN is 32-bit and the package and SQL runtime options are both set to run in 32-bit mode. The connection string to the Timberline Pervasive DSN is stored in a package config file (including password).
The vendor only offers a 32-bit connection, and the general Pervasive drivers will not work with Timberline (as far as I understand it). Only the application specific driver they offer works. I've contacted the vendor (Sage Software), but their scope only extends to regular office apps like Excel and Access (which the DSN does work with).
The package protection level is set to DontSaveSentative. I also tried EncyrptSensitiveWithUserKey and EncyrptSensitiveWithPassword with the same results. The SQL job is running on a proxy with my credentials.
I have tested the config file to make sure its being read. If I “break” the file, running the package in BIDS and direct in SSIS file sytem both fail. So it does appear to be reading the config correctly (from BIDS and SSIS). The config file is saved in a folder on the server that I have access to. The proxy runs under my credentials, so it should have access to.
I’ve also tried running the package without a config file and with the connection string stored in a variable with the same results.
I’ve spent an hour on the phone with our SQL consultant and he couldn’t figure it out either.
I’ve run out of ideas on this. Any suggestions? I can’t figure out why it runs fine from BIDS and SSIS, but not from the SQL Agent.
[font="Courier New"]Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 9:40:56 AM
Error: 2012-03-19 09:40:58.49
Code: 0xC0047062
Source: Data Flow Task 1 srcPervasive_PCI [1]
Description: System.Data.Odbc.OdbcException
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
End Error
Error: 2012-03-19 09:40:58.49
Code: 0xC0047017
Source: Data Flow Task 1 SSIS.Pipeline
Description: component "srcPervasive_PCI" (1) failed validation and returned error code 0x80131937.
End Error
Error: 2012-03-19 09:40:58.49
Code: 0xC004700C
Source: Data Flow Task 1 SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2012-03-19 09:40:58.49
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:40:56 AM
Finished: 9:40:58 AM
Elapsed: 1.685 seconds[/font]
March 19, 2012 at 1:34 pm
I may have figured out what the problem is. I spoke with one of our network engineers and he mentioned that the DSN for the Pervasive database relies on a mapped drive location. The agent does not load a profile when it runs, so the drive mapping is not executed first and the DSN connection fails. When I ran it from BIDS, SSIS and command line DTEXEC, it was all running under my profile so the drive mapping was established.
I'm going to work with him later this week on a possible solution. I'll report back then.
March 21, 2012 at 2:53 pm
Drive mapping was the issue. To fix it, we scheduled a job which runs the xp_cmdshell stored procedure to execute a bat file. The bat file has the drive mapping script followed by a call to the dtexec utility with switches for the SSIS package.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply