How to download a facebook video?

Friday, 26 June 2015

How to create a new SSIS Package using SQL Server Business Intelligence?

Overview
In this section we will talk about creating a new SSIS package from the begining. We will also look into some important SSIS package properties.

Explanation

Step 1 - Launch SQL Server Business Intelligence Studio from the start program.


Step 2 - Go to File --> New and click on Project...

Step 3 - Select Integration Services Project from the New Project wizard. Provide a proper name ie. MyFirstSSISSolution and click on OK button.

Step 4 - It will create a new SSIS solution with a default package called Package.dtsx.

Now your SSIS Package is ready for the design. From the tool box, You can drag and drop the required component  into the control flow tab as well as in to the data flow tab.

SSIS package contains few tabs like 
a. Control flow - It defines the work flow between tasks.

b. Data flow - It defines the flow of data from source to destination.

c. Event Handlers - It helps to implement the custom logging if required.

d. Package Explorer - It helps to check on hierarchical view of configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables.

Adding a new SSIS Package into the existing solution
You can create a new SSIS package into the existing solution. To do that -
Right click on the SSIS Package folder and select New SSIS Package. It will create a new SSIS Package.

Renaming the existing SSIS Package
You can rename an existing SSIS package by right click on it and select Rename 

Important Points 
Other than the above explanation, SQL Server Integration Services (SSIS) has other wonderful features -

Transaction Option - It provides the Transaction features into the SSIS Package like as database transactions. The default value for the TransactionOption is Supported. Here the SSIS package will execute within the existing transaction(i.e. called from the other package) and will join the existing default transaction.
  Two other modes are available as Required and NotSupported.  TransactionOption Required means that the package will always execute within the transaction, either joining any existing transaction or creating a new one.  TransactionOption NotSupported means the package will never bother with the transaction feature. 

Isolation Level - IsolationLevel property in SSIS works like the Transaction Isolation Level in database. Default value is Serializable which may not be required always. In Serializable Isolation level, read locks are held until a transaction will not be committed or rolls back and also No data can be inserted due to additional lock. Purpose of the Serializble Isolation level is to decreased the data concurrency but also keep in mind that it may not be required always.

Protection LevelThe protection level in SSIS provides various options to apply encryption on sensitive information like database connection string. Default value is EncryptSensitiveWithUserKey which may not requrie always. Data base connection string with the default protection level will encrypt with the user key of person who created the package. When other user executes the same package it will fail due to decryption of the connection string. To avoid this consequence , use the protection level as DontSaveSensitive which means you are not keeping any sensitive information in your package. The main benefit to do this change is that the other user can also execute the package.

I hope the above article will help you to create a new SSIS package with some additional information.

Provide your suggestions through the comment box.

Saturday, 13 June 2015

Implementing Registry entry configuration in SQL Server Integration Services

Scenario - Registry entry configuration is used very rarely. In my experience I never got the situation in which it is compulsory to use Registry entry configuration. I also did not see any particular advantage to have the Registry entry configuration in SQL Server Integration Services. According to few people Registry entry configuration may useful to create the system dependency in which it can store any code value into Value data in Registry key and later be validated for running the SSIS Package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Registry entry configuration. To verify this configuration let's create a key in the windows registry and fetch it from the SSIS package using a script task.

Step 1 - Press    + R and type Regedit then press Enter, It will open the Windows Registry 


Editor.




Step 2 - Expand the HKEY_CURRENT_USER then right click Go to New then choose Key in order to create a new key. We could also use any existing key but for clear understanding let's create a new one.

Step 3 - Let's name of the key SSIS_Reg. Right click on it and choose New then choose String Value. Provide name Value as name for the String Value

Step 4  Right click on Value and select Modify... then we will provide some value into it. 

Step 5 - Let's provide the value as 'This is registry entry configuration in SSIS' in the Value data then click on OK button. 

Step 6 - Registry entry value has been created till the Step 6. Now from then Step 7 on wards we will fetch this Registry entry value from a SSIS package. Now  open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 7 - Open the default package and create a Variable of string type. let's say Var_Regedit.

Step 8 - Now we need to integrate this variable with the Windows Registry key (SSIS_Reg) which is created at Step 3

Step 9 - Open the package and select Package configurations by right clicking on the package.
Check on Enable package configuration and click on Add button -

Step 10 - Select Registry entry from the Configuration type dropdown list and specify the Window Registry Key name "SSIS_Reg" which we have already declared at the Step -3 then click on Next button.

Step 11 - Now it's time to integrate the Registry entry key with the package variable Var_Regedit which is created at Step 7. To do that select it's Value  and click on Next button.

Provide the Configuration name and click on Finish button,

Step 12 - Let's drag and drop a Script task from the toolbox. Provide Var_Regedit as ReadOnlyVariables then click on Edit Script button.

Step 13 - Provide the below lines of code into script editor -
public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
            MessageBox.Show(Dts.Variables["User::Var_Regedit"].Value.ToString());
        }


Close the script task editor and click on OK button into the script task.

Step 14 -  Now we are done with all the changes. Let's run the package, It should populate the message box containing the value of Windows Registry Entry which we have created at Step 5.

As per all the above configuration, Message box should contain the value as per below screen shot.

So in this way we have completed successfully the Registry entry configuration in SQL Server Integration Services.

Important points -

1. Registry entry configuration value can be stored either in an existing registry key or in the new registry key created.

2. This configuration type in SSIS used very rarely.

3. It should not use to store the database name or any user credentials if there is any security concern.

4. At the time of deployment same registry entry key must be available with the appropriate value data.

5. Only a single value can be stored in one registry key.

Please provide your comments if the above post is useful for you,











Thursday, 11 June 2015

Implementing Parent package configuration in SQL Server Integration Services

Scenario - Parent package configuration is very useful when it is required to pass the value from the parent package to it's child package. The main advantage to using this configuration is to avoid the calculation of the variable again and again in every package, we can get the value of such variable from the parent package. For example the main database connection string, Any global variable or Database name and so on.

Usage of SQL Server configuration
Parent package configuration is more useful whenever it is required to access any variable from the child package.

Implementing the Parent package configuration -

Let's create a simple and short implementation for Parent package configuration. To verify this configuration let's pass the value of a variable from the Parent to it's Child package.

Step 1 - Open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 2 - Let's create two packages,  parent package as well as child package -

Step 3 - Open the Parent package and create a variable called Var_Global_DB and assign the value 'Global Database Name'. Later we will fetch this variable from the child package -


Step 4 - Drag an Execute Package Task from the tool box and double click on it. A wizard for Execute Package Task Editor will appear.

Step 5 - Select the Package tab from the left side bar and choose File system as Location in the right side bar.

Step 6 - Create a new connection for File system location by selecting the New connection from the connection drop down list.

Step 7 - Provide the path of the child package with the help of browse button and Click twice on OK  button -

Now the parent package can call the Child package through the Execute Package Task

Step 8 - In the child package create a variable called Var_Child. It will hold the value of the parent package variable.

Step 9 - Open the Child package and select Package configuration by right clicking on the package.
Check on Enable package configuration and click on Add button -

Step 10 - Select Parent package variable from the Configuration type dropdown list and specify the variable name which we have already declared at the Step -3 then click on Next button.


Step 11 - Expand the variable properties from Var_Child and select it's Value then click on Next button.

Step 12 -  Provide the Configuration name and click on Finish button.

Also close the main Package configuration wizard. Parent package configuration has been completed here.

Step 13 - To Verify the Parent package configuration, let's check the value of the variable Var_Child in the child package which would be holding the value from the parent package variable using Parent package configuration.

Step 14 - Drag and drop a script task from the toolbox into child package and double click on it. Pass Var_Child as  ReadOnlyVariables then click on Edit Script button.

Step 15 - Write the below line of code into the script task -
public void Main()
        {
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;            MessageBox.Show(Dts.Variables["User::Var_Child"].Value.ToString());
        }

Close the script task editor and click on OK button into the script task.

Step 16 -  Now we are done with all the changes. Let's run the parent package, It should populate the message box from the child package with the variables's value which has initialized in the parent package -

We are done here with the parent package configuration. I believe this article will help you to understand on Parent package configuration is SQL Server Integration Services.

Please provide your comments if this post is useful for you,



Tuesday, 9 June 2015

Implementing SQL server package configuration in SQL Server Integration Services

Scenario - There are several ways to implement configuration in SQL Server Integration Services. As per my understanding with SSIS packages each type of configuration has a special quality and it is useful in a specific scenario. Like SQL Server configuration, it is very useful when we need to store multiple parameters using the configuration. In this scenario SQL Server configuration will be more useful than XML or other configurations, because SQL server table configuration provides you the database table to hold the parameter values, you can easily update them using SQL update query, while in the other hand if you use XML configuration for the same, it may contain a bunch of XML code which may be bit difficult read and understand.

Usage of SQL Server configuration
SQL Server configuration is more useful whenever it is required to store the multiple parameters in the SSIS configuration.

Implementing the SQL Server configuration -

Let's create a simple and short implementation of SQL Server package configuration.It will store the variable value in a SQL server table and later we will check it at the package level.

Step 1 - Open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 2 - Let's create a Oledb connection using local SQL server database.
Click on OK button, Oledb connection will be created as per the below screen shot -
Step 3 - Create a variable at package level and let's name it ConfigVar.

Step 4 - Right click and select Package Configuration -

Step 5 - Select the checkbox "Enable package configuration" and click on Add button in the below wizard-

Step 6 - Select SQL Server from the Configuration type and pass the oledb connection which we have created at Step 2.

Step 7 - If SSIS Configuration table is not exist in the database then it is required to create a new SSIS Configuration table, you can create SSIS Configuration table by click on the New button. Also you can rename it. Once click on OK button SSIS Configuration table will be created and also selected by default in the above wizard.

Step 8 - Once you are done with the SSIS Configuration table, provide value to Configuration filter to group a set of entries. Let's name it TestFilter and click on Next button. 
Select the value of the variable ConfigVar which we have created at Step 3 and click on Next button.

Step 9 - Provide name of the configuration and click on Finish button and also close the configuration wizard.
Here SQL Server configuration has been completed for the variable ConfigVar.

Step 10 - . To verify the same just check the dbo.[SSIS Configurations] table in the database -

ConfiguredValue field will store the value of the variable, As of now it is empty and can be udpated using SQL update statement.

Step 11- Let's edit the ConfiguredValue and fetch it from the SSIS package in order to test the SQL Server configuration.

Step 12 - We will fetch the value of the variable ConfigVar though ScriptTask, and it should be provided the same value which has been updated in the SSIS Configuration table.

Testing of SQL Server configuration
Let's take a Script task and pass ConfigVar variable as ReadOnlyVariables then we will populate the value of this variable using MessageBox.

Step 13 - Click on Edit Script... button and write the following code, It will populate a message box with the updated value of the variable.
Dts.TaskResult = (int)ScriptResults.Success;
MessageBox.Show(Dts.Variables["User::ConfigVar"].Value.ToString());

and click on OK button in the Script task.

Step 14 - Now run the same SSIS package, it should populate a message box with the same variable value which is edited at Step 11.


As per the above screen shot we are getting the same value which was stored previously at Step 11. SQL Server configuration has been completed successfully.

I hope this article will help you to understand on how to implement SQL Server configuration in a
SSIS package.

Please feel free to share your comments/suggestions.





Friday, 5 June 2015

How to remove extra white space from the right side of the report page in SQL Server Reporting Services ?

In one of my SSRS support application, It was required to remove one column from the tabular report. Usually it seems very easy task to remove the column by right clicking on it and select delete, but actually it was not as simple as i thought. Here an unexpected while space will also be appear in the report area because of the deleted column. This is small but very common observation found in the SSRS reports, In order to resolve this  follow the solution below -

Solution - Change the width of the report body. Follow the below steps -

Step 1 - Click on report body and press F4, It will popup the body property window or you can also select the same by choosing the body component from the property window -
(screen shot for your reference)




















Step 2 - After selecting the body component,  increase the body width in order to remove the extra space from the report page.
(screen shot for your reference)


Step 3 - Keep changing in the body width until all the unwanted white space would be removed.

To perform a quick verification  check the fix, export the report into pdf.

I believe it will resolve the problem.

Sunday, 31 May 2015

SQL Server Important and common interview queries

Question 2: Using CTE, how to write a recursive query which will define the organization hierarchy level ?







Solution - We can achieve the above situation by the following query-

With CTE
As
(
Select E1.EmployeeID,
E1.FirstName, E1.LastName,
 E1.ManagerID,
CAST(''as nvarchar(30)) as [ManagerF],
CAST(''as nvarchar(40)) as [ManagerL],
 1 as EmpLevel from dbo.#MyEmployees E1 where managerId is null

Union ALL

Select E2.EmployeeID,
E2.FirstName, E2.LastName,
 E2.ManagerID,
 EL.FirstName as [ManagerF],
 EL.LastName as [ManagerL],

 EL.EmpLevel + 1 as EmpLevel from dbo.#MyEmployees E2
Inner Join  CTE as EL on EL.EmployeeID=E2.ManagerID
where E2.ManagerID is not null
)

Select
FirstName + ' ' + LastName as [Employee Name]
,ManagerF + ' ' + ManagerL  as [Manager Name]

, EmpLevel  from CTE

SQL Server Important queries