Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 8: Creating parameterized SSIS packages

Feb 28 2012 12:00AM by Sudeep Raj   

Now that we have learnt how to create a simple SSIS Package, let us see how we can make it adaptive. What I mean by adaptive is, how we can maintain the package with minimal changes in the future. Before we talk about how we handle this, let us talk about the scenarios where we might want to change the package.

  • Package needs to be moved from one environment to another
  • The password needs to be changed for SQL Server
  • You wish to send a mail with the number of records processed/error
  • You wish to send a mail to a new recipient
  • FTP Location has changed
  • Logic of a query has changed (this needs more thinking as it might also need change in the logic)
  • There can be a lot more scenarios than these.

So now the question is how do we achieve this?

Folks who have done development in any other language or TSQL should have guessed the answer. Yes, we make use of variable in SSIS.

How do we set up variables in SSIS?

When you open a SSIS Package, you may see the Variables panel to the left of the Control flow panel as in figure below:

SSIS Variables

In case you are unable to view this, you need to go to Menus -> View-> Other Windows-> Variables as shown in figure below:

Open Variables panel from menu on BIDS

Another quick way is to click anywhere on the blank area of control flow or data flow and select Variables from the shortcut menu. You will be able to see the variable panel to the left hand side like shown in figure below.

BIDS – Variables panel

In the variables panel, you will see it as a blank panel with a few buttons on top. By default, you will have 5 buttons as shown in the figure below. Each button is described below with reference to their position from the left.

BIDS – Variables panel described

  1. New Variable: Click this to create a new variable
  2. Delete Variable: This button is enabled only when you have an existing user variable. Select a variable that you have created and click this button to delete it.
  3. Show System Variable: This button is used to toggle view between the list of system variables and user defined variables. You can click this and look at the number of system variables that exists in the package before you do anything in the package.
  4. Show All Variable: The variable pane displays the variables based on which container you had last focus on the control flow or the data flow. In case you are in the data flow the variables in the data flow scope will be visible to you. Now, in case you want to view the entire variable you need to click this button, or go to the control flow and click outside the containers in blank space and then go to the variables pane. I guess clicking the above button is simpler.
  5. Choose Variable Columns: Click to open the Choose Variable Columns dialog box where you can change the column selection. Use it to select the columns whose properties will display in the Variables window.

Once you create a new variable you will see the panel like the one in Fig 5. Here you see 4 columns by default, namely:

  • Name: As the name suggest it shows the name of the variable, this is an editable field
  • Scope: This is set automatically and you cannot change the scope of a variable once created.
  • Data Type: This is an editable column with default value as Int32. It is a drop down with all possible data types in SSIS. Change this as per your need.
  • Value: This will contain the value of the variable. It may be predefined or set by expressions (will talk about this in the net section), or SSIS configuration based on the requirement.

BIDS – Variables panel with one variable

Note: Scope of the variable is set based on the container you have focus before clicking the new variable button. Though BIDS Helper can help you change the scope of the variable very easily that’s the 6th button provided by BIDS Helper on top of the variable pane.

Where to use Variables in SSIS Package?

You can use variables in SSIS in the following places:

  • Setting us any connection string
  • Setting up row count (one of the available transforms to get the rows of incoming data) transform in the data flow task
  • Used in For Each Loop, For Loop, Parameter mapping in Execute SQL Task.
  • Script task
  • There are many more places where you can make use of variables and you will come to know that as and when you require them.

How do we set Variable values dynamically?

Having created variables in our package and used them, how can we set the variables values dynamically? For example, we need to get all the files names depending on the date of the package execution.

Let us take a scenario:

The package is scheduled to execute daily with no manual intervention. If the date of package execution is odd pick file one else pick file two.

To handle this situation we would have to have a variable store the date of package execution, but can we provide the value of the variable manually? No, as the package is automated and scheduled to run, it will execute automatically daily.

Hence we would need to use Expressions here to set up the value of the variable on the fly, when the variable is used that time the value would be set. This solves our first problem getting the date on the fly, now how do we select the file names based on the date would be taken up later.

To set the value of the variable by expressions follow the below steps:

  • Select the variable you want to set up dynamically, right click and click Properties OR directly select the variable and click F4 button on your keyboard.
  • On completing the above step a properties window would open on the right side of your BIDS.
  • Here select the property EvaluateAsExpression and set the value to True
  • Next, go to the property Expression and click the small button towards the end the one highlighted in the figure below

Variable set dynamically using expression

  • On clicking this button an Expression Builder window will pop up which is shown in the figure below

BIDS Expression builder window

  • Expand the Variables section to view all the variables in the package, you could use them to create your expression. On the top right hand side you can see the various functions available for use.
  • We need to get today’s date so we go to the Data/ Time functions, select GETDATE() and drag drop it to the Expression section below.
  • Next, click on the Evaluate Expression button to validate that the expression is returning valid data. If the date returned is fine, click the OK button to close this window.
  • Now if you go back to the Variables properties -> Expressions you will notice that now it has the value GETDATE(). When the package will execute this value will be calculated and set and we could use this as per our needs in the package.

To get detailed explanation in SSIS Expression please read my blog post SSIS- Expressions.

Points to note while using Variables:

  • Check the scope of the variable
  • Do not use excessive variables, delete the unwanted or unused variables
  • Number of variables is inversely proportional to the manageability of the package :)
  • While using a template or copy pasting a package remove unwanted variables
  • Use Bids Helper for tracking variables with expressions(helps save a lot of time while debugging)

Therefore, that would be all that we talk on variables as of now. I will talk about SSIS Debugging in the next section.


Sudeep Raj
12 · 13% · 4306
9



Submit

10  Comments  

  • Hi Sudeep,

    Really nice article.After reading this post for testing purpose i have a work around this.

    Question: I have 2 Data Flow Task, one is pulling data from Table to Table and 2nd one pulling data from Flat file to Table.So here what i want is if Day is ODD number then DFT1 should execute, if Day is even number then DFT2 should execute.

    Note: i have created one variable also in properties i have changed expression----> DAY( GETDATE() )

    What i have to do next to accomplish this task.

    Thanks in advance.

    commented on Feb 5 2013 6:42AM
    Bala Krishna
    84 · 2% · 676
  • Hi Bala,

    If these are the only 2 tasks you have in your package then you can put a Script Task above them and connect both the DFTs to the Script Task. In the precedence constraint set the expression like modulo of your variable ==1 or 0 for the each task respectively. And you are done.

    commented on Feb 5 2013 8:41AM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep,

    Thanks for your reply. I don't know much knowledge about Script Task. I tryed with Execute Sql Task. i will explain you first what i did.

    1. I have placed Execute Sql Task before the 2 Data Flow Task.

    2. And then i connected to both DataFlowTasks.

    3. In ExecuteSQLTask, SQL Statements---> SELECT DAY(GETDATE()) , ResultSet-->SingleRow.

    4. Then In ResultSet Window i have mapped with variable(Here @Day)

    5. When we comes to Presidense Constraint---> Right Click--->Edit and here set Evaluate Expression=EXPRESSION

    Finally If Day is EVEN Nmber then @Day%2==0

    else @Day%2==1

    It worked for me ................. Please share with me any alternate ways(With Out Script Task)

    Sudeep Really Thanks a lot.

    commented on Feb 5 2013 10:18AM
    Bala Krishna
    84 · 2% · 676
  • Thats just fine.

    commented on Feb 6 2013 2:47AM
    Sudeep Raj
    12 · 13% · 4306
  • Thanks for your reply

    commented on Feb 6 2013 3:07AM
    Bala Krishna
    84 · 2% · 676
  • Hi Sudeer,

    Hence we would need to use Expressions here to set up the value of the variable on the fly, when the variable is used that time the value would be set. This solves our first problem getting the date on the fly , now how do we select the file names based on the date would be taken up later.

    Could you please explain how to select the file names based on the date.

    commented on Apr 4 2013 6:19AM
    N R Vinay
    646 · 0% · 54
  • Hi Nrvinay,

    Could you give me details of what you are trying to do?

    commented on Apr 4 2013 12:31PM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep,

    Firstly, I would like to thank you for these basic 11 SSIS tutorials and the instant reply.
    Basically I am new to this technology. Going forward I am looking for further tutorials where it would cover all the dataflow transformations and Control Flow Tasks.

    The following is my question: (which is part of above tutorial) We need to get all the files names depending on the date of the package execution?

    Here we are using Expressions to set up the value of variables on the fly By using GETDATE() function we are getting today’s date My question is how to implement the logic to get the file names depending on the date (Even date / odd date) of package execution. Above you mentioned that you explain it further.

    If still this question is not up to the point, please send a mail to nrvinaykumar59@gmail.com

    commented on Apr 5 2013 2:42AM
    N R Vinay
    646 · 0% · 54
  • Lets assume that we have 2 files coming in a folder. One with the name 1.txt and other with the name 2.txt.

    Now the requirement says that we if the date is odd you process file 1, else process File 2.

    Set up a variable to save the file name using the expression: "C:\FilePath\" + Date(GETDATE())%2==1? "1.txt" : "2.txt" Now use this variable in your flat file connection manager.

    commented on Apr 5 2013 5:59AM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep,

    I have created SSIS Package for SQL Sever db using OLEDB Destination and thats running fine but now i have to deploy that package on live database, so i have to provide dynamic db connection string to package through variable from my coding, but its not working. How can i achieve this ? Its urgent.. Please help ....

    commented on Mar 6 2014 5:11AM
    siddharth
    2915 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 8: Creating parameterized SSIS packages" rated 5 out of 5 by 9 readers
Getting started with SSIS - Part 8: Creating parameterized SSIS packages , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]