SSIS Package Execution

Written by:

While working on a project, I was asked to create and SSIS Package that would read a flat file (.txt) from a Network Drive onto a SharePoint Custom List. While most of the solution was straight forward, the one part that I was hoping to be the simplest, turned out to be a pain.
So, I created and SSIS Package, which would access a Network Drive and read a flat file. The SSIS Package worked great on my development environment, however, when I created a “SQL Job” and the only step in the Job was to execute the SSIS Package, I kept on getting the following error:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005.  All rights reserved.
Started:  2:23:06 PM
Error Code: 0xC001401E
Source: Package Connection manager “Flat File Connection”
Description: The file name “\\servername\folder\file.txt” specified in the connection was not valid.
End Error
Error Code: 0xC001401D
Source: Package
Description: Connection “Flat File Connection” failed validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

As I looked at the History of the SQL Job, it seemed that the Job was executing the SSIS Package by using different credentials : SERVERNAME\SYSTEM

After reading a few blog post, I encountered the following microsoft link, which listed a few options:

http://support.microsoft.com/kb/918760/

While informative, the above link did not provide instructions on resolving the issue, so here is a few steps that will help.

1. Under SQL, go to Security -> Credentials

  • Right click and select “New Credentials”
  • Provide credentials of a Domain User who has permissions to the Network Drive.

2. Under SQL, go to SQL Server Agent -> Proxies

  • Right click and select “New Proxy”
  • Under “Credentials”, select the credentials that you created on step 1.
  • Under “Active to the following subsystems”, check the checkbox that says “SQL Server Integration Services Package”

3. Under SQL, go to Jobs

  • View the properties of the Job, whose step is calling the SSIS Package.
  • Edit the Step that is calling the SSIS Package and under the “Run As” drop down, select    the Proxy created under step 2.

4. That’s it, run the Job once more and all is working as expected.

Posted on July 28th, 2008 in Technology, Tips & Tricks.