Execution Modes of SSIS package in Detail

I wrote a blog on various modes of SSIS package execution few days back. But I did not explain any one of them. So, I thought of giving a detailed explaination of how to execute an SSIS package using various execution modes. To check and verify the result of each execution mode, create a table as:


Now create a simple SSIS package using BIDS. The package will contain a variable named Mode of type string (in the scope of package)and an Execute SQL Task. Use following insert statement as SQL Statement in execute sql task:
INSERT INTO SSISExecutionMode (ExecutionMode)SELECT ?
"?" is parameter which will be mapped to variable (Mode) using Parameter Mapping tab inside the Execute SQL Task Editor. Now the stage is set to start executing the package using various modes and checking the result in table.
Following are the various modes:
Using BIDS
Open the package and set the value for the variable "Mode" as BIDS as shown in the below figure and then execute the package.


Verify the package execution result in the table by issuing a Select statement against the table as: SELECT * FROM SSISExecutionMode
The result will show 1 row in the table with Execution Mode value as BIDS
Using DtExecUI (Execute Package Utility)
Go to Run and enter DtexecUI to open the execute package utility. Select the package source as File System (if the package is deployed on SQL Server, select the SQL Server as Package Source) and click on ellipsis (…) to locate the actual package as shown below:
The path shown in above figure is the actual location of the package. Package can be executed by clicking on execute button but before that set the value for variable "Mode" as "DtexecUI". To set the value of a package variable use "Set Values" tab in the execute package utility as shown below:
Now click the Execute button and check the result in table. This time there will be a new entry in the table with Execution Mode as DtexecUI.

Next

Create a file using SSIS file system task

I have seen a lot of queries on forums to create an excel file of a particular format and then use it inside a data flow task. File System Task does come to the mind but unfortunately there is no "Create" operation inside File System Task.
But there is a workaround with little bit of stage setting.
Suppose an excel file is to be created on a daily basis and the name of the file should be the current date (20100110, 20100111 etc.)
First it is required to create a template excel file (same as the file required to be created daily). Then a file system task to copy this template file to the destination folder (folder where the new file is to be created). Then use another file system task to rename the copied file with system date.
Step1: Create a template excel file (Sample.xls) in C:\ABC folder
Step2: Create 3 variables (string type and package scoped) named :
Dest with value as :C:\XYZ (folder where new file is to be created)
Final with "Evaluate as Expression" property set as TRUE. then set the
expression as: "C:\\XYZ\\" +REPLACE(SUBSTRING( (DT_WSTR,30)GETDATE(),1,10),"-","")+ ".xls"
Rename with value as : C:\XYZ\Sample.xls
Step3: Take a File System Task and open the File System Task editor.
Select Operation as "Copy File"
Set IsDestination path variable to TRUE and select Dest from drop down box as variable.
Keep IsSourcePath variable as FALSE and create a connecion manager for C:\ABC\Sample.xls
Step4: Take one more File System Task and open the File System Task editor.
Select Operation as "Rename File"
Set IsDestination path variable to TRUE and select Final from drop down box as variable.
Keep IsSourcePath variable as TRUE and select Rename from drop down box as variable.

Select the 2nd File System Task, hit F4 (to go to its properties) and set "Delay Validation" as TRUE. The new excel file with current date as its name can be verified in folder C:\XYZ

Various ways to execute an SSIS package

When we develop a package, we execute it throuh Business Intelligence Development Studio (BIDS), for testing the package functionality. Once the package is developed and signalled green for a proper intended behavior we deploy it and then scedule it and we are done as a developer. But there are some other ways also apart from BIDS and SQL Agent Job, that can be used to execute a package.
Let's see how many different ways we can execute an SSIS package in:?
1. Using Dtexec utility from command prompt or DOS prompt
2. Using Dtexec utility from powershell or DOS prompt
3. Using DtexecUI also known as Execute Package Utility
4. Using code (C# or VB.Net)
5. Using a batch file and schedult it through Task scheduler
6. Using sp_start_job procedure to execute a sql agent job
7. Using xp_cmdshell to execute a package deployed in MSDB database or from File System
8. Using BIDS
9. Using SQL Agent job

To know how to use all of the aforesaid execution modes, please check my blog

Executing a task on failure of other tasks in SSIS

I saw a query on SSIS MSDN forum regarding sending a mail when any 1 of the 2 specific task fails at control flow. The way Send Mail Task was attached to those 2 tasks, using Preednce Cnstraint for Failure, was looking absolutely fine but the Send Mail Task was not geting executed on failure of any one of the other two tasks. In fact there was a small mistake in the configuration of precdence constraint and thats why thought of putting it here.
I will try to explain the scenario using three simpe Script tasks. This is how they are connected to each other using precedence constraint.
ScriptA and ScriptB are connected to ScriptC using Precedence constraint for Value as Failure in Precedence Constraint Editor as shown here

Now open the Script editor for ScriptA and change the default code as
Dts.TaskResult = Dts.Results.Failure Now let's execute the package. We will see that ScriptA will turn RED but the ScriptC will not execute. Try to configure ScriptA for success and ScriptB for failure by changing the default code and re-execute the package. Again we will find ScriptC not getting executed. (Why??.. we configured the precedence constraint for Failure.) Okay, lets open the precedence constraint editor and select the radio button "Logical OR". The appearance of the constraint at control flow will change and looks like
Lets execute the package again. This time ScriptC will execute if any one of ScriptA and ScriptB are configured to fail. The key was to change the default selection from Logical AND to Logical OR. Logical AND means both the constraints shoud be true (means both the Taks, ScriptA and ScriptB should fail which is not possible becasue ScriptB will execute only when ScriptA completes successfully) while Logical OR means any one of the two constraint should be true.

Lets configure the constraint between ScriptA and ScriptB to "Completion" and configure both the tasks to fail and execute the package.Also, select Logical AND for the precedence constraint between ScriptA and ScriptC. This time ScriptC should be excuted. This is how the control flow looks after execting the package

Dynamic lookup query in SSIS 2008

In SSIS 2008, lookup component has undergone a significant amount of change. Now we can parameterize the lookup query in Full Cache Mode which was not possible in SSIS 2005. Let’s see how to use dynamic lookup behavior in SSIS 2008.

Scenario:Record is a table having stats about the football players and Detail is the table having information about various players. Information is the table which is created to have consolidated information of all the players.We want to pull the data from Record and Details and push it into Information table for the players of a particular country.
This is how Detail table looks:

This is how Record table looks:

So our approach would be to take Record as Source and Detail as lookup table. Earlier we were unable to parameterize the lookup query in Full Cache mode. But in SSIS 2008, we can do that and I am going to use this enhancement while doing the lookup on Detail table.
Create 2 package scoped variables: Country and SQL (both of type string). Country is the variable used to parameterize the lookup query and SQL is the variable which is used to create the lookup query (not clear!! Well, I will explain the use of this variable when the context will come). So this is what Data Flow and the variable window look like:

Source is Record table and Lookup is using Detail table. This is how the new lookup transformation editor looks like
Click on connection and select the appropriate connection manager and write a query as:
SELECT * FROM Detail
Then click on Columns and complete the lookup editor. Then take an OLEDB destination and select the Information table as the destination and do the column mapping. So the data flow task is complete. But we have not yet parameterized our lookup query. So let’s do that (Yes, there is no parameter button in the lookup editor). Go to variable and give it a default value of Brazil. Go to SQL variable and set its “evaluate as expression” property as True. Then set an expression for this variable using Country variable as:
"Select * From Detail Where Country = '" + @[User::Country] + "'"
If you remember, I have created variable SQL to frame the lookup query. Now, we will use this SQL variable as lookup query. Select the data flow task and go to its properties. Click on the ellipsis (…) against Expressions and select the property [LookupName].[SqlCommand] as shown
Hit OK and done. Execute the package and you will see that lookup component has cached only those records for which Country is Brazil. In my Detail table there are 4 rows for Brazil so lookup has brought only 4 rows in memory instead of all the rows from Detail table. After executing the package, go to Execution Results tab to check how many rows are brought in memory by lookup component. In my package, name of lookup component is LKP_PLAYER_DETAILS and you can see that number of rows cached are 4 which is equal to number of rows for Brazil.