Friday, January 29, 2016

Project deployment model SQL Server 2012(SSIS)

This is new type of deployment model in SQL Server 2012(SSDT) called project deployment model. This can create one packet for all the packages in a single ISPAC extension file with this you can deploy package without hassle. This model includes package parameter, environment variables and environment references.
  1. By default ssis packages created in project deployment model, you can revert to original version
  2. Deployment packet is created with ispac extension.
  3. This packet can not contain any additional data. Except packages information
Steps to follow create the SSIS Project deployment model
Create SSIS Catalog in SQL Server to store. It stores application related data and to encrypt sensitive data
After creation on SSISDB. Go to integration service catalog and create one folder for each different project deployment. So that you can identify the package/project based on the folder.

After creating the folder you will find two things under SSISDB Main catalog
Reason for storing environment variable is deploy the packet in any environment like test, prod, uat
Now Create the SSIS Project deployment Model
Create one sample package in SSIS IDE
By default SSIS will create in Project deployment model. If you want you can convert it into package deployment model

Now depending on the Environment we need to move the data from one db to another for ex:- if you move package into TEST environment. Data need to move from test environment database to test environment data, likewise UAT db to uat db.
Now create one project parameter which will hold name of database which is passed to an environment and used in the target connection to connect target database. To create project parameter go to click on
Right click on project parameter open it. And click on new project parameter

  • Name:-  DatabaseName
  • Scope:- Project
  • Data type:- String
Defaultvalue:- Database info which you want hold in project params here am using testdb

And the project also contain connection manager (source and destination). To make connection manager dynamic you need to configure expression for the connection.
Right click on the connection manager and go to properties and edit the expression as show below
If you have many number of connection managers create many number of project parameters and map it to corresponding database name. Now execute the package.

Go to solution explorer and click on deploy

Click next on introduction screen you will see below screen which is asking you to choose the path of project. Select project deployment file if you have already. Now we are doing from ssis package we need to choose project deployment file

Click on next mention the server name where do you want to deploy. Select integration service catalog folder which you already created in earlier steps and click ok.

Click next here you will see the review information and click on deploy.

Done check SQL Server you will see below type structure which contains deployed package.

Sunday, January 24, 2016

Using execute package task in SSIS

Execute package task is used to break down the complex package into smaller one. This task allows you to run another package, executable file of workflow.
Salient features of execute package task:-
  1. Break the complex package into multiple packages and execute by calling them from this task. This will facilitates better maintenance and easier access to each individual task.
  2. Re-using the same tasks can be easy. For example you can use any package in any other package which require same functionality (or) same data.
  3. If you are loading star schema you require different packages to fill each dimension. In step by step loading each finishing task will contain one execute process task which call other package to start loading data into dimension.
  4. Controlling package security is major task. By breaking huge package into small package and giving permission to a specific part to the user this is a great security future granting access to the particular part
  5. A package that runs other package is generally referred as parent task, Execute process task is very use full in parent child configuration.
  6. Stored procedure execution, database maintenance activity can be planed through execute package task
Let’s consider this scenario for better understanding. Parent task will load data from HRdepartment data to destination in another database. Where the child task will updates the department id’s.
Create parent task which loads data from source table to destination
Create child task which contain department id updating task. Drag execute sql task into control flow and create connection to the destination table.
Paste the following code in sql command to update department id.
update [dbo].[HRD] SET DepartmentID= (case when GroupName='Research and Development' Then 1when GroupName='Sales and Marketing' then 2  when GroupName='Inventory Management' then 3  when GroupName='Manufacturing' then 4 when GroupName='Inventory Management' then 5 end )
Now go to parent task and add execute package task in control flow and start configuring it.
Go to package option and there you will find below options
Reference type:-
  1. Project Reference: - Package which already in the same folder (or) same project.
  2. External Reference: - Package which presents in another project.
Here I selected project reference where my child task exist in the same project.
PackageNameFromProjectReferece:- select the child task which you want to execute
User name and password not require. If the package is protected with user name and password then you need to enter username password and execute the package done.
Hrd table in another database.

Wednesday, January 20, 2016

Excel file upload to the sql server table by using SSIS

Let’s start with creating a small package in SSIS
We will start creating small project which loads data from source flat file to destination SQL Server database. here i have written few ways to load data. There may other ways that would be discussed later in our tutorials
  1. Import And export wizard (32 bit/64 bit)
  2. SSIS package
Loading data from excel/flat file to sql server:-
  1. Open SSDT press Ctrl+Shift+N (or) Select New Project Solution from File menu.
  2. You find  options to create a project

  1. Name your project. Whenever you create a project a solution file will be created in location folder c:\users\xxxx\documents\visual studio 2010\Projects. Click ok
  2. Go to ssis tool box drag data flow transformation from the tool box. And double click on it you will automatically entered into Control flow pane

  1. After entering into the Control flow  Drag excel source and flat file transformations from the control flow

  1. Here we are using two sources to load data into destination. So the column name should be same in both sources
  2. Select flat file and excel file which you want load into SQL server.

  1. Click on new and select create the connection excel file as show above picture
  2. Go browse and select the excel file where you stored

  1. Click ok and select name of the excel sheet. As 1st page of excel xxxx$

  1. Go to columns and click ok. You can check preview also. Next configure  Flat File source

  1. Click on new  create new flat file connection

  1. Click on browse select Flat file you want to load go to columns and click ok.
  2. Remember you must go to columns tab otherwise you can’t map the inputs to the outputs
  3. we are using merge join transformation to merge flat file and excel file

  1. before configuring merge join we need to set the property    IsSort to true for both transformations

17. Right click on the excel source go to show advanced editor

  1. go to input and output properties  click on external source output on the right pane you will find Is Sorted Propertie make it true and set out put orders of the columns id,ename,salary

  1. Set orders of the output columns  do same process for flat file also

  1. After setting both transformations sort key orders drag merge transformation

  1. Take one ole db. destination save and execute the package.
  2. For excel File loading Right click on the package go to debugging Run64bitruntime :false
  3. Press F5 to debug.

Check SQL SERVER table for loaded data

Installing sql server 2012

Before installing SQL server or its related tools check your machine configuration whether it is compatible or not. You can download SQL server 2012 from Microsoft site or Download here else you can use DVD set up to install.  Minimum system requirements to install setup given below
PROCESSOR: 1.5 GHz and above
Other versions may require additional set up files
Sometimes you require .Net framework to support actual setup installation. double click on setup file it will starts installation wizards.

Click on “new SQL Server stand-alone installation or add features to an existing installation. You will see screen like below picture
Click ok for setup support rules screen
Click ok for installation rules.
Click on next. And installation type click on next Enter product key if you have or try for evaluation period

Click next and accept agreement by the Microsoft. Click next select server role as SQL server feature installation for manual selection or all features with defaults

Select features and click next define installation rules and name your instance.

You can also change installation directory by browsing “instance root directory”. Click on next button

Click on next.  You will entered into server configuration wizard
Configure as show above figure. You can change these settings after installation in windows services click next to database engine configuration.
Here you can add current user to operate it and Authentication mode as mixed mode. So you can login with windows credentials or SQL credentials .define a password for sql authentication. Click next.
Add current user again. You can add administrator to access your instance

Again add current user. Click next you will entered into Distributed Replay controller and click next after error reporting click next done.

Click next you will see below screen select install. installation will be finished in few minutes