SSIS: Creating Package Configurations

This post discusses the creation of Configuration Files and how they can be useful while migrating a package from one environment to another. Ex: From Development to Staging/QA. In this post we will be making use of one of the old packages, “Importing data from a text file using a package” which we created in one of the previous posts.

Configuration Files:
Configuration Files contains the connection strings for various sources and destinations, package variables and also the expressions. We can modify these properties without even opening up the package. At the run time, the SSIS engine looks for the Configuration File. If the file does not exists then it takes the configuration/variable information contained in the SSIS package. If the file exists, then the information in the Configuration File is made use of. If any variable/configuration information is missing in the Configuration File, that particular value of the configuration parameter/variable from the SSIS package is made use of.

Creation of Package Configurations:

The following image shows the SSIS Package for importing the text file from the location C:\SSIS\TextFiles:

Let us assume that we are migrating this package to a staging server and connecting to a different database (northwind) and placing the incoming text file in the location C:\ImportTextFiles. In our case the development server and the staging server is the same that is the local machine. In this post we are going to discuss the modification of the package properties at run time without actually opening up the package and modifying it so that this package executes successfully. In such scenarios, the concept of Configuration Files comes into the picture.

Right click on the control flow pane and click on the Configurations menu. This will open up the configuration wizard. The following image shows us how to open up the configuration editor in SSIS:

Opening up the Package Configurations:

Clicking on the Package Configurations opens up the Configuration Organizer. The following figure shows the Package Configuration Organizer.

Clicking on the Add button of the Organizer opens up the Package Configuration Wizard. The figure below shows the Package Configuration Wizard.

Clicking on Next takes us to the page where the wizard asks us to enter the type of Configuration as shown in the figure:

SSIS supports various types of Configuration Types:
1. XML Configuration File:
In this type of Package Configuration, the configuration parameters are stored in a xml file in the file system (hard disk). This is the easiest way of creating the package configurations. The properties of the package can be altered at run time by modifying this xml file. This article makes use of this configuration type.

2. Environment Variables:
The package configurations can be provided using the Environment Variables. This is beyond the scope of this article. 

More information can be read on MSDN at http://msdn.microsoft.com/en-us/library/ms141682.aspx.

You can refer to the following site for an example: http://www.mssqltips.com/tip.asp?tip=1405

3. Registry Entry:
The configuration values can also provided using a Registry values. This is also beyond the scope of this article. More information can be read at:
http://msdn.microsoft.com/en-us/library/ms141682.aspx .

4. Parent Package Variable:
In this type, the configuration values are stored in a variable of the parent package. This variable is used to update the properties of the child package at the run time. This is also beyond the scope of this article.

5. Sql Server:
In this configuration type, the configuration values are stored inside the database in a table. This is very similar to the xml configuration type but the values are stored inside the database. We can make use of DML statements to modify the configuration values. The advantage of this type is that since the database is backed up regularly, the configuration values are also backed up. But if the configuration is stored in the xml file then we have to back it up manually. Another advantage is that DML is more familiar to the database developers and DBAs since not many people know xml.
For the purpose of our demo, let us select the XML Configuration type. In the text box below, the wizard asks us to enter the location and name of the XML configuration file. Click on the browse button and specify the path and name for the xml file and click next. The next screen shot shows the various configuration properties that can be changed at the run time of the package. It includes the connection managers, variables and expressions.

Normally, we prefer to include all the connection manager connection strings and the variable values used in the package. Since we are moving to a different server, the select the connection string for both the connection managers and the variable value. The figure below shows the partial screen shot of the property selection.

Choose the necessary configuration properties and click next. In the next screen give the configuration name and click finish to exit the wizard. Then click close on the Package Configuration Organizer. The following xml is the xml file which is generated from the Package Configuration Wizard.

<?xml version=”1.0″?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy=”REDMOND\v-vipul” GeneratedFromPackageName=”TextFileImport”
GeneratedFromPackageID=”{297BD82A-0E0B-444B-A6B0-D606CD5171A1}”
GeneratedDate=”8/13/2008 9:37:56 PM”/>
</DTSConfigurationHeading>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[Applications].Properties[ConnectionString]“ValueType=”String”>
<ConfiguredValue>C:\SSIS\TextFiles\Applications.txt</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[DecipherDestination].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>
Data Source=.;Initial Catalog=AdventureWorks;
Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;
</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Variables[User::TableName].Properties[Value]” ValueType=”String”>
<ConfiguredValue>Applications</ConfiguredValue>
</Configuration>
</DTSConfiguration>

We can observe that the configuration wizard automatically populated this file with the values of the connection managers and the variables.

Now let us assume that this package is migrated to staging and the database to which the data is imported is changed to Northwind rather than AdventureWorks. Further let us assume that the incoming files are placed in the directory C:\ImportTextFiles rather than the directory C:\SSIS\TextFiles as in the original package.  In SSIS, we can modify the configuration file which holds those configuration values. Copy the text file and place it in the location C:\ImportTextFiles. Open up the configuration file and modify the connection manager’s connection string as shown below.

<?xml version=”1.0″?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy=”REDMOND\v-vipul” GeneratedFromPackageName=”TextFileImport”
GeneratedFromPackageID=”{297BD82A-0E0B-444B-A6B0-D606CD5171A1}”
GeneratedDate=”8/13/2008 9:37:56 PM”/>
</DTSConfigurationHeading>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[Applications].Properties[ConnectionString]“ValueType=”String”>
<ConfiguredValue> C:\ImportTextFiles\Applications.txt</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[DecipherDestination].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>
Data Source=.;Initial Catalog=NorthWind;
Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;
</ConfiguredValue>
</Configuration>

<Configuration ConfiguredType=”Property” Path=”\Package.Variables[User::TableName].Properties[Value]” ValueType=”String”>
<ConfiguredValue>Applications</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Save the configuration file and execute the SSIS package. We will see that the table named Applications is now created in the northwind database rather than in AdventureWorks database. In this way, the Package Configurations provides us with a neat interface with which we can alter the properties of the package at run time without manually opening up the package and modifying it.

SSIS – Non-blocking, Semi-blocking and Fully-blocking components

Synchronous vs Asynchronous

The SSIS dataflow contain three types of transformations. They can be non-blocking, semi-blocking or fully-blocking. Before I explain how you can recognize these types and what their properties are its important to know that all the dataflow components can be categorized to be either synchronous or asynchronous.

·         Synchronous components The output of an synchronous component uses the same buffer as the input. Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input. Number of records IN == Number of records OUT.

·         Asynchronous components The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records then input records.

The only thing you need to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that need a new buffer.

All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output. All destination adapters on the other hand, are synchronous.


Non-blocking, Semi-blocking and Fully-blocking

In the table below the differences between the three transformation types are summarized. As you can see it’s not that hard to identify the three types.
On the internet are a lot of large and complicated articles about this subject, but I think it’s enough to look at the core differences between the three types to understand their working and (dis)advantages:

	Non-blocking	Semi-blocking	Fully-blocking
Synchronous or asynchronous	Synchronous	Asynchronous	Asynchronous
Number of rows in == number of rows out	True	Usually False	Usually False
Must read all input before they can output	False	False	True
New buffer created?	False	True	True
New thread created?	False	Usually True	True

All SSIS transformations categorized:

Non-Blocking transformations	Semi-blocking transformations	Blocking transformations
Audit	Data Mining Query	Aggregate
Character Map	Merge	Fuzzy Grouping
Conditional Split	Merge Join	Fuzzy Lookup
Copy Column	Pivot	Row Sampling
Data Conversion	Unpivot	Sort
Derived Column	Term Lookup	Term Extraction
Lookup	Union All
Multicast
Percent Sampling
Row Count
Script Component
Export Column
Import Column
Slowly Changing Dimension
OLE DB Command

SSIS Interview Questions

1.      How to provide security for the configuration file (xml package configuration file)?

2.      Different approaches of deployment of package in ssis?

3.      Three tasks are running in your package and 2 tasks are successfully executed and third task is failed, in this situation I need to rollback 2nd and 3rd tasks, so what can u do in SSIS package and sqlserver?

4.      There are 10 records in a flat file source, among them 9 will be executed successfully and 10^th record is failed, in this scenario I need to get all 10 records source level to target level, in target level then 10^th record failure error will be displayed, what I need to do?

5.      Explain the dynamic behavior of your project?

6.      Explain the validations of a package at runtime?

7.      What are isolations in SSIS, and where u can use this?

8.      What is optimizing a packages?

9.      Tell me one complex packages in your project?(which task mostly we are used)

10.  What is linked server?

11.  I created one package with some file. I have diff servers having that package with diff configuration file. Is possible to execute that package in servers simultaneously?

15. What is incremental loading and decremental loading?

16. Microsoft office 2007 excel sheet supported by sql server 2005 or not?

17. What is the difference between file system and sql server ( at the time of deployment)?

I have one package in d(d drive) folder I want move that package in to e(e drive) folder how to move the package?

18. I have table like this

Sno	Sname
1,2	Sreenivas
3,4	Reddy
5,6	Raja
7,8	Reddy

 I want like the following table

Sno	Sname
1	Sreenivas
2	Sreenivas
3	Reddy
4	Reddy
5	Raja
6	Raja
7	Reddy
8	reddy

19.I have one package that package scheduled by daily 6 am but the job is failed at Saturday  then what I need to do?(where we go how to resolve)

21. What is parallel execution in ssis?

22. What type errors occurred commonly in your project and what are those names?

24. I have one package and that package is already scheduled is it possible to apply the transaction for that package?

25.suppose I have one folder with 5 file text files by using for each file enumerator we store the files in to one folder but suddenly tomorrow one file add to that folder how to store the file into same destination?

26. in source table data having like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10,20,30,40,50

I want like this

Eno	Ename	Esloc	Deptno
1	Sreenu	Hyd	10
1	Sreenu	Hyd	20
1	Sreenu	Hyd	30
1	Sreenu	Hyd	40
1	Sreenu	Hyd	50

 How to do this?

27. Suppose I have one destination table with some data suddenly 2 excel files data want to insert into the destination table but how to know this data is already inserted into the destination and only new data is inserted into the destination?

28. Why we are using xml file configuration file?

29. How to access and execute the packages clients?

30. In ssis package I created a data ware house by using slowly change dimension.

Cname	Cadd	Status
Sreenu	Bangalore	True
Sreenu	Hyderabad	False
Sreenu	Kadapa	False
Sreenu	Badvel	False
Sreenu	Pml	false

  From the above how to know second row?

31. What is smtp server and what is the main purpose?

32. In my source table having 1000 records from that I want move 10 to 990 rows then what I need to do?

33. I have two sources with two tables and one table is having data and another table having conditions how to use the conditions in the table?

34. I have one ssis package. How to know the how much time take for executing this package and after improving the performance how to see the time?

35. I developed one package how to know the whether the package having data or not?

36, I have one parent, child package in case the errors found child package how to handle that errors?

36.in my sql server one package is  there how to move  that package into some other server?

37. How to Concat row data through ssis?

Source:

Ename	EmpNo
stev	100
methew	100
john	101
tom	101

Target:

Ename	EmpNo
Stev methew	100
John tom	101

38. How to send Unique (Distinct) records into One target and duplicates into another tatget?

Source:

Ename	EmpNo
stev	100
Stev	100
john	101
Mathew	102

Output:

Target_1:

Ename	EmpNo
Stev	100
John	101
Mathew	102

Target_2:

Ename	EmpNo
Stev	100

38. How do u populate 1^st record to 1^st target , 2^nd record to 2^nd  target ,3^rd record to 3^rd target  and 4^th record to 1^st target through ssis?

39. We have a target source table containing 3 columns :

 Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1Col2Col3
-----------------
 a      b      c
There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

40. There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.
41.

There is a source table containing 2 columns Col1 and Col2 with data as follows:

Col1   Col2
 a          l
 b         p
 a         m
 a         n
 b         q
 x          y

Design a mapping to load a target table with following values from the above mentioned source:

Col1    Col2
  a        l,m,n
  b       p,q
  x        y

Design an ssis package to load first half records to 1 target while other half records to a separate target.