Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Windows batch file executions within SSIS fail when run from SQL Job Agent Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 16, 2014 7:52 AM
Points: 22, Visits: 119

Windows batch file executions do not fail within SSIS when SSIS run using DTExec
The account we are using to execute the jobs is a local admin on the server
The jobs ran successfully in the past, but have been failing for 2 weeks

We are running SQL Server 2008 R2 on a 64-bit server. We had a successful job execution when we ran the job using 32-bit mode while trying to solve the problem.

The specific batch job that fails maps drives to allow access to a password protected folder so we may resolve the “file connection string” in the connection manager.

Anybody have any thought on this?

Post #1367018
Posted Wednesday, October 31, 2012 4:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:41 AM
Points: 53, Visits: 118
Open the SQL Agent job and the step where you call the SSIS Package.

On the "Execution Options" tab...check the box "Use 32 bit Runtime" and see what happens. When using Excel or some ODBC drivers you need this setting to emulate the 32-bit environment or the job will fail.

-Eric
Post #1379561
Posted Thursday, November 1, 2012 7:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 16, 2014 7:52 AM
Points: 22, Visits: 119
Thanks of the suggestion. We aleady tried running the job in 32-bit mode via the job agent.

The big picture is that we have to connect to password protected Samaba folders to retrieve files. We need to map drives(shares) with the password to resolve the connection string in the flat file connection manager. Ultimately the issue was that the batch job was mapping letter drives and as the batch file was getting reused from different developers, either the letter drives eventually ran out, or the letter drive was already mapped and the batch job failed.

The solution was to rewrite the batch file more intelligently.
Post #1380102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse