SSIS Indirect Configurations

Configurations in SSIS are mainly used to make the package independent of the environment  dependent objects such as connection strings ,location of files etc.. When we  deploy our package to different server with different configurations we can  easily apply the environment dependent objects through configurations  .

I will implement direct  configurations initially than i will  extend to indirect configurations.

I'm using the same package of my previous demo,a simple package of for each loop for a collection of excel files,which loads the files dynamically to a database table.So the connection string for excel source is implemented dynamically by using variables with expression as i explained in my previous post,and the destination is connection string pointing to a table in my local database  SSIS Demo 

I will create a configuration file for these two


In order to create configuration you can  either go to SSIS menu or right click on your design surface and add configurations

I have selected configuration type as xml  configuration file ,and given a location for it.remember  configurations can be implemented using many ways with xml files or sqlserver or environment variables in this demo i will be using combination  of xml files and environment variables.In the next step we will   selecting the environment dependent variables 

This example simply selects connection string for our destination  database and variable value. By Capturing these we can make changes in our destination server configuration with relevant values which are local to that environment .In next step give  a name and finish.


Save the package  and manually copy the package to the other server.
Bring DTEXECUI   by searching  for programme file  for  DTEXECUI

Double click DTExecUI 

Give the connection details of the package location and  than add configuration file as shown above.The Configuration  file should be edited as per your environment.


This is a direct configuration,as we need to provide the hard coded location of the configuration file which is not a flexible approach.In order to avoid this we will create an environment variable pointing to the location of configuration file

 Environment variables  can be set by right clicking the  computer  and  advance system  settings  and environment  variables  .

 and finally  configure the environment variable. and give this environment variable name while executing the package on the destination server with the environment variable set.

Dynamically Loading excel files using SSIS

In this post i will  design a package for looping through excel  files   on  a  64 bit computer  as it requires few extra steps otherwise the package will fail.

The Package Looks like this

For Each Loop  will be looping through all sales files  and  will be mapped to Salesfilename  Variable

All the files are located under  C:\

Open the Foreachloop container  and  set the Properties for Folder to enumerate and the File Qualifier as shown below

Variable Mapping

Next step is to configure connection string for the excel source

As we will provide the file name at run time we need to set few properties Add a connection string to the design surface and right click the connection string and go to properties  ,expressions  and set connection string as

The easiest way to set this expression is to connect to excel file initially and than  copy the connection string and make necessary changes to include the variable name  and reset the Expression with connection string with the expression

Open the DataFlow drag a Excel Data Source and Oledb Destination with a mapped table

Open the properties of the Excel Source and set the Validate external meta data to  False other wise it will give validation error ,as the connection string gets its value at run time

Also  go to the For each Loop Container properties and set  delay validation to true and run the package on your 64 bit System

The Package Fails and throws an error

[Excel Source [1]] Error:

SSISErrorCodeDTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

The AcquireConnection method call to the connection manager "Excel Connection Manager 2"

failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The solution is to go to Project Menu and Select ProjectProperties and  go to  Debugging and set Run64BitRuntime  to False.

And re run the package the package will execute without any errors