SSIS - Options to deploy a package in SSIS (2008)

Introduction

In this article we are going to see on how to deploy a SSIS package once we are done with developing, building and executing the package. Deploying an application is something which we need to take care since it performs based on the requirement like when the package should execute, who should execute the package etc. We have 3 options available with deploying a SSIS package and the options are as follows

1.       Deployment Utility

2.       Command line Executable

3.       SQL Server Management Studio

We will see on each approach and the steps involved in deploying the package. You can refer to my previous articles on SSIS to get some idea before going ahead with this article. The URL’s for those articles can be viewed at the below URL

Type

Type 1 : Deployment Utility

This approach is used to create an installer for the package and can be execute at the place where ever it’s required. This Utility is available by default to all the Integration projects; we will see the steps to make use of this utility.

Steps:

Go to BIDS (Check my previous articles on how to go to BIDS) and open the package solution which you want to deploy and right click on the project and go to properties as shown in the below figure.

A dialog will open where you have the option as Deployment Utility. Clicking on that will show the options at the right side of the dialog box where we need to set the CreateDeploymentUtility to TRUE and give the path where the installer need to be created as shown in the below screen

Once we are done, now right click on the project and give BUILD it will show the result at the bottom of the page if the build is succeeded or failed. If it’s succeeded it will create the deployment installer as shown in the below screen. You can copy these files to any location and double click to make use of it

Type 2: Command Line Executable

Microsoft has provided an Executable DTUTIL.EXE that can be used to deploy SSIS packages. This command like utility is a good option to script the package. We can use this executable directly or can make use of it in the batch file.

To execute it at the command prompt check the below script. Go to the path of the package and execute the below scripts.

DTUTIL /FILE Package1.dtsx

       /COPY

       SQL;SSISPackage1

In order to deploy the package at the file system level then go with the below script

DTUTIL /FILE Package1.dtsx

       /COPY

       FILE;C:\SSIS\SSISPackage1.dtsx

For more option on this utility check the below url

Type 3: SQL Server Management Studio

This approach required integration services to be connected and must be in running mode in order to use this deployment option. In the object browser you can see something like below

To deploy our package right click on the File system and select the Import Package from the menu. Fill the details as shown in the below screen

Clicking on OK will deploy the package

Conclusion

This article describes on the options available to deploy the SSIS packages.

SSIS - Options to execute a package in SSIS

Introduction

In this article we will see on how to deploy a package once we are done with creating and building the package.

Type :

Once a package is created and build it successfully, we have 3 options to make a deployment. We will look into each approach with an example.

Type 1:

DTEXEC command line utility

SQL Server provides a command line utility (DTEXEC.EXE) that helps the developers to execute the SSIS package. It can be directly used from the command prompt by moving around to the folder where the package is available and executing making use of this EXE.

DTEXEC /? Provides the list of available options to execute the package from the command prompt as shown in the below screen

So to execute the package go to that folder where the package is available and provide the syntax as shown in the below screen

This is the result once we execute a package in SSIS Command line utility. This example shows an error that package is not executed properly and it has some errors which need to be fixed.

For more details on DTEXEC utility refer to the below msdn article

Type 2:

SSIS Package Windows Application

This approach is straight forward a user interface option to execute the package. Microsoft has provided a user interface or we can say a tool kind of option to execute the SSIS packages. DTEXECUI.EXE is the User interface exe which performs the task of executing the package.

We can launch DTEXECUI.EXE by double clicking on the package itself directly (i.e. go to project folder and double click on *.dtsx file). It will open the graphical user interface as shown below.

As we can see there are many options available in order to execute the package based on our needs. If we want to follow the standard format then directly clicking on EXECUTE button at the bottom will do the task. We can navigate through each option and customize the package based on our needs.

You can have a look at the msdn article on DTEXECUI.EXE utility at the below url

Type 3:

SQL Server Agent Scheduling

The last and the final approach to execute the SSIS packages are the SQL Server Integration services Job step type which helps to schedule the package and execute it based on our needs. This is one of the easiest approaches since we have the UI to schedule the package and execute it without any user interactions.

In order to do these approaches go to SQL Server Management Studio à Connect to the Database using the credentials àopen object explorer and go to JOBS à Select New Job and fill the details based on our needs

Conclusion

In this article we have seen the different options to execute the SSIS packages. We will look into the deployment options in the upcoming articles.

Introduction to SSDT (SQL Server Data Tools)

WHAT IS SSDT ?

SQL Server Data Tools (SSDT) is a toolset which provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries.

In a previous blog entry I have described on how to install SSDT into the VS environment. During this I will briefly describe few of it’s features.

SSDT’s intention is not to replace the SQL Server Management Studio, but provide a developer a complete development environment, which the developer need not required to leave Visual Studio IDE to do any database related development. The tool does not contain all the features which you find in SSMS, yet it’ll provide sufficient functionality which will required to most of the developers during their development tasks.

I find following features, pretty  much interesting and helps to increase the productivity of the developer/team.

1. DESIGN & CODE VIEW IN A SINGLE SCREEN.

Ability to see the design view and the code view in a single screen is a wonderful thing. You do not have to move across screens. And the changes you do to the design view will be affected to the code immediately and vice versa.

 

2. ABILITY TO ADD CONSTRAINTS, INDEXES, FOREIGN KEYS & TRIGGERS WITHOUT CHANGING THE SCREEN

Adding constraints, indexes, etc.. are much easier. You can add those by right clicking and choosing the ‘add <object>’ menu like shown below.

 

3. IT USES ‘DECLARATIVE – MODEL BASED DEVELOPMENT’

What this means is that there is always an in-memory representation of what a database looks like—an SSDT database model— and all the SSDT tools (designers, validations, IntelliSense, schema compare, and so on) operate on that model. This model can be populated by a live connected database (on-premise or SQL Azure), an offline database project under source control, or a point-in-time snapshot taken of an offline database project (you will work with snapshots in the upcoming exercises). But to reiterate, the tools are agnostic to the model’s backing; they work exclusively against the model itself. Thus, you enjoy a rich, consistent experience in any scenario—regardless of whether you’re working with on-premise or cloud databases, offline projects, or versioned snapshots.

4. CONNECTED DEVELOPMENT

Although SSDT places great emphasis on the declarative model, it in no way prevents you from working imperatively against live databases when you want or need to. You can open query windows to compose and execute T-SQL statements directly against a connected database, with the assistance of a debugger if desired, just as you can in SSMS.

5. DISCONNECTED DEVELOPMENT

The new SQL Server Object Explorer lets you connect to and interact with any database right from inside Visual Studio. But SSDT offers a great deal more than a mere replacement for the connected SSMS experience. It also delivers a rich offline experience with the new SQL Server Database Project type and local database runtime (LocalDB).

Actually this is one great feature it has. Because due to many reasons, most of the time we are requested to work offline, or to do our development locally and later on merged. So this feature allows us to maintain a local database and do all the development using that. And later on merged/published to other database. And by doing a schema comparison, it’s possible to find out the changes we have done.

6. SCHEMA COMPARISON

This is a very valuable feature. This allows us to do a schema comparison between our development environment and the physical database (or vice versa) and find out the changes very quickly.

7. SAVING SNAPSHOTS OF THE DATABASE

Sometimes it’s required to keep snapshots of your database at different stages of the development. And the best thing is, it even allows you to compare between two database snapshots. So it’s easy to see the changes you have done compared to the previous stages.

8. ABILITY TO FIND ANY ERRORS OR REFERENCE ISSUES IN DESIGN TIME

When SSDT is used it’s easy to identify any syntax issue or any reference issue before deploying it to the database. E.g. assume we have one view which is referring to few columns of a table. Usually if someone change or remove any columns from the table which this view is referring, there is no way of identify that, till the view is used in our application. But when SSDT is used, it will show you these issues, when building the project. So these can be eliminated before we apply these to the deployment server.

These are some features which I have find very interesting and most developers expect. But having said that I am not saying it’s the complete set of features. There are few missing functionalities which I felt that it would have been even nicer, if those were there.

And you can find a good article in here (http://www.codeproject.com/Articles/357905/Evaluating-SQL-Server-Data-Tools) regarding the SSDT. You can find things in more depth.

Deployment model in SSIS 2012

Aim :- To Learn a New way of deployment model in MS SQL Server Integration Services i.e. SSIS 2012.

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 abrand 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 :-

• Design Default value is assigned and used in BIDS.
• Server Default value is assigned when project comes in the catalog and overwrites the Design Default value.
• 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.

• Open SSMS and Go to Integration Services Catalog. Right click on that and choose Create catalog.

• Once you click on Create catalog option, below window will pop up. After filling the desired values, Click OK button.

• Once you click on OK button, One “ssisdb” database is created.
• 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.
• To create a folder, Just click on ssisdb. Right click on ssisdb and choose create folder option.

• Once we click on Create folder, the below window will appear.fsf

STEP 2.  Create Integration services project in BIDS.

• Once we create a folder named “Test”, it’s time to go and create 1 Integration Services project inBIDS.
• Open BIDS and Create Integration Services project, name it as say “PHPRING”.
• 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.

• Go to Project name, in our case it is “PHPRING”.
• Right click on the project name and click on deploy. If you have any doubt follow the below screenshot.

 

• Once you click on “Deploy” option, it will bring up the below window.

• Once we click on Next, it will bring up next window i.e. “Select source”.

• Once we click on Next, it will jump to 3 option i.e. “Select destination”.

• 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.

• 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.

• Go to SSMS and expand Integration Services catalogs.
• Now, Expand ssisdb. Expand folder Test and then Expand Projects.
• 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.

• Simply right click on first package “DataFlow.dtsx”. Click on “Execute”.

• Once we click on Execute, it will bring up one window. Here simply click on “OK”.
• After that it will pop up below window. Just read the information and then click on “Yes”.

• 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. Preety 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.