SSIS - generate deployment manifest file

Creating a Deployment Manifest

  • With the Solution Explorer open Right-Click on the project name and click Properties
  • This will open the Project Properties Pages
  • Select the Deployment Utilityfrom the page
  • Change the CreateDeploymentUtility property to True

After you’ve gone through these steps the next time you build your project it will create the file (YourProjectName).SSISDeploymentManifest. This file is located in the same folder as your packages in the bin\Deployment folder.

If you run this file it will open the Package Installation Wizard that will allow you to deploy all your packages that were located in the project to a desired location.

SSIS flat file connection error: The file name property is not valid. The file name is a device or contains invalid characters

PROBLEM:

When SSIS try to access the file through connection manager from a folder that already have access.

SOLUTION:

The account use to access the folder needs to have access inherited from then highest directory, which mean you need to give access from the root directory and inherited down to the folder that contain your file.

The AcquireConnection method call to the connection manager failed with error code 0xC0202009

PROBLEM:

Today I encounter a problem when deploying my SSIS packages to production environment. The package itself is basically get data from a flat file located at an input folder, populate flat file data to a database table, do manipulation with the data within the table then generate some report and output some flat file to a destinlation folder.

Configurations like connection string, are set up in the config file. There are some other parameter are setup in the database table which the SSIS package will read at the first step then use it at runtime.

 

ERROR:

Message
Executed as user: MSOIT\app_services_ppsql. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  11:54:21 AM  Error: 2011-12-09 11:54:22.38     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.38     Code: 0xC020801C     Source: CBLoad_Bell_Package Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  COM error object information is available.  Source: "CBLoad_Bell_Package"  error code: 0xC0202009  Description: "SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  ".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0202009     Source: CBLoad_Bell_Package Connection manager "ctor-dc01sql04.ArielDC4"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'MSOIT\App_Services_PPSQL'.".  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC020801C     Source: Data Flow Task - Flat File to SQL OLE DB Destination [224]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ctor-dc01sql04.ArielDC4" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0047017     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: component "OLE DB Destination" (224) failed validation and returned error code 0xC020801C.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC004700C     Source: Data Flow Task - Flat File to SQL SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-12-09 11:54:22.46     Code: 0xC0024107     Source: Data Flow Task - Flat File to SQL      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:54:21 AM  Finished: 11:54:22 AM  Elapsed:  0.656 seconds.  The package execution failed.  The step failed.

 

CAUSE:

As per my understanding, if DelayValidation is not set to true to any connection that use config file value or any runtime value, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

 

Fix:

Changed the DelayValidation property of anything need to swap runtime value to true.

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

PROBLEM:

When I try to run a SSIS package using a config file keep getting error....

FIX:

Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.