Sunday, February 28, 2016

SSIS - Standard Reusable Templates

Sql-datatools.com
SSIS is a well-defined ETL platform to load the raw data from the various OLTP or OLAP sources into an established destination. SSIS provides pretty much all the tools that system need to get the job done but sometimes it’s not always easy to know what tools to use and how to use them. To avoid the different approaches, Standard Reusable Templates are the best way to follow the same standard to accomplish the development jobs.
An ideal SSIS package can contain items such as connection managers, log providers, control flow elements, dataflow elements, event handlers, variables, and configurations files. SSIS template packages are the re-usable packages that one can use in any SSIS project any number of times to maintain the development standards.
System can reuse these items when system uses a standard package template to create a new package. An ideal package template must have the following items:   
  1. Connection Managers and Log Providers: Connection manager and a log provider should be the common things for the ideal package template because all event information is logged in the same SQL Server database. 
     

  2. Common Variables and Configurations: There are some very common variables which could be used in most of the packages and system should have the same types of configurations settings or files.
  3. Common Execute SQL Tasks: An ideal package template should Execute SQL task controls to do truncate the data tables or attaches databases. This task is also responsible for deleting the target table of system import process. Therefore the SQL statement has to be updated, so the proper table will be deleted. 
  4. Common Script Task: It will be included (with all of the same properties and code) in all packages created from that template.
  5. Event Handlers: System may need to use same event handlers or error handling across ETL packages in a project. So, this should be the important part of the ideal package templates.
  6. Send Mail tasks: An ideal system should have a Send Mail task also in the package template that contains an SMTP connection manager and a property expression to build the Subject line. By using the task, package is capable to send the error alerts to the respective authorities.
  7. Common Task: In the many projects where many tasks are common across 70% packages e.g. Execute SQL Task, Data Flow elements, and Control Flow elements. It may differ project to project but it is always good idea to encapsulate the common task in a template and use it wherever required.
Implement SSIS Template Package: There are very basic steps to create a new SSIS package template in SQL Server Business Intelligence or SQL Server Data tools for Visual Studio 2012 on wards which is given below: 

  1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.  
  2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK. 
  3. Add the items that we want from the Toolbox to the Package.dtsx file. Once we are done with package template development, Click File, and then click Save Selected Items(Important Note: Give a package name that describes the functionality of the package.)
  4. Click File, and then click Save Copy of PackageName As. Here file name is the name of package. In the Save Copy of Package dialog box, click File System in the Package location box, type the following path in the Package path box, and then click OK. In this path, drive is the hard disk where Microsoft Visual Studio is installed:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\ Common7\IDE\ProjectItems\DataTransformationProject\DataTransformationItems
Note: You must have the admin credentials before done the above job. Type the path of the Visual Studio 2012 folder in the Package path box in case you didn't use the default location to install Visual Studio.

Guidelines: Use Package Template in other Project or Solution - After creating the package template and saved it on the defined location, we can reuse the same package n-number of times by using the following things. 

  1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project
  2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.
  3. In Solution Explorer, right click on Project name, click on Add and then New Item... Under Visual Studio installed templates, click the template that we want, type a name for the template, and then click Add. 

Note: The default name for the template in the Name box is the name of the template plus an incremented counter. For example, if the template name is IdealSSISTemplate.dtsx, the default name is IdealSSISTemplate1.dtsx.
Please keep in mind, if you want to do some modification in the existing package template then it should be good to have to keep the original one also. You could have parent and child package template also.

References: https://support.microsoft.com/en-us/kb/908018

Friday, February 12, 2016

SQL - Dynamic SQL Scripts

Sometimes, it's become urgent to use the dynamic columns, dynamic data filter parameters or dealing with dynamic tables and views names to meet the requirements then Dynamic SQL queries come to the picture to generate the expected outputs and writing dynamic SQL queries is become a very urgent task that requires much more discipline and understanding to avoid losing control over the SQL code. Dynamic SQL query can become very messy, and be difficult to read, troubleshoot and for the maintenance also. 
An interesting advantage of using dynamic SQL is that it provides access to any database because the dynamic code can append the database name before an object’s name.
As we know that purpose of dynamic query also does matter because by using them we can combine one or more T-SQL statements. These combinations may be based on conditional data joins, conditional where clause or condition output columns.
Dynamic SQL query is dependent on one of the following SQL commands - 
  1. Sp_ExecuteSql – This feature was introduced in SQL Server 7 which is a built-in stored procedure that takes two pre-defined parameters and any number of user-defined parameters. This is mostly usable command to invoke the dynamic SQL because it will prevail. We can use 4000 characters query string to invoke this command. The first parameter @inpStatement is mandatory, and contains a batch of one or more SQL statements and second parameter @inpParameters is optional and they are exactly the same as for the parameter list of a stored procedure. To prevent from SQL injections, this is the best option to use dynamic SQL. 
  2. EXEC() – This feature was introduced in SQL 6.0 and mostly used for quick throw-away things and DBA tasks. We can use 2000 characters query string to invoke this command. This is the one parameter based SQL command. It is less hassle than sp_executesql and easily permits us to make very quick things.
To understand the Dynamic SQL, we will use the following table structure as given below-
---- Create data table Employee
CREATE TABLE Employee
(
EmpId INT,
EmpName VARCHAR(25),
Department VARCHAR(25)
)

---- Insert Data into Employee
INSERT INTO Employee (EmpId,EmpName,Department)
VALUES (1, 'Ryan Arjun','Finance'),
(2, 'Kimmy Wang','Admin'),
(3, 'Lucy Gray','Sales'),
(4, 'Billy Doug','Admin'),
(5, 'Gery Dean','IT')

----Pull the data from Employee
SELECT EmpId,EmpName,Department
FROM DBO.Employee
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
How to use Dynamic SQL
By using the above table, we can create the dynamic SQL by declaring three variables such as first variable to define the requested columns, second variable to define the source table name and third variable to create the dynamic SQL query as given below-
---- Declare local Variable
DECLARE @inpColumns VARCHAR(30),
@inpTableName VARCHAR(30),
@SQL NVARCHAR(MAX);

---- Set values
SET @inpColumns = 'EmpId,EmpName,Department';
SET @inpTableName = 'Employee';

---- Create Dynamic SQL
SET @SQL = 'SELECT '+@inpColumns+' FROM dbo.' + @inpTableName;

---- PRINT Dynamic SQL
PRINT @SQL

SELECT EmpId,EmpName,Department FROM dbo.Employee


---- Invoke Dynamic SQL
EXEC(@SQL)
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT

---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT

Dynamic Pivot Query
If we want to get department wise employees and show them into column wise instead of rows then we can use dynamic SQL. We can get the dynamic column name for each department. We need to use two variables; one variable to hold the dynamic columns and another variable to store dynamic SQL. In this example, we are using pivot feature of SQL in dynamic SQL such as –
---- Declare local Variable
DECLARE @inpPivotColumns VARCHAR(200)='',
@SQL NVARCHAR(MAX);

---- Set values
SELECT @inpPivotColumns += N', ' + QUOTENAME(DEPARTMENT)
FROM (SELECT DISTINCT DEPARTMENT FROM DBO.EMPLOYEE)XYZ;

------ Create Dynamic SQL
SET @SQL = 'SELECT '+STUFF(@inpPivotColumns, 1, 2, '')+' FROM
(SELECT DEPARTMENT, COUNT(*) as CTR
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENT
)TBL
PIVOT
( SUM(CTR) FOR DEPARTMENT IN ('
       + STUFF(REPLACE(@inpPivotColumns, ', DEPT.[', ',['), 1, 1, '')
       + ')
) AS DEPT';

------ PRINT Dynamic SQL
PRINT @SQL

SELECT [Admin], [Finance], [IT], [Sales] FROM
(SELECT DEPARTMENT, COUNT(*) as CTR
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENT
)TBL
PIVOT
( SUM(CTR) FOR DEPARTMENT IN ( [Admin], [Finance], [IT], [Sales])
) AS DEPT


---- Invoke Dynamic SQL
EXEC(@SQL)
Admin
Finance
IT
Sales
2
1
1
1

---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
Admin
Finance
IT
Sales
2
1
1
1


Benefits of Dynamic SQL
There are a lots of benefits of dynamic SQL scripts. For an example, we are building a dynamic web page or web report to accept the conditional based multiple parameters to generate the output result then dynamic SQL script approach is the best way to handle this kind of requirement because of Dynamic SQL have the following benefits- 
  1. Easily translates the input data, including any parameter markers, into an SQL statement.
  2. Easily provide a way to prepares the SQL statement to execute and acquires a description of the result table. 
  3. Provide a simple way to reusable code for database objects and integrate variables, parameters and joins.
  4. Provide a sorting functionality for any requested data column.
  5. Easily implement the conditional data filter as well as data output also.
  6. Obtains, for SELECT statements, enough main storage to contain retrieved data and executes the statement or fetches the rows of data
  7. Processes the information returned and handles SQL return codes.
Drawbacks  of Dynamic SQL
If there are a lots of benefits of dynamic SQL scrip but they have some drawbacks also as given below:
1 - Writing dynamic queries inside the procedure is very near to SQL Injection attacks. 
2 - When a stored procedure is executed first time, SQL Server caches the execution plan, and served the next request from this cache. This gives much performance difference. But dynamic queries won't allow to generate a static execution plan, and this will be compiled for every request. 
3 - We need to be vary careful to write them to avoid the syntax errors because SQL Server does not show the syntax errors intelligence.
4 - It's very tough to debug the queries and spent a lot of time to debug them.
5 - It hides errors in queries since it is appended in a string.
6 - We cannot use them in SQL functions and views.