1. Mac Flat Package Editor
2. Flat Package Editor

This SQL Server SSIS tutorial shows how to export data stored in database table into a flat file by using SSIS package.Using SQL Server Business Intelligence Development Studio (BIDS), SQL Server BI developers can easily create SSIS package to transfer table data to a text file.

SQL Server Integration Services enable Business Intelligence developers to to automate tasks like to export data, to import data, etc.Microsoft Business Intelligence Development Studio (BIDS) provides a Visual Studio IDE environment for developers to create reusable packages (SSIS packages) that will be executed by SQL Server Integration Services.

I have bunch of 100 logfiles in a location.I need to load one by one.I am using the foreachloop container with flat file connection manager.The problem here is if a Flat file source editor try to open a file whcih is opened or opend for writing the Package failes.So any one can suggest how to overcome this issue.Is there any Readonly properties like that avilable in the flat file file. Package Name Description Version NVT2008BQ1 NVT2008 8 DHVQFN20 plastic dual in-line compatible thermal enhanced very thin quad flat package; no leads; 20 terminals; body 2.5 4.5 0.85 mm SOT764-1 NVT2008PW1 NVT2008 8 TSSOP20 plastic thin shrink small outline package; 20 leads; body width 4.4 mm SOT360-1. However, you will have to update the package for all the connection information based on your SQL Server 2008 installation. Open Visual Studio 2008 IDE and setup an SSIS project as shown in the diagram below. After you name the solution, click OK to create the project. The package will be named as Package.dtsx by default.

Let's create SSIS package using BIDS and execute this package to export data from SQL Server database table into a text file (or flat file).

Open SQL Server Business Intelligence Development Studio.
I've SQL Server 2008 R2 Business Intelligence Development Studio installed on my computer so I'll be working on SQL Server 2008 R2 and its tools for this SSIS example.

Create new SSIS project following BIDS menu options 'File > New > Project...'
Then choose 'Integration Services Project' from Visual Studio installed templates.Name the SQL Server Integration Services project as 'SSIS Tutorial'.
The Integration Services project will create a default empty SSIS package titled Package.dtsx under SSIS Packages folder in Solution Explorer window.You can rename it by a right click on the SSIS package and choosing Rename in context menu.

On the left side of the Business Intelligence Development Studio, you will see a Toolbox containing tools developed for specific tasks.These tools can be configured for solutions of common tasks like bulk insert, executing sql scripts, executing FTP commands, send mail, back up database, rebuilding index, or even shrink database task.

Since our aim in this SSIS tutorial is to export data from SQL Server to flat file, we will choose Data Flow Task
Double click or drag-drop Data Flow Task object on to the Control Flow surface of the SSIS package in design view.

When you double click on Data Flow Task object on design view a new tab 'Data Flow designer surface' will be active to configure data flow task.First we'll configure data source component, which is SQL Server database in our SSIS tutorial.Since we will directly export data from SQL Server table to text file without any transformation, we will not use any of the Data Flow Transformation tasks.As last step, we need to identify the destination component which is the flat file in our sample SSIS package.

Let's configure source component as SQL Server for sample SSIS package.
Choose OLE DB Source component from Tools and drag-drop it onto Data Flow designer surface.

Double click on OLE DB Source object to configure the data source for reading.I will not demonstrate here how OLE DB Source object will be configured for database connection to SQL Server.SQL Server BI developers can easily create new SQL Server data connections for the data source to export to flat file.

I'm using SQL Server 2008 R2 sample database AdventureWorks2008R2 for the SSIS package tutorial as data source.

I also plan to export all data stored in database table HumanResources.Department, I choose 'Table or view' in the 'Data access mode'.It is also possible to define a SQL query as the source of export data task by choosing the 'SQL command' data access mode.

Continue the SSIS package source editor by selecting the table or view name

OLE DB Source editor screen enables developers to preview source data.BI developers can exclude columns from export list in Columns tab by clearing checkbox beside column names or rename the output column name

By pressing OK buton, the configuration of the OLE DB Source component is complete.This means we have configured data source for SQL Server export task.Now we can configure the destination component for exporting data from SQL Server to flat file or to text file.

Among Data Flow Destinations components in Toolbox, choose Flat File Destination for this SSIS tutorial which will get data from SQL Server and write it into a flat file in text format.

When the Flat File Destination object is displayed on the designer surface, connect two objects.You can create a connection between two objects from data source to flat file destination object.All you have to do is click on green arrow of OLE DB Source component and then to click on the Flat File Destination component.

After the connection is build, we can now continue to SSIS tutorial by configuring the Data Flow Destination component Flat File Destination.Double click on the item on Business Intelligence Development Studio design surface.We have to configure the Flat File connection manager. Choose New buton.In Flat File Format screen, you will be requested to choose flat file format among available formats: delimited, fixed width, fixed width with row delimiters, and tagged right.

I choose Delimited file format for target text file of the data export task.The next step is identifying the flat file name and path in Flat File Destination Editor screen.

Point to the file folder and type the name of the flat file where you want to export SQL Server table data, and click OK to end SSIS package developing

An important configuration setting for Flat File Destination component is the overwrite settings.If you want to overwrite data which might be already in the text file when the export data task is executed, mark 'Overwrite data in the file' checkbox.But if you want to append export data at the end of the flat file each time the SSIS package is executed, then make sure that you clear the 'Overwrite data in the file' checkbox.

Now our sample SSIS package is completed. We can now test if we can export data in SQL Server database table to flat file in selected file folder.To test the SSIS package, use F5 (Start Debugging) or Ctrl+F5 (Start Without Debugging) options from Debug menu.

After the SSIS package execution in Business Intelligence Development Studio (BIDS), I see that data stored in sample database table is exported to text file HumarResources-Department.txt in target folder.As you see SQL Server Integration Services services along with Business Intelligence Development Studio will help SQL Server developers provide easy and fast solutions for many operational tasks like exporting data from SQL Server to flat file.I hope SQL Server BI professionals like this SSIS tutorial.

Execute SSIS package using BIDS

During the development phase of the project, developers can test the SSIS package execution by running the package from BIDS.

Open BIDS, configure a new package ----------------->

Press Alt+Ctrl+L for solution explorer ----------------->

Select the package ----------------->

Right-click and select execute package option

Execute SSIS package using DTEXEC.EXE :

Command Line utility

Using the DTEXEC command-line utility can execute an SSIS package that is stored in the File system or SQL Server.

For example: DTEXEC.EXE /F “C:packagescheck points .dtsx”

Do you want to Master MSBI? Then enrol in 'MSBI Training' This course will help you to master MSBI

Executes SSIS package using DTEXEC UI.EXE :

Using the execute package utility graphical interphase can execute an SSIS package that is stored in the file system or SQL server.

In the command line type DTEXEC UI.EXE and press enter which will open up execute package utility editor.

In executing package utility editor select the general tab,

Package Source – File System

Package – Click Browser ----------------->

Select any package from the list and click open ----------------->

Click execute to execute the linked/ embedded package.

(or)

The execute package utility is also used when you execute the SSIS package from the integration services node in the SQL server management studio.

Open SQL Server Management Studio ----------------->

Connect to integration services ----------------->

Expand stored packages ----------------->

Expand file system

Select file system and right-click, Select import

Package editor

Package location – file system

Package – click browse ----------------->

Select any package from

The list and click open ----------------->

Package name - place cursor ----------------->

Click ok ----------------->

Select imported package ----------------->

Right-click and select run package ----------------->

In execute, package utility editor click execute

Execute the SSIS package using SQL server agent (using jobs):

Open SQL server management studio ----------------->

Connect to database engine ----------------->

Select SQL server agent

Note: Ensure that SQL server agent service is start mode

Select Jobs ----------------->

Right-click and select a new job

Provide Job Name as Load for each loop container ----------------->

Select Steps page ----------------->

Click new ----------------->

Step Name - Load for each loop container

Type - SQL Server Integration Service Package ----------------->

In General Tab,

Package Source - File System

Package – click Browse ----------------->

Select any package from the list of package ----------------->

Click open ----------------->

Click ok ----------------->

Mac Flat Package Editor

Select Schedule package ----------------->

Click new ----------------->

Provide the job Name as Load For each loop –Container ----------------->

Schedule Type – Recurring ----------------->

Set the Frequency (When to start the execution of the specified package) ----------------->

Click ok twice

Lookup Transformation:

It erased to compare the data from source to reference dataset. Using a Reference dataset using simple equally joint.

Note: While implementing data warehousing the reference data must be dimension table.

Steps to implement Look Up Transformation:

Open Business Intelligence development studio ----------------->

Create a new package and rename it as lookup .dtsx ----------------->

dn control flow drag and drop and flow task ----------------->

dn data flow, drag and drop OLEDB Source ----------------->

Double click on OLEDB Source to edit it ----------------->

Provide Connection Manager if exists and select

Production .product category ----------------->

Select columns and click ok

Drag and drop lookup Transformation and make

A connection from the source to lookup ----------------->

Provide connection manager and select production

.product subcategory ----------------->

Click configures error output and set Redirect Rows under error header ----------------->

Click ok ----------------->

Click ok ----------------->

Drag and drop OLEDB destination ----------------->

Make a connection from Lookup to destination ----------------->

Double click on OLEDB definition to configure it ----------------->

Provide destination connection manager and ----------------->

Click new to create a new destination table ----------------->

And rename the table as Matched data ----------------->

Click ok ----------------->

Select Mappings ----------------->

Drag and drop OLEDB destination to capture

UN matched records from source to reference dataset. ----------------->

Make a connection from Look Up to OLEDB destination

Using error output (Read data flow path) ----------------->

Click ok in lookup error output editor ----------------->

Double click on a destination to configure it ----------------->

Provide destination connection Manager and click

New to create destination table and rename it as

UN matched-records ----------------->

Select mapping and click ok ----------------->

Execute package

Note:

• Matched records will be updated to the destination.
• All the unmatched records will be inserted to the destination.
• In Lookup transformation, edit or remove the mappings between all the columns expert between Product category ID.

Scenario: TO the Dynamic flat file destination

In control flow drag a drop data flow Task ----------------->

Define the following variables with respect to the package.

In Data flow drag and drop OLEDB source

Double click on OLEDB source to edit it ----------------->

Provide a Source connection manager and select production. Production sub-category

From drop-down list ----------------->

Select columns ----------------->

Drag and drop flat file destination and make a connection from OLEDB Source to flat-file ----------------->

Double click on the flat file to edit it ----------------->

Click new to configure new flat file connection Manager ----------------->

Select Delimited flat-file format ----------------->

Click ok ----------------->

Connection Manager Name - Dynamic vendor flat file

Description – Dynamic vendor flat file

File Name – Type the following path and file with an extension that is not available at the destination.

Flat Package Editor

D:ssis PackagePackagevendor.txt ----------------->

Select columns page ----------------->

Click ok ----------------->

Select Mappings page ----------------->

Click ok, In Connection Manager, Select dynamic vendor flat file ----------------->

Press F4 for properties ----------------->

Expression – click browse

Select connection string and click in expression builder, provide the following expression which create a new flat file dynamically

@ [user:: Uv source path]+””+@[user::UV File Path] + “-”+(DT_WSTR,10)(DT_DBDATE)

@ [System::start Time] +’.txt’ ----------------->

Click ok twice ----------------->

Close property window ----------------->

Execute package

Script task:

Script task is used to design custom interphases.

Scenario:

Create a text file on every corresponding month dynamically using script task.

Define the following variables type string

Uv Source path: D:ssispackagespackages

Uv file name: Product category Details on

Uv Full path: ----------------->

Drag and Drop script task ----------------->

Double click on the script task to editor configure it. ----------------->

Select Script from the left panel and set, ----------------->

Read-Only Variables – Uv Source path, Uv File Name

Read-write variable – Uv Full path ----------------->

Click Design Script, Opens MS Virtual Studio for Applications IDE, ----------------->

Provide the following VB.Net Script to create dynamic text file the specified name.

Dim s Source path As String

Dim s File name As String

Dim s Full path As String

‘D:ssis package.Package

S Source path = Dts. variables (“Uv source path”).value.Tostring

‘Product category Details on sFile Name = Dts.variables(“Uv File Name”).Value.Tostring

‘D:ssis packagespackagesproduct category Details on-

sFull path = ssource path + sFile Name +”_”+Month

(Now ()) ,Tostring() +Year(Now()).Tostring()+.”txt”

Dts.variables(“Uv Fullpath”).value = sFullpath.To string() ----------------->

Select Debug Menu and Select build to build the above Scripting ----------------->

Select file menu and Select close and return ----------------->

Click ok ----------------->

Drag and drop data flow task and make a connection from script task to data flow task ----------------->

In Data Flow Drag and Drop OLEDB Source ----------------->

Double click on OLEDB Source to configure to ----------------->

Provide connection Manager if exits ----------------->

Select Product category table ----------------->

Select columns from left pane and click ok ----------------->

Drag and drop flat file destination and make a connection from OLEDB source to flat file destination ----------------->

Double click on flat file destination ----------------->

Click new to create new connection Manager ----------------->

Select Delimited flat file format ----------------->

Click ok ----------------->

Provide flat file connection manager name and description if any ----------------->

Type the following path ----------------->

D:ssis packagepackageproduct category Detailson.txt ----------------->

Select columns from left panel ----------------->

Click ok ----------------->

Select mapping from left panel ----------------->

Click ok ----------------->

To connection manager select flat file connection manager ----------------->

Press F4 for properties and set, expression - click Browse ----------------->

Select connection string and click browse to build the expression in expression builder ----------------->

Expand Variables ----------------->

Drag and Drop User:: Uv Full path in to expression ----------------->

Section, i.e @ [user:: Uv Full path] ----------------->

Click ok twice ----------------->

Close properties window ----------------->

Execute the package

Providing Security for SSIS Package:

The protection level is in the SSIS package that is used to specify how sensitive information is saved with

In the package and also whether to encrypt the package or sensitive portions of the package.

Example1 the sensitive information would be password to the Database.

Steps to Configure protection level in SSIS

Open Business Intelligence Development Studio

Create OLEDB connect with server authentication and provide

Design package

Select package in control flow, right-click

Select properties,

• Security

Protection level – Don’t save Sensitive

Don’t save Sensitive:

When you Specified Don’t Save Sensitive as the protection level, any sensitive information

Is not written to the package XML file when you save the package. This could be useful when

You want to make sure that anything sensitive is excluded from the package before sending

It to someone. After saving the package with this sending, open the OLEDB Connection Manager,

The Password is black even though save my password checkbox is checked.

Encrypt Sensitive With User Key:

Encrypt Sensitive with User Key encrypt Sensitive information based on the credentials of the

The user who created the Package.

There is a limitation with this setting if another user (a different user than the one who created

The package and saved it) open the package the following error will be displayed, error loading

Encrypts Sensitive with user key; failed to encrypt protection level XML load (dts. Password).

Encrypt Sensitive With Password:

The Encrypt Sensitive with password setting require a password in the package and that

Password will be used to encrypt and decrypt the sensitive information in the package. To fill in the

Package password clicks on the button in the package password field of the package and provide

Password and confirm password. When you open a package with this setting you will be prompted

To enter the password.

Note: The Encrypt Sensitive with Password Setting for the Production level property overcomes

The limitation of the encrypt Sensitive with user key setting by allowing any user to open the package

As long as they have to password.

Encrypt All With Password:

The Encrypt All with password Setting used to encrypt the entire content Of the SSIS package with the specified password. You specify the package Password in the Package

Property, Same as Encrypt Sensitive with password settings. After saving the package you can

View the package XML code that is already encrypted in between encrypted data tags in the package XML.

Encrypt All With User Key:

The Encrypt All with User Key Setting is used to encrypt the entire

Contents of the SSIS package by using User Key this means that only the user who created the package

Will be able to open it, view or modify it, and run it.

Server Storage:

The server storage Settings for the Production level property allows the package

To return all Sensitive information when you are saving the package to the SQL server. SSIS packages Saved to SQL Server use the MS DB Database.

Pivoting and Un pivoting:

The presentation of the data is required for easy analysis turning columns into rows

And rows into columns are another way of presentation of data. So that the end-user can understand

It easily.

Un pivot:

A process of turning columns to Rows is known as Unpivot.

Steps to Configure Unpivot

Prepare the following Excel Sheet

Open Business Intelligence Development Studio

Create a new package and rename it as UN pivot. dtsx

In control flow drag and drop Data Flow Task

In Data Flow drag and drop Excel source

Double click on Excel source to configure it ----------------->

Click ok ----------------->

Click Browse ----------------->

Select Unpirot.xls file and click open ----------------->

Click ok ----------------->

Select sheet 1 from the drop-down list ----------------->

Select columns and click ok ----------------->

Drag and drop Unpivot transformation and make a connection from the excel source to Unpivot ----------------->

Double click on Unpivot ----------------->

Select the below columns to unpivot then, Jan, Feb, March, April, May, June ----------------->

Rename pivot key value column Name as – Months ----------------->

Specify sales amount as a Derived on Destination column for all the selected pivoted Key values or input columns ----------------->

Click ok ----------------->

Make sure that the Excel source file is closed. ----------------->

Drag and drop Excel destination ----------------->

Make a connection from Unpivot to Excel destination ----------------->

Double click on Excel destination to configure it ----------------->

Click New to create new destination excel sheet and rename it as Unpivot data ----------------->

Click ok ----------------->

Select Mappings ----------------->

Execute package

Pivot:

A process of turning rows into columns is known as a pivot

Steps to configure the pivot

Prepare the following Excel sheet for source data.

Rename the Excel Pivot.xls ----------------->

Open Business Intelligence Development Studio ----------------->

Create a new package and rename it as Pivot. dtsx ----------------->

In control flow drag and drop data flow task. ----------------->

In Data Flow Drag and Drop Excel source ----------------->

Double click on Excel source to configure it ----------------->

Click New ----------------->

Click Browse ----------------->

Select pivot .xls and click open ----------------->

Click ok ----------------->

Select sheet 1 from the drop-down list ----------------->

Drag and Drop pivot transformation and make a connection from Excel source to Pivot ----------------->

Double click on Pivot ----------------->

Select Input columns tab and check all input columns ----------------->

Select input-output properties tab ----------------->

Expand pivot default output ----------------->

Expand Input column ----------------->

Select Year and Set, pivot usage – 1 ----------------->

Select Quarter input column and Set, pivot usage – 2

Select Sales Amount input column and set, pivot usage – 3

Pivot Usage:

Pivot Usage tells ssis how to treat the data what its role during the

Transformation process

0 – The column is not pivoted1

• The column is part of the set key that identifiers 1 or more rows as part of 1 set.

All input rows with the same set key are considered into 1 output row.

• The column is a pivot column
• The values from this column are Placed in columns that are created as a result of the pivot. ----------------->

Export pivot default output and create the following columns by click the add columns button Year Q1, Q2, Q3, Q4 ----------------->

Copy or Note the Lineage ID of input column year

Select year output column and set,

Pivot Key-value – Year

Source column – 58(Lineage ID of year input column)

Select Q1 and set,

Pivot Key-value – Q1

Source column – 64

Select Q2 and set,

Pivot Key value – Q2

Source column – 64

And follow the same process for Q3 and Q4 output columns ----------------->

Click Refresh ----------------->

Click ok ----------------->

Drag and Drop Excel destination ----------------->

Double click on Excel destination to edit it ----------------->

Provide Excel connection Manager, ----------------->

Click New to create a new table (sheet) and name it as pivot data ----------------->

Click ok twice ----------------->

Execute package

For In-depth knowledge of MSBI click on: