I have been using an easy approach for adding an SSIS Package to an SSIS Project for a couple of years now. But didn't get a chance to actually blog about it. Today I decided to blog it for sure and here I go.

Often there will be more than one option for doing many things in our day to day life and quite often the approach which we discover lately turns out to be an easy & simple approach.

Adding an existing SSIS Package from file system to an SSIS Project is no different. There are basically two different approaches for doing this - The Hard Approach & an Easy Approach.

Hard Approach:

• Right click on the "SSIS Packages" folder in the SSIS project in SQL Server Business Intelligence Development Studio (BIDS) and Select "Add Existing Package" from the context menu.
• In the "Add Copy of Existing Package" dialog box select "Package location" as "File System" from the dropdown.
• Click on the ellipsis button next to the "Package path" textbox.
• Locate the SSIS package (.dtsx file) which you want to add from the file system.
• Click OK in the "Add Copy of Existing Package" dialog box to finish adding the package.

Easy Approach:

• Locate the SSIS package (.dtsx file) which you want to add from the file system.
• Right click on the SSIS package in the file system and select "Copy" from the context menu (copy the package).
• Right click on the "SSIS Package" folder in the SSIS project in SQL Server Business Intelligence Development Studio (BIDS).
• Select "Paste" from the context menu to finish adding the package.

Hope you will find this tip usful.

I have been working on SSIS for few years now and in couple of projects I have encountered a situation where in we need to load the data from Source to Staging area or from Feed Files to Staging area requiring similar structure for all the Staging ETL's except for the difference in Naming, Tables, Columns etc.

Interestingly SQL Server/SSIS provides a very great feature which can be utilized effectively to build large number of ETL's (similar ETL's) except for the differences in the tables, connections etc. This feature of SSIS is called as Package Template.

There is a detailed step by step procedure documented in the Microsoft Knowledge Base Articles. Following are the links to Microsoft Knowledge Base articles with detailed steps for creating Template Packages:

• http://msdn.microsoft.com/en-us/library/ms345191.aspx
• http://support.microsoft.com/kb/908018

Advantages of Template Packages in SSIS

• Considerable reduction in the development time
• Ensuring Coding/Naming Standards/Best Practices accross the Modules/Projects/Organization

Here are few tips to make effective use of Template Packages:

• Generation of UniqueID for evey single instance of Template Package
• Changing the necessary connections, configurations, protection levels etc
• Names in the template package should be generic. So that when we instantiate a package from template we can easily rename the new instance simply by doing a find and replace in the XML view of the dtsx packages. Example: Package Name as PKG_PRJ_TableName.dtsx, CheckPoint File Name CHK_PRJ_TableName.chk etc.

Hope you will find this post useful.

We often find ourself searching the web for options to configure/customize the Connection Strings. I have found a very interesting and useful website which lists/explains various options for configuring/customizing the Connection Strings for various Data Sources.

No need to worry about which source database you are dealing with, almost all the popular data sources have been listed on this site. You name the data source and you will find it on this site. SQL Server, Oracle, DB2, Excel, Access, SharePoint and many more.

Snapshot of sample OLE DB Connection String for AdventureWorks database:

Here is the site which I am talking about:
http://www.connectionstrings.com/

Hope most of you will find this site useful.

Many times we come across situations where in due to inconsistent coding/naming standards it becomes very difficult to debug the code or to maintain the code in a long run. Especially from a maintenance standpoint it is very essential that we follow coding standards. A little extra effort/time for incorporating coding standards results in an easy to maintain code.

Defining and following Naming/Coding Conventions/Standards, helps in maintaining consistent standards accross Projects/Organization. During the process of implementing the changes/CR's/debugging, these standards make the life of developers/testers lot more simpler :-)



Here are few tips on coding standards.

• As soon as you drop a Task/Component/Transformation on the designer Name It.
• Use Standard Naming Conventions for every object in the package (Like Package, Task, Component, Transformation, Configurations, Checkpoint & Configuration files, Variable Names etc).
• Update the Descriptions for Tasks/Components/Transformations etc. Names & Descriptions will be helpful especially while debugging a package.
• Use meaningful names/phrases for Task Precedence Constraints and Output from each of the Components/Transformations within the Data Flow Task.
• Use Annotations with a brief Description/Write up at the Package level and also inside each of the Data Flow Task. This will be helpful from a maintenance standpoint and will server as a short documentation. 

This list is updated as & when there is something relevant & worth mentioning.