SSIS

Using a Meaningful Application Name in the OLEDB Connection String

Aalam Rangi4 Comments

The default connection string for a OLEDB connection looks something like this –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoOLEDBPackage-{9758C970-4AAC-449E-8390-FA7EAC5CD52A}(local).ReportServer;
 

Notice the Application Name parameter in bold font. It has the name of the package, a unique Id for the connection, the SQL server name and the database name. This application name will be exported out to the config file too. Also, this is what you will see in the Program Name column of SP_WHO2 results when the package is running.

When the developers create the packages on a development server, the connection string includes the development server name. At the time of migrating the package and its config file to the QA and Production environment, the developers usually change the Data Source and Initial Catalog in the connection string but leave the Application Name as it is. Anyone monitoring the Production server with a SP_WHO2 would see a package running with a funny long name including GUID and the development server name. It is certainly not user-friendly.

This can be fixed easily with any one of the following methods.

Method 1 – Properties Window

Right click the OLEDB connection and select Properties (or select the connection and press F4). The Properties window will show up. Edit the Connection String property to change the Application Name parameter of the connection in this window.

Method 2 – Config File

Edit the Connection String property to change the Application Name parameter of the connection in the config file. As we know, the values in the config file override the design time values when the package is running. Although I prefer making the initial change in the package itself before creating the config file.

Method 3 – The Connection Manager Wizard

  1. Double click the OLEDB Connection.
  2. Click on the All Tab.
  3. Edit the Application Name.

SSISOLEDBConnectionStringApplicationName

Now the connection string would look like the following, which is a lot cleaner than the default string –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoPackage;
 

I prefer using a _ convention based on the target environment e.g. SSIS-DemoPackage_TEST, SSIS-DemoPackage_QA or SSIS-DemoPackage_PROD in the different config files. You can plan to put in other information to make it more meaningful in your context.

I’d love to hear your thoughts on this!

SSIS Variable Naming Convention Based on Origin of Value

Aalam Rangi2 Comments

There are many schools of thought about naming conventions, not just about SSIS variables, but about naming anything that can be given a name! One point that everyone agrees on is – use a convention that suits your environment, and stick to it.

Related Blog Post – SSIS component naming convention

One issue that I face while looking at packages with many variables is that there is no straightforward way to get a summary or a quick glance of all variables that have expressions defined for them. The BIDS Helper add-in for Visual Studio assists in this regard by adding a triangular icon overlay over the variables with an expression. SQL Server 2012 is also said to have a similar feature but I haven’t tried that yet. On the other hand, if I need to see a summary of variables that have values coming from a config file, I have to use the Configuration Editor to edit the config file and reach its last summary screen.

I’ve started using a prefix-based convention to name my SSIS variables recently based on the origin of the variable value. I distinguish between the variables as having –

  1. a value set from a config file at execution time.
  2. a value calculated in an expression at execution time. The expression could be based on other user variables, system variables, functions or literals.
  3. a static value that will not change from its design time value.
  4. a value assigned in a Script Task, a For Each Loop Container, Row Count etc.

If I know at design time that the value of the variable will be passed from a configuration file, I give it a prefix of “config”, e.g. configEmailDistributionList. If the value would be set via an expression then the variable gets a prefix “expr”, e.g. exprDirectoryForArchive. Other variables do not have any prefix, simply a name that is descriptive enough.

Advantages:

  • At the time of creating a config file, the prefix makes it clear that all variables with a “config” prefix must be included in the configuration. So the config file now has only such variables along with other task or connection manager properties.
  • On opening up a package, I can easily figure out where should I be looking for the value of the variable, the config file or the Expression Editor.
  • Alphabetical ordering in the Variables window groups similar variables together.
  • Helps in development because I don’t have to constantly check back variable properties if it has an expression or not.
  • Helps in standardizing the names and expressions for some of the most frequently used variables.
  • Helps in debugging.

Disadvantages:

The argument against this convention would be the same as given for any prefix-based convention (e.g. Hungarian Notation) that if the origin of the variable value changes then the prefix would also need to change. Which in turn, may need a change at all those places where the variable is used. As an example, if a variable was getting its value from a config file but would now have a static value, then the “config” prefix would have to go away and all the expressions, properties etc. using that variable would have to be updated. Right now I’m counting on doing a Find/Replace in the XML code of the package.

I’ll try to see if using variable Namespaces would be a better idea than prefixes and share the experience in a blog post later.

References –

(not specifically for Hungarian Notation, but in context of any prefix-based convention)

Hungarian notation (Wikipedia)
http://en.wikipedia.org/wiki/Hungarian_notation

Hungarian Notation (MSDN)
http://msdn.microsoft.com/en-us/library/aa260976(v=VS.60).aspx

Hungarian Notation – The Good, The Bad, and The Ugly
http://ootips.org/hungarian-notation.html

SSIS Package Development Effort Estimation

Aalam RangiLeave a comment

I blogged about a DTS to SSIS conversion effort estimation project that I had a chance to work upon previously. I had created an Excel workbook to help in that estimation.

That workbook was not just limited to the DTS to SSIS conversion estimates. It can be used for regular SSIS development project estimations too. I’ve updated the workbook a bit to reflect its generic nature.

The Excel workbook can help in estimating the effort required to create new SSIS packages. The new SSIS could either be a conversion of an existing DTS package or a fresh requirement. The estimation methodology would be similar in both cases.

Benefits of Using this Estimation Methodology

  • Standardize the estimates by using the same rules instead of guessing randomly, which could return widely varying figures depending on whom you talk to.
  • Standardizing also means that the estimation process for small, medium or large projects is the same. The importance of having a standardized process is directly proportional to the project size, to reduce the variance.
  • The estimates can be fine-tuned over time with real-world feedback so that the estimates get better with each new project.
  • Flexible to include the developer skill factor.

Two Steps to Fill the Estimation Workbook

  1. Put in the estimated number of minutes required for –
    a) creating each type of component in SSIS. This time could vary depending on the developer’s experience and skill. Sample values are included in the workbook.
    b) standard time additions for activities like analysis, error handling, logging and documentation. Sample values are included in the workbook.
  2. Update the workbook with the details like the type and number of all tasks, connections, transformations etc. that would be used in the SSIS package.
    a) If the basis is a DTS package then open the DTS and count the tasks, connections etc.
    b) If the basis is a fresh requirement then put the details according to those requirements. This will also help you to plan the package development.

A brief Help section is also included in the workbook.

Download

Download at: http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

DTS to SSIS Conversion Effort Estimate Workbook

Aalam Rangi6 Comments

I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.

I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.

I approached this requirement as follows –

  1. Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
  2. Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
  3. Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.

You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.

Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx  approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.