Export CSV File Data into SQL Server using SSIS

SSIS Support importing CSV(Comma Separated Values) files into SQL Server. Using the Data flow task we can achieve this. 



Drag the Data Flow in Control Panel. Double Click on Data Flow Task. It will go to Data Flow task tab.

Now in Data Flow Task.  Right Click ----Select  SSIS ToolBox---Select Source Assistant

 Click New. Select Flat File as Source Type and Click New.

 Select Format  as delimited. Select the check box Column names in the first data row if the csv file contains the columns names in the first row. For changing Column names ,Data Type and OutputColumnWidth select the Columns tab as shown below.

 Run the Package as administrator by pressing F5 or Green button.

Some time you may get error as below.

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Folder Path" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Flat File Source [2]] Error: The "Flat File Source.Outputs[Flat File Source Output].Columns[Folder Path]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[Folder Path]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

The above error can be solved by changing the outputcolumnwidth in Flat File Connection Manager Editor or using the below steps. 

Right Click on the Flat File Source ---- Click on Show Advanced Editor

Select Input and output Properties tab.

Expand the Flat File Source Output . Expand the Output Columns .

Select respective column and Go to Data Type Properties section then change the Length.

Build and run the project. The data export from CSV to SQL Server Destination.

Working with SQL Server Integration Services Catalogs (SSISDB)

SQL Server Integration services catalogs are very useful for developers and administrator to identify the issues and current state of SSIS Packages.

This feature is available in enterprise and business intelligence editions.

This is one of the key features for SQL Server integration Service (SSIS). 

In order to use the project deployment model is SSIS first SSISDB should be created. If SSISDB is not available it will show the error message as below.

“An Integration Services catalog (SSISDB) was not found on this server instance”

SSISDB must be created using the Integration services catalogs.

A database must not be created with the name SSISDB. It will show the error message if any other user database created with the name SSISDB.

“SSISIDB has been found on the server. But it is not recognized as integration services catalog”.

You must drop or rename any other user database if it contains the database name SSISDB.

SSISDB name is reserved for SSIS catalog.

In order to create SSISDB go to integration services and click on create catalog as shown below.

CLR must be enabled and password must be provided for creating the SSISDB. The password is used for encrypting the SSISDB.

Now SSISDB created successfully.

 Once SSIDB created go to Tables node. Several system tables were created for logging information about SSIS packages.

Some of the important tables are listed here.

[internal].[executions] : Provides the name of the packages that has been executed, project name and in which folder that package is exist in SSISDB and who has executed the SSIS Package.

Internal.folders   : will provide the list folder created in ssisdb catalog

Internal.folder_permissions : list the permission to the folder

 Using the above tables mentioned in the screen shot we can able see the folders ,folder permissions,execution details ,environment and environment variables etc in ssis catalog, 

Using the System stored procedures we can able to create the folder ,folder permissions,environment and data tap for logging ,deleting folder,project deployment etc.

There is also standard reports available to view using the GUI as shown below.

Integration services Dashboard will show the below standard reports

All Executions :

Displays the details of all integration services executions that have performed on server.

All Validations:

Displays validations that have performed on the server

All operations:

Displays the operations that have performed on the server.

.

All Connections :

Displays the connection related information that have applied on server.

We able to view all the packages that have run in the past 24 hours.

We can add custom reports to SSIS dash borad which uses the SSRS rdl files.

Working with Data Profile Task in SSIS (SQL Server Integration Services)

Data Profile Task is used to identify the data quality issues.  For identify the data integrity problems we can use the data profiling task.

Data Profile Task is available in control flow tab in SSIS.

Drag and drop the data profile task and double click the task for editing.

In General tab destination type is used to store the profile information. There two destination types available

1)       File Connection

2)      Variable

Destination will allow for specifying the connection string for File Connection or Variable name for variable.

Create a file connection using the destination.

Open Profiler viewer is used to view the existing data profile file.

In order to create a new data profile click on new profile. If you need to know how to create connection go to this post.

I have downloaded the adventure work 2012 database from codeplex site.

Check the checkbox to verify the data quality issues. Click ok.

Then it will go to the profile request tab. Then Click on ok.

Then execute the task as shown.

Profile executed successfully. Profile will be saved in XML format. In order to open the profile data use the dataprofileviewer.

The dataprofileviewer will be available in the below mentioned path.

C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ DataProfileViewer

(Drive\programfiles(x86)\ Microsoft SQL Server\sqlserver2012 version\bin folder).

You can for Address line null ability ratio is very high.

How to roll back transactions in SSIS (SQL Server Integration services)

In SQL Server Database programming we use begin transaction and roll back transaction for transactions, Similarly SSIS also will support transaction in package execution.

The below example will shows how to roll back the entire transaction in case of transaction failure.

In order to show this I used two execute SQL tasks

One for creating a table and second one insert into the newly created table.

In First execute SQL Task

create table testrollback(id int,transname varchar(100))

 In Second execute SQL Task I have provided the following SQL commands

insert into [dbo].[testrollback] values (123,'Insert')

insert into [dbo].[testrollback] values ('fail','Insert')

By default the transaction support option for ExecuteSQLtask or package is supported. There are three options available for package in SSIS.

        *   Not Supported

        *  Supported

        *  Required

When transaction option set to require the “Distributed transaction coordinator” service should be started. Otherwise you will get the following error while running the package or individual task.

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Once the distributed transaction started you can use transaction option required so that if any failures in the execution of task in control flow then it will roll back the entire transaction.

First we will check the transaction option Supported at package level and execute the package.

The First task is running successfully and the second task failure because of conversion error. In this scenario the create table statement in first task is committed and in second task first statement executed successfully only second statement will fail.

verify the script the changes in database

For Supported Transaction option it does not roll-back the changes in package execution. 

Then let us try to change the transaction option of package to required

Drop the testrollback table from database and execute the package.

Execute the package once again and verify the changes in database

This time package failed but its roll-back the commands in first execute SQLtask. The table was not created in the destination table.

Loading XML data into a table in SQL Server

Using XQuery in SQL Server we can Convert the XML Data into Table format.

The below Example shows how to convert the XML into relational table format using Xquery method.

Value() method is used to fetch the values of SQL Data Type. 

It accepts two parameters one is attribute name and second parameter is data type.

Nodes() method is used  to represent the xml data in XML node format. I will be used for shred the XML into Relational table.

Example :

DECLARE @Persondetails XML ;

SET @Persondetails = '
                                       
                                               mamidi                                               malls
                                              

                                  

                                  
                                               Reddy                                               Mamidi
                                              

                                  
                                  
                                               Naga                                               Eswara
                                              

                                  
                                  '

 The below query will Convert the above XML into Relational Database

       select  t.c.value('Surname[1]','VARCHAR(50)') As Surname,                      t.c.value('Givename[1]','VARCHAR(50)') As GivenName,                     t.c.value('./Address[1]/@Builiding','VARCHAR(50)') AS Building,                     t.c.value('./Address[1]/@Street','VARCHAR(50)') AS Street,                     t.c.value('./Address[1]/@State','VARCHAR(50)') AS State       From @Persondetails.nodes('/PersonDetails/Person') as T(c)

The Output of the query as
 

Surname	GivenName	Building	Street	State
mamidi	malls	131	Lonsdale street	VIC
Reddy	Mamidi	150	Queen street	VIC
Naga	Eswara	130	Elizabeth street	VIC

How to Pass the a table variable to script task from Execute SQL to Script Task in SQL Server

How to Pass the a table variable to script task from Execute SQL to Script Task in SQL Server

Here I will show how to pass the variable data from one task to other task.
For this first we need a table.

Create an EMP table

create table emp (empid int identity primary key,name varchar(100),salary int); GO insert into emp values ('malls',10000),('reddy',20000),('contract',30000)GO One we have created the scripts execute in the database
Use Execute SQL Task to fetch the values from this table based on emp id.

select empid,name,SALARY from empWHERE empid = 1
 EmpId 1 is the input parameter for execute SQL task.

Create connection string for the database in which the above script executed.

Go to result section and set the result name as 0. For fullresult in ExecuteSQL Task result set must be set to zero.

Variable must be created as Object for full result set. 

 Double Click on Script task and select the Object variable as read only varaiable.

Edit script task

In order to convert the Object into datatable we will use Oledbdataadapter

Using the below namespace.

using System.Data.OleDb;

Copy the below code.

    Dts.TaskResult = (int)ScriptResults.Success;

            DataTable dt = new DataTable("Test");

            OleDbDataAdapter ad = new OleDbDataAdapter();

            ad.Fill(dt, Dts.Variables["User::Variable"].Value);

            dt.WriteXml("D:\\Malls.xml");

            System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\Malls.csv");

            foreach (DataColumn col in dt.Columns)

            {

                sw.Write(col.ToString()+",");

            }

            sw.WriteLine();

            for (int i = 0;i

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    sw.Write(dt.Rows[i][j].ToString()+",");

                }

                sw.WriteLine();

               

            }

            sw.Close();

Save the script and execute.

It will create the CSV with the following output.

empid name SALARY

1 malls 10000

2 reddy 20000

3 contract 30000

Execute SQL Scripts using Foreach Loop Container

Execute SQL Scripts using Foreach Loop Container.

We can execute the SQL Scripts from file using the Foreach Loop Container in SSIS.

In this demo am using the two Control Flow Tasks.

For Each file enumerator is used to go through all file in a directory. suppose we have an SQL Scripts in a file need execute one by one for each loop is best option in SSIS.

Add a variable for dynamic file connection in for each loop

Make sure proper mapping variables.for first variable index should be zero.

Once its completed drag the execute SQL Task. 

Provide the connections for the execute SQL task

Make sure file connection type as file connection.

Once connection is ready execute the SSIS task.