Thursday, December 31, 2015

New ways of 2012 SSIS deployment model


Description:-  
In this article we are going to learn a New way of deployment model in SSIS 2012. It’s always been a challenge & a difficult thing for SSIS developers at the time of package deployment. It’s ‘a brand new feature’ in SSIS coming with MS SQL Server 2012. Prior to this version, it was a tedious process to deploy SSIS packages in Legacy model. We can call this new deployment model in SSIS 2012 as “Project Deployment Model in SSIS”.

Why we call this as Project deployment model in SSIS and what is the reason behind this?
In the new deployment model in SSIS, we don’t have any option & we are unable to deploy the packages individually. Just we have deployment option at Project level only, that’s why we can call this as“Project Deployment Model”. The new Project Deployment Model in SSIS includes Project/Package Parameters, Environments, Environment variables and Environment references.

Project/package parameters :-
In this new deployment model, we can declare project parameters/package parameters. The major difference between these two parameters is “scope”. You can create project parameters at project level and package parameters at package level.

We can use project parameters to any package in the project, package parameters can only use to that package only. These parameters allow us to assign values to the properties within at the time of package execution. Project parameters are used to supply any external input the project receives to one or more packages in the project.
In the above screen shot you can clearly observe project parameters option. Once you click and open it appears like left side image. The best part of these parameters is that you can mark any of them as sensitive and it will be stored in an encrypted form in the catalog.
There can be three default values for these parameters :-
o    Design Default value is assigned and used in BIDS.
o    Server Default value is assigned when project comes in the catalog and overwrites the Design Default value.
o    Execution value is assigned in reference to a specific environment variable during execution.

Environments and Environment variables :-
We have different type of environments like Development, Test and Production. It is a place for environment variables which are used to apply different groups of values to the properties of package components by means of environment reference during runtime. An environment reference is the mapping between an environment variable to pass a value to a property of a package component. A project can have multiple environment references.
To know more about project/package parameters, environments and environment variables you can visit – www.msdn.com.

Integration services catalog :-
In the below theoretical part we will frequently use “catalog” term. Now we are going to discuss what is integration services catalog, where it exists?
This is the brand new feature in MS SQL Server 2012. It comes with SQL Server Management Studio(SSMS). It stores the data about deployed projects including packages, variables and environments. We must know one mandatory thing i.e. “we can create only one catalog per instance”. When you create a catalog you need to provide a password which will be used to create a database master key for encryption and therefore it’s recommended that you back up this database master key after creating the catalog.
The catalog uses SQLCLR (the .NET Common Language Runtime (CLR) hosted within SQL Server), so you need to enable CLR on the SQL Server instance before creating a catalog.
Finally let’s jump into practical session with example – My best part of any article.

STEP 1.  Create integration services catalog.
o    Open SSMS and Go to Integration Services Catalog. Right click on that and choose Create catalog.
integration services catalog
o    Once you click on Create catalog option, below window will pop up. After filling the desired values, Click OK button.

o    Once you click on OK button, One “ssisdb” database is created.
o    It’s time to create one folder inside ssisdb database. When we are going to deploy our project than that total content is deployed in this folder only.
o    To create a folder, Just click on ssisdb. Right click on ssisdb and choose create folder option.
o    Once we click on Create folder, the below window will appear.fsf

STEP 2.  Create Integration services project in BIDS.
o    Once we create a folder named “Test”, it’s time to go and create 1 Integration Services project inBIDS.
o    Open BIDS and Create Integration Services project, name it as say “PHPRING”.
o    Create Couple of packages inside this project “PHPRING”.
I hope you all are aware of how to create integration project in BIDS and how to create few sample packages inside that project. Now I don’t want to go and create all those things now. Already I have few packages  existed in PHPRING.
My First package name is “DataFlow Task”. The internal operation of this task is to extract the data from Flat file. After exteracting data it applies sorting on those columns by using soft transformation. Finally, we can load that sorted output into Flat file destination.
My second package name is 
“Execsql”. It contains 1 Execute SQL Task. I issued one Select statement in this package.

STEP 3.  Package Deployment time.
o    Go to Project name, in our case it is “PHPRING”.
o    Right click on the project name and click on deploy. If you have any doubt follow the below screenshot.
 
o    Once you click on “Deploy” option, it will bring up the below window.
o    Once we click on Next, it will bring up next window i.e. “Select source”.


o    Once we click on Next, it will jump to 3 option i.e. “Select destination”.
o    Once we click on Next, we will get a Review window”. In this window, we can know all the information in – Select source, Select destination tabs.
o    Once we click on Deploy option, immediately “Results window” will pop up.

Once our Deployment is over, simply click on Close button.

STEP 4.  Checking whether Project deployment is Succesful or not?
Once we complete the above process, we can jump into SSMS (SQL Server Management Studio) and see whether our project “PHPRING” is deployed in “Test” folder or not.
o    Go to SSMS and expand Integration Services catalogs.
o    Now, Expand ssisdb. Expand folder Test and then Expand Projects.
o    Expand PHPRING (our project name) and then expand Packages.
By observing the above screen shot, we can conclude that our project “PHPRING” with two packageswere deployed successfully into SSISDB.

STEP 5.  Executing our Package.
In this step we are going to run our first Package i.e. “DataFlow.dtsx” from “Integration Services Catalog“. It is very simple to run packages from here.
o    Simply right click on first package “DataFlow.dtsx”. Click on “Execute”.
o    Once we click on Execute, it will bring up one window. Here simply click on “OK”.

o    After that it will pop up below window. Just read the information and then click on “Yes”.
o    Once we click on “YES” it will bring up a window with the following information.

STEP 6.  Output in Flat file Destination.
In this step we are going to see our package “DataFlow” is executed successfully or not. To do this, simply go to flat file destination path and see the data. Pretty simple right?
By observing above screen shot we can concluded that first package i.e. “DataFlow” is executed successfully and we can also observe the data is in Flat file destination.

References :- www.mssqltips.com
Summary :-
1.       Created one Integration services catalog in SSMS.
2.       Created a folder named “Test“ inside this catalogue.
3.       Created Integration Services Project named as “PHPRING” with two Packages (DataFlow, Exec sql).
4.       Once we created all these things, we then Deployed “PHPRING” project to SSISDB.
5.       Finally, executed First package and saw the result in our output Flat file destination.

I hope my effort of making you aware on new way of Deployment model in SSIS 2012 helped you in your journey of SSIS.  If you feel any queries, please post them as comments below. Also, your feedback will be well appreciated as it helps us to improve better and better every time.

SSIS 15 Best Practices - SQL Server Integration Services

Introduction
Listed below are some SQL Server Integration Services (SSIS) best practices:
1.     Keep it simple.
Avoid using components unnecessarily. For example:
·           Step 1. Declare the variable varServerDate.
·           Step 2. Use ExecuteSQLTask in the control flow to execute a SQL query to get the server date-time and store it in the variable
·           Step 3. Use the dataflow task and insert/update database with the server date-time from the variable varServerDate.
·           This sequence is advisable only in cases where the time difference from step 2 to step 3 really matters. If that doesn't really matter, then just use the getdate() command at step 3, as shown below:
Hide   Copy Code
--create table #table1 (Lap_Id int, LAP_Date datetime)
Insert into #table1 (Lap_Id, LAP_Date) values (1, getdate())
2.     Calling a child package multiple times from a parent with different parameter values.
When a child package is executed from a master package, the parameters that are passed from the master need to be configured in the child package. For this, you can use the ‘Parent Package Configuration’ option in the child package. But, for using the ‘Parent Package Configuration’, you need to specify the name of the ‘Parent Package Variable’ that is passed to the child package. If you want to call the same child package multiple times (each time with a different parameter value), declare the parent package variables (with the same name as given in the child package) with a scope limited to ‘Execute Package Tasks’.
SSIS allows declaring variables with the same name but the scope limited to different tasks – all inside the same package!
3.     SQL job with many atomic steps.
For the SQL job that calls the SSIS packages, make multiple steps, each doing small tasks, rather than a single step doing all the tasks. In the first case, the transaction log grows too big, and if a rollback happens, it may take the full processing space of the server.
4.     Avoid unnecessary typecasts.
Avoid unnecessary type casts. For example, the flat file connection manager, by default, uses the string[DT_STR] data type for all the columns. In case you want to use the actual data types, you have to manually change it. Better to change it at the source-level itself to avoid unnecessary type castings.
5.     Transactions.
Usually, the ETL processes handle large volumes of data. In such a scenario, do not attempt a transaction on the whole package logic. However, SSIS supports transaction, and it is advisable to use transactions where the atomicity of the transaction is taken care of.
For example, consider a scenario where a source record is to be spitted into 25 records at the target - where either all the 25 records reach the destination or zero. In this scenario, using a transaction, we can ensure either all the 25 records reach the destination or zero.
6.     Distributed transaction spanning multiple tasks.
The control flow of an SSIS package threads together various control tasks. It is possible to set a transaction that can span into multiple tasks using the same connection. To enable this, the “retainsameconnection” property of the Connection Manager should be set to “True”.
7.     Limit the package names to a maximum of 100 characters.
When an SSIS package with a package name exceeding 100 chars is deployed into SQL Server, it trims the package name to 100 chars, which may cause an execution failure. So, limit the package names to a maximum of 100 characters.
8.     Select * from…
Make sure that you are not passing any unnecessary columns from the source to the downstream. With the OLEDB connection manager source, using the ‘Table or View’ data access mode is equivalent to ‘SELECT * FROM <TABLE_NAME>’, which will fetch all the columns. Use ‘SQL command’ to fetch only the required columns, and pass that to the downstream. At each down-stream component, filter out the unnecessary columns.
9.     Sorting.
Sorting in SSIS is a time consuming operation. At the places where we know that data is coming from database tables, it’s better to perform the sorting operation at the database query itself.
10.  Excel Source and 64-bit runtime.
The Excel Source or Excel Connection manager works only with the 32 bit runtime. When a package using the Excel Source is enabled for 64-bit runtime (by default, it is enabled), it will fail on the production server using the 64-bit runtime. Go to the solution property pages\debugging and set Run64BitRuntime toFalse.
11.  On failure of a component, stop/continue the execution with the next component.
When a component fails, the property failParentonFailure can be effectively used either to stop the package execution or continue with the next component - exception - stop/continue with the next component in a sequence container. The value of the constraint connecting the components in the sequence should be set to "Completion", and the failParentonFailure property should be set toFalse (default).
12.  Protection.
To avoid most of the package deployment error from one system to another system, set the package protection level to ‘DontSaveSenstive’.
13.  Copy pasting the Script component.
Once you copy-paste a script component and execute the package, it may fail. Just open the script editor of the pasted script component, save the script, and execute the package – it will work.
14.  Configuration filter – Use as a filter.
It is a best practice to use the package name as the configuration filter for all the configuration items that are specific to a package. It is especially useful when there are so many packages with package-specific configuration items. For the configuration items that are general to many packages, use a generic name.
15.  Optimal use of configuration records.
Avoid the same configuration item recorded under different filter/object names. For example, if two packages are using the same connection string, you need only one configuration record. To enable this, use the same name for the connection manager in both the packages. Also, use a generic configuration filter. This is quite convenient at the time of porting from one environment to another (e.g.: from UAT to production).