I created and deployed two packages in SSIS, one that eports to an Excel 2010 spreadsheet, and one that exports to a CSV file. The packages run fine when connected to Integration Services in SSMS (directly from the deployed packages). But when placed in a job, I get errors. I have checked the "run in 32 bit . . . " checkbox.
The command line looks like this:
/SQL "\xxxxxxxx\DTS Packages\Packagexxx" /SERVER xxxxxxxx /CONNECTION DestinationConnectionFlatF
v.csv" /CONNECTION "DPU_csv.csv";"P:\XXX Accounting\DPU_SSIS\DPU_cs
v.csv" /CONNECTION SourceConnectionOLEDB;"\"D
ata Source=xxxxxxx;Initial Catalog=VCCdm;Provider=SQL
egrated Security=SSPI;Auto Translate=false;\"" /X86 /CHECKPOINTING OFF /REPORTING E
The error I get when I run the CSV package from a job is this:
Executed as user: XXXXXXX\xxxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:05:49 AM Error: 2012-09-07 09:05:51.11 Code: 0xC020200E Source: Data Flow Task 1 Destination - DPU_csvTest1_csv  Description: Cannot open the datafile "P:\XXX Accounting\DPU_SSIS\DPU_cs
v.csv". End Error Error: 2012-09-07 09:05:51.12 Code: 0xC004701A Source: Data Flow Task 1 SSIS.Pipeline Description: component "Destination - DPU_csvTest1_csv" (64) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:05:49 AM Finished: 9:05:51 AM Elapsed: 2 seconds. The package execution failed. The step failed.
I get a similar error for the Excel export, but as I have read, the hangup is more on the syntax for the Excel version. I want to resolve them one at a time, so I am starting with the CSV export.
I have found numerous articles online as to how this problem can be solved. The article I found that appears to give a solution does not have the detail I need to make it happen. The solution I found in a forum ends with his comment:
"OK, based on suggestions on another thread I moved my connection string to a package configuration file. You must also un-check the connection on the job step. After much troubleshooting trying many different ways to produce this data in a useable format it is now working."
I have never created a "package configuration file", and have no idea how to implement it.
Any help I can get on this is much appreciated.
Thanks - Hubbs