SSIS - New Features Of SQL Server Integration Services (SSIS) 2012

Here are some improvements in SQL Server Integration Services 2012 from SQL Server Integration Services 2008.

1-Undo Change in SSIS Package: 

Undo was not available in SSIS Package. Once you have deleted any Task or Transformation, there was no way to undo the change. SSIS 2012 has added Undo facility. You can use Ctrl+Z or Click on the Button as shown in below picture
Fig 1: Undo in SSIS 2012

2-Group Tasks and Transformations:

The Tasks and Transformations can be grouped together. You will not be using this Group to connect to other Containers or Tasks. This grouping is just for arranging the Tasks or Transformations but you will not be able to set the properties like Containers.
Fig 2: Grouping Tasks and Transformation in SSIS 2012


3-Change the Scope of Variable/s:

In earlier versions of SSIS, to change the scope of variable from one Task to another Task or from Task level to Package level, we had to delete the variable and then create with new scope. In SSIS 2012, we can change the scope of variable without deleting and recreating it as shown below.
Fig 3: Change Variable Scope in SSIS 2012

4-Decimal Data Type Variable:

New Data Type is added for Variables as shown below. More Details Link
Fig 4: Decimal Data Type for Variable

5-Expression Character Limit

We often have to write expression in Execute SQL Task for source query. The character limit for SQL Server 2008R2 and old version are 4000. This limit is removed in SQL Server 2012 Integration Services.

6-SSIS ToolBox and Variable Button

SSIS ToolBox and Variables buttons are  added to Package Design Surface as shown below.
Fig 6: SSIS 2012 ToolBox and Variables button

7-Zoom In and Zoom Out 

For Zoom in and Zoom out a scale is added at the right bottom corner of Package design surface. Also the Button for navigation button is removed that was available in right bottom corner of Package design surface in SSIS 2008R2 and old versions.
Fig 7:Zoom in/out Scale on Package Design Surface

Fig 7.1 SSIS 2008R2 and earlier versions Package Navigation Button

8-Parameters

With new Project Deployment Model, Parameters are added to provide configuration values. The Parameter can be on Project level or Package level. We can change the value of these parameters without making any change in Package or Project itself while Package is going to execute in different environments such as DEV, QA, UAT and Prod. Parameters can be accessed by Right Clicking on Tasks, Containers etc. You will also see the Parameters while writing expressions and you can use them if required.
When you create a new Parameter, you can Add to Configuration as well from SQL Server Data Tools.
Fig 8: Parameters in SSIS 2012

9-New Added Functions for Expressions

Below are the newly added functions which can be used while we write expressions.


  • LEFT 
  • TOKEN
  • TOKENCOUNT
  • REPLACENULL

10- Row Count New GUI

Row Count GUI is simplified, The purpose of Row Count was to save the Count in user variable but Row Count Transformation in SSIS 2008R2 and earlier versions were also displaying all system variables. In SSIS 2012, it only displays the Variables which are Integer Type and does not provide the list of all variables that helps to configure this Transformation quickly.

Fig 10: Row Count GUI in SSIS 2008R2 

Fig 10.1 Row Count GUI in SSIS 2012

11- Data Viewer

Extra features of Data Viewer are removed such as see the data as Histogram,Scatter Plot and Column Chart. I have been always using Grid and never used other types and specially for debugging other charts really does not help.
Fig 11: SSIS 2008R2 Data Viewer

In SSIS 2012 , Only Grid is available and other types are removed.
Fig 11.1 : SSIS 2012 Data Viewer 


12-Pivot Transformation GUI Improvements

New Graphical User Interface is provided in SSIS 2012 for Pivot Transformation to configure it quickly and easily. Click Link to see example of SSIS 2012 Pivot Transformation and how to use it.

Fig 12: New GUI for Pivot Transformation in SSIS 2012

13-ODBC Source and ODBC Destination

ODBC Source and ODBC Destination is added in SQL Server Integration Services 2012.
Fig 13: ODBC Source and ODBC Destination in SSIS 2012

14-CDC Control Task

The CDC Control Task is added to SSIS 2012 to perform initial load for CDC enable table.
Fig 14: CDC Control Task

15-CDC Source

CDC Source is added to read the change data from CDC tables.
Fig 15: CDC Source in SSIS 2012

16- CDC Splitter

CDC Splitter Transformation is added in SSIS 2012 to split the data into multiple flows for Insert, Update and Delete Operation.
Fig 16: CDC Splitter SSIS 2012


To be continued.....




SSIS - How to Debug Script Task Step by Step In SSIS

We often have to debug Script task which we have used in our SSIS Package. We use MessageBox.show to see the value of variables and then have to hit Ok to see the next value. Instead of introducing Message Boxes for debugging we can use Break Point and debug step by step so we do not have to worry about removing the Message Boxes or commenting them after debug.  In case we forget any of them that can become the reason of package failure when we will be running the package from SQL Server Agent.

Let’s jump into an example, how to debug Script Task step by step by using Break Point.
In this sample Package, I am reading the file names from a folder. I have created a Variable FolderPath in SSIS Package and passed this variable to Script Task. Script Task uses this folder path and read the file names.
Fig 1: FolderPath Variable holding the Folder path 


First, I want to make sure the Script task is reading the correct value of FolderPath variable and then want to know what file names are read from Folder?

Drag Script Task to Control Flow Surface, Open it by double clicking and then provide the FolderPath variable to it and Click on Edit Script. I am using C# for scripting, you can use VB.Net if you like.
Fig 2: Provide the Variable FolderPath to Script Task

Add using System.IO under namespaces and below code in Main. The code is going to read the .txt files from the Folder which is provided by FolderPath variable.

DirectoryInfo dir = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());
            FileInfo[] Files = dir.GetFiles("*.txt"); 
            foreach (FileInfo file in Files)
            {
                string variable =  file.Name;
            }

Fig 3: Break Point in Script Task
Hit the Save button and close the Script Task Edit window. You will see that the Script Task has Red Circle that means Break Point is added to the Script Task.
Fig 4: Script Task After Break Point added

Let's execute our SSIS Package and see how we can debug the Script Task and see the values of variables etc. As we can see the Script Task is stopped at Break Point. 
Hit F10 or F11 on your Keyboard to proceed

Fig 5: Execution stopped at Break Point

Once you hit F10 or F11 , the next step will be highlighted and executed as shown below.
Fig 6: Debug step by step by using F10 or F11

As you can see in the fig 6, we can click on the line and it will show us the value. you can unpin or pin to see or hide the values while debugging.

Once the Script Task execution is completed, the package will return to run next task in Control Flow Surface. If all looks fine to you. Open the Script Task and Click on the Break Point to remove it.

SSIS - How to Copy Variables From a SSIS Package to Another SSIS Package

Scenario: 

As we know that we can copy Connection Managers, Tasks and Transformations between SSIS Packages but how would we copy the variables from a SSIS Package to another SSIS Package. This can save us time while development in cases where we need to create variables with same name and data types.

Solution:

We can not click on Variable/s to copy and then go to second SSIS Package and paste it. I have created two SSIS Packages Package1 and Package2. The Package1 has two variables as shown Variable1 and Variable2 as shown below

Fig 1: Variables in SSIS Package1

To copy variables we have to go to the SSIS Package code and then copy the required variable code and paste in Package2 xml file. Before you copy and paste the variable from the code, make sure you have saved the copy of your SSIS Packages in case you mess up with SSIS Package code;)

Right Click on Package1 and Click on View Code and then copy the part as shown below
Fig 2: Package1 View Code 

Copy the code as shown below
Fig 3: Copy Variables code from Package1


Go to Package2. Right click and then Click on View Code and then paste the code we have copied in above step as shown below
Fig 4: Paste Variable's code in Package2

Save the file by using Ctrl+S or by using Save button. Once the file is saved, we will see that two variables are created in Package2 as shown below
Fig 5: Variables copied to Package2


DBA - Drop Backup Tables From All The Databases On A SQL Server

Scenario:

Sometime when we deploy code to QA/UAT/Production, depending upon changes we take the backup. Sometime we take the backup of entire database and if they changes are small and only happening to one table, we take the backup of that table instead of taking the backup of entire database before deployment.

Solution:

Below code can be used to find out all the tables which has the numbers in their names (mostly we put date when backup the table or table has Bkp or Backup in the name). If you are backing up your tables with different name patterns, please change the select query according to that.

The code will run on all the databases on a server those are online. It will return DBName,TableName with Schema and Drop statement. Please review the list provided before you execute on any environment to make sure you are dropping expected backup tables.

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
  DROP TABLE ##Temp
CREATE TABLE ##Temp
  (
     DBName    VARCHAR(1000),
     TableName VARCHAR(1000)
  )
DECLARE @DBName NVARCHAR(MAX)
--Declare Cursor
DECLARE CUR CURSOR FOR
  --Get the list of online databases and exclude System databases
  SELECT name
  FROM   sys.databases
  WHERE  database_id > 4
         AND state = 0
         AND is_read_only = 0
--OPEN CURSOR
OPEN CUR
--Fetch First Row
FETCH NEXT FROM CUR INTO @DBName
--Loop
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX)

      SET @SQL='Insert into ##Temp( DBName,TableName) 
      Select ''' + @DBName
               + ''',''[''+schema_name(schema_id)+''].[''+name+'']'' from ['
               + @DBName + '].sys.tables
where name like ''%[0-9]%'' OR name like ''%Bkp%''
OR name like ''%Backup%'''

      -- Print SQL for Debugging
      -- PRINT @SQL
      --Execute SQL to insert records in ##Temp table
      EXEC (@SQL)

      --Fetch next DataBase name 
      FETCH NEXT FROM CUR INTO @DBName
  END
--Close and Deallocate CursorCLOSE CUR
DEALLOCATE CUR
--Get DBName, table Names and Drop Statement
SELECT DBName,
       TableName,
       'Dropt able [' + DBName + '].' + TableName AS DropStatement
FROM   ##Temp 

SSIS - How To Find and Replace Text In SSIS Package/s?

Scenario:

A developer from our team stopped by and he asked me that he has used a Stored Procedure multiple times in a SSIS Package and want to replace with new name. I asked him that why do you want to change the name of Stored Procedure. His answer was that he did not follow the naming convention or company’s standards for TSQL Object name and now he realized that he need to change the Stored Procedure name in Database as well in SSIS Package.

Solution:

Once solution can be go to the SSIS Package and open each of the Execute SQL Task or any other tasks where you have used the Stored Procedure. This can be lengthy and tiring process.  How about finding the Stored Procedure and just replace it?

Open the SSIS Package in BIDS, Right Click on SSIS Package and Click on View Code
Fig 1: SSIS View Code

You will see the SSIS Package opened as XML file. Press Ctrl+F or Ctrl+H to find and replace. Put the text that you want to find and provide text with which you want to replace as shown below


You will have the option to Replace the founded text in entire solution that may contain more than one SSIS Package. In my case I only want to replace in Current Document.

Once done, close the file and save the SSIS Package or entire solution with all SSIS Packages.


SSIS - How to Find An Object ( Table,Function or Stored Procedure etc.) If It Is Used in SSIS Package/s?

Scenario:

On daily basis we come to the point where we want to know if the table/view is used in a stored procedure or function. Same way while debugging SSIS Package we need to know if the SSIS Package is using table or Stored procedure so we can further look into the issue.

Solution:

The very first thing came in my mind, open an SSIS Package, Press Ctrl+F or Ctrl+H and see if something show up like the way we find and replace in SSMS or MS Office products.
Unfortunately, that does not work in SSIS Package. So how do I find if the Stored Procedure is used in my package?

I have created Two SSIS Packages and in each of them I have used the Execute SQL Task and each Execute SQL Task is executing dbo.Test Stored Procedure as shown in Fig

Fig 1: Execute SQL Task executing Stored Procedure ( dbo.Test)

The SSIS Package files are saved as xml file. Open all the SSIS Packages in which do you want the stored procedure in BIDS or SSDT. In my case I have already opened two SSIS Packages.

Follow the steps in Fig 2
Fig 2: Find Text in SSIS Packages

Fig 3: Find and Replace in SSIS Package

Put the object name or text that you want to find in SSIS Package.

Check the results in Find Results Pane as shown 
Fig 4: SSIS Package Find Results Pane.

As we can see that the both SSIS Packages are shown in Find Results with Task where dbo.Test is found.

To initiate Find Wizard, in Solution, we need to use Ctrl+Shift+F.