History of SQL Server Integration Services

Integration Services is the successor to Data Transformation Services (DTS). DTS had humble beginnings. It was started on a shoestring budget with very few resources. Its first incarnation was a proof-of-concept transformation, which later became known as the data pump. The proof of concept caught the attention of some folks around Microsoft, and it was given some funding.

The first release of DTS shipped with SQL Server 7.0 to receptive users. The alternatives at the time were either difficult to work with, expensive, or both. Many DBAs were forced to write custom transformation software, which was inflexible and difficult to maintain. Some tools had limitations, such as the need for source and destination schemas to match exactly, direct dependence on a particular database product, and/or no transformation capabilities. Many wrote custom parsing and transformation applications. For example, many companies are only now converting from hand-coded flat file parsers, SQL scripts, and transformation code to a standard platform such as Integration Services.

The first release of DTS addressed several of these issues and simplified life for a lot of people. By using OLEDB for its data access layer, DTS could access various data sources with little or no custom coding. DTS was also affordable because it shipped "in the box" with SQL Server. Users had access to all the power of more expensive products, yet incurred no additional cost for their ETL tools. This was obviously a benefit to IT shops trying to stretch their budgets. DTS was a flexible product that was easy to use. There were also a number of standard tasks in the box, including the Transform Data, Execute Process, Active X Script, Execute SQL, and Bulk Insert Tasks.

SQL Server 8.0 added even more functionality by adding more tasks. The Execute Package, FTP, and MSMQ Tasks added incremental improvements across the product. However, users experienced some frustration with DTS when attempting to work with large datasets and some of the other limitations inherent in a script-based tool. The time was ripe to create a truly enterprise-ready integration tool.

In 2000, SQL Server decided to make a substantial investment in the ETL and Integration space and brought together some talented folks who formulated the ideas behind the Data Flow Task and the next version of Integration Services. Over a period of five years, the development time frame for SQL Server 2005, the DTS team completely redesigned and rewrote DTS to become Integration Services 2005.

Integration Services 2008 is the next incarnation of that release. While not as revolutionary a release as in 2005, Integration Services 2008 brings some new capabilities and incremental improvements across the product.

What Is SQL Server Integration Services?

Depending on whom you ask, you might get different answers to that question ranging from descriptions such as a data import/export wizard, to an ETL tool, to a control flow engine, to an application platform, or to a high-performance data transformation pipeline. All are correct because Integration Services is a set of utilities, applications, designers, components, and services all wrapped up into one powerful software application suite. SQL Server Integration Services (SSIS) is many things to many people.

Data Import/Export Wizard
One of the most popular features of Integration Services is the Import/Export Wizard, which makes it easy to move data from a source location such as a flat file or database table to a flat file, table, or other destination. The Import/Export Wizard was the first utility developed back in the SQL Server 7.0 time frame and continues today as an important utility in the database administrator's (DBA) toolbox.

ETL tool
ETL is an acronym for Extract, Transform, and Load and describes the processes that take place in data warehousing environments for extracting data from source transaction systems; transforming, cleaning, deduplicating, and conforming the data; and finally loading it into cubes or other analysis destinations. Although Data Transformation Services (DTS), Integration Services' predecessor application, was considered a valuable tool for doing ETL, Integration Services is where true Enterprise ETL became available in SQL Server.

Control flow engine
The processes involved in moving data from location to location and transforming it along the way are not restricted to only processing data. Integration Services provides a control flow for performing work that is tangentially related to the actual processing that happens in data flow, including downloading and renaming files, dropping and creating tables, rebuilding indexes, performing backups, and any other number of tasks. Integration Services provides a full-featured control flow to support such activities.

Application platform
Developers can create applications that use Integration Services as a platform, embedding the engines within their application using the provided object models. As a developer, you can embed the Integration Services engines and components within your applications using the object models.

High performance data transformation data pipeline
That's a mouthful and really incorporates two ideas: high performance and data pipelining. The Data Flow Task is a high-performance tool because you can use it to perform complex data transformations on very large datasets for incredibly performant processing. The pipeline concept means that you can process data from multiple heterogeneous data sources, through multiple parallel sequential transformations, into multiple heterogeneous data destinations, making it possible to process data found in differing formats and on differing media in one common "sandbox" location.

Importing and exporting bulk data with SQL Server's bcp utility

The Bulk Copy Program (bcp) utility in Microsoft SQL Server enables database administrators to import bulk data into a table or export it from a table into a file. It also supports several options that define how data will be exported, where it will be imported and which data will be loaded.
This tip discusses several examples of bcp commands to bulk-copy data in and out of SQL Server tables. The examples have been tested on SQL Server 2005 and SQL Server 2008, and for all examples I used the AdventureWorks sample database.

Exporting data with the bcp utility

One of the simplest operations that you can perform with the bcp utility is to bulk-copy data out of a SQL Server table into a text file. By entering the command at a Windows command prompt, you can run a bcp command. The following command, for example, copies the data from the Sales.vSalesPerson view in the AdventureWorks database to the C:\Data\SalesPerson.txt file:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.txt -c –T

As you can see, the bcp command begins with the utility name, followed by the fully qualified name of the table (database.schema.table). The out keyword is next, which tells the bcp utility that the data will be exported from the table. The path and file name of the target text file follows the out keyword. Note that the command examples in this tip might wrap to multiple lines. All the examples, however, should be run as a single command.

In addition to these basic arguments, the bcp utility supports several switches that control the utility's behavior. In the example above, the -c switch indicates that all data is treated as character data, regardless of how it is stored in the source table. If you do not specify the -c switch or another type-related switch, you will be prompted to specify the type for each column after you enter the bcp command. You may notice that bcp command switches are case sensitive.

The next switch in the above example is -T, which tells the bcp utility to use a trusted connection to connect to the SQL Server instance. If you do not specify -T, you must provide your user name (the -U switch) and your password (the -P switch), or you will be prompted for this information.

Because no instance is specified in the preceding example, the utility uses the default instance on the local machine. To specify a SQL Server instance, use the -S switch, followed by the server name, as in the following example:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.txt -c -T -S Server01

The bcp utility will now connect to the default instance on Server01. If you want to connect to an instance other than the default, you must specify the instance name along with the server name, as in Server01\SqlSrv.

By default, the bcp utility uses tabs to separate fields in the target file. You can, however, override that behavior by using the -t switch, as in the following example:

bcp AdventureWorks.Sales.vSalesPerson out C:\Data\SalesPerson.csv -c -T -t,

In this case, a comma is specified after the -t switch, which means that the data fields will now be separated by commas. This allows you to save the data to a .csv file, as I've done here, which can be handy if you want to view the data in a Microsoft Excel file.

These examples have been limited to exporting data from a table. But you can also use the bcp command to run a Transact-SQL query and export the query results. The following bcp command, for example, includes a SELECT statement that retrieves only the SalesPersonID, FirstName, and LastName columns from the vSalesPerson view:

bcp "SELECT SalesPersonID, FirstName, LastName FROM AdventureWorks.Sales.vSalesPerson"
queryout C:\Data\SalesPerson.csv -c -T -t,

In this scenario, the query, enclosed in quotes, is passed into the bcp command instead of the table name. In addition, the out keyword has been replaced with queryout. But the rest of the command is the same as the preceding example. As a result, the SalesPerson.csv file will now contain only the three specified columns. You can also refine your query further: You can include, for example, a WHERE clause that qualifies which rows are returned from the source table.

Importing data with the bcp utility

The bcp utility makes importing data as simple as exporting data. To run the examples in this section, first run the following T-SQL script to create the SalesPeople table in the AdventureWorks database:

USE AdventureWorks
GO
IF OBJECT_ID (N'SalesPeople', N'U') IS NOT NULL
DROP TABLE dbo.SalesPeople
GO
CREATE TABLE dbo.SalesPeople (
SalesPersonID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL
)

To import data, you need a source file from which to copy the data. For instance, the following command uses the recently created SalesPerson.csv file to load data into the SalesPeople table:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPerson.csv -c -T -t,

First, you must specify the target table, followed by the in keyword that replaces out or queryout in this scenario. Next, you must specify the path and file name of the source file, followed by any applicable switches.

When you run this command and view the results, note that while the source file includes the salesperson IDs, these values are not inserted into the SalesPersonID column. The column is defined as an IDENTITY column, so the source data is ignored. To retain the original values, you must add the -E switch to the command, as in the following example:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPerson.csv -c -T -t, -E

Your table will now include the desired data.

Using a format file

When importing or exporting data, you may find that the structure of your source data does not match the structure of the target data. The columns in a text file, for example, might not be in the same order as those in your target table, or the number of columns may be different. You can address this issue by creating a format file that maps the structures of the source and destination. Let's look at an example to demonstrate how this works.

Suppose you use the following command to export data from the vSalesPerson view to the SalesPeople.txt file:

bcp "SELECT LastName, FirstName, SalesPersonID FROM AdventureWorks.Sales.vSalesPerson"
queryout C:\Data\SalesPeople.txt -c -T -t,

This command uses the same arguments as those in previous examples. Note, however, the order in which the columns are retrieved from the view: LastName, then FirstName, and finally SalesPersonID.

Now suppose you plan to use this file to import data into the SalesPeople table. The columns in the table, however, are defined in a different order from that in the text file. To address this issue, you can create a format file to map the columns from the source to the destination. The following command demonstrates how to create a format file:

bcp AdventureWorks.dbo.SalesPeople format nul -f C:\Data\SalesPeople.fmt -c -T -t,

As previous examples show, the command first specifies the target table. This time, however, the table name is followed by the keywords format nul, which indicate that the bcp utility should create a format file. The -f argument is then used to specify a path and file name for the format file, followed by several switches. As a result, when you run this command, a format file is generated that contains the structure of the SalesPeople table.

The following data shows the content of the SalesPeople.fmt format file that is generated by the above command:

10.0
3
1 SQLCHAR 0 12 "," 1 SalesPersonID ""
2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "\r\n" 3 LastName SQL_Latin1_General_CP1_CI_AS


The first row of the file (10.0) identifies the version of bcp that is being used. The second row (3) identifies the number of columns in the table, and the next three rows provide information about the columns themselves:


The first field indicates the order in which the columns should appear in the source document.


The second field shows the source file data type for each column. Because I specified the -c switch when I generated the file, a character data type is used for all fields as they are extracted from the data file. SQL Server will then convert this data into the correct types as the data is inserted.


The third field shows the prefix length for the field, which is used by SQL Server to provide the most compact file storage. A value of 0 is used automatically if you specify the -c switch when creating the format file.


The fourth field indicates the number of bytes for the data type of a particular field.


The fifth field shows how columns and rows are terminated. Because the -t switch was used when creating the format file, the field values of the source file must be terminated by commas.


The sixth field maps to the order in which the columns appear in the SQL Server table.


The seventh and final field provides the collation information for character columns in the SQL server table.

To use the format file to import data into the SalesPeople table, we must modify the file as follows:

10.0
3
1 SQLCHAR 0 100 "," 3 LastName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 1 SalesPersonID ""


As you can see, the order of the columns has been modified to reflect how they appear in the format file. The SalesPersonID column is now listed last and it is terminated with \r\n. The LastName column is now listed first and is terminated with a comma.

After you modify and save the format file, it is ready to use in your bcp commands. The following example demonstrates how to call the format file:

bcp AdventureWorks.dbo.SalesPeople in C:\Data\SalesPeople.txt -f C:\Data\SalesPeople.fmt –T

Note that, when you import data from the SalesPeople.txt file, you must also use the -f switch to call the format file. Also notice that you no longer need to include the -t and -c switches because the information is now included in the format file.

How to import an Excel file which has columns with more than 255 characters to SQL Server using SSIS?

When the driver determines that an Excel column contains text data, the driver selects the data type based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row.

You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. By default it is 8 rows.

Using BULK INSERT to Load a Text File

his example combines dynamic SQL, BULK INSERT and the proper handling of double-quotes to solve a client's problem with loading various text file formats into a database. (This article has been updated through SQL Server 2005.)

One of my clients contacted me recently and said they needed some help creating a stored procedure that imported data from a text file. They wanted the procedure to accept three parameters: PathFileName, OrderID, and FileType. The PathFileName is simply the name and physical location of the source file on the hard drive, the OrderID is generated in the program that calls the procedure and the FileType indicates the format of the data in the source file. The two possible formats for the source data are shown here:

FileType=1 (TxtFile1.txt)

"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","sara@parker.com"

FileType=2 (TxtFile2.txt)

Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com

BULK INSERT

I decided to use BULK INSERT to implement the solution. The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with bcp (bulk copy program) via a script. In pre-7 versions the only way you could access bcp functionality was from a command prompt. I am not going to list the full syntax of BULK INSERT here (but you can find it here), because it is a little long and most of it does not apply to the problem I am solving. Instead, I will show the valid BULK INSERT statements used to load the data shown above.

BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')

TmpStList is the target table and TxtFile1.txt is the source data file. The source file is located in the root of the C drive. The FIELDTERMINATOR argument allows you to specify the delimeter used to discern column values.

The valid statement for FileType=2 is shown here:

BULK INSERT tmpStList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = ',')

The only difference is the value of the FIELDTERMINATOR argument.
The Solution

The stored procedure used to implement the solution is fairly straight forward once you master the BULK INSERT statement. The only real trick is loading the data that comes to you in FileType=1 format. Because a double-quote starts and ends a data row, it too is loaded in the table. The FIELDTERMINATOR works between columns, not at the beginning or end of a row. To workaround this I simply load the data into a temporary table and then use a CASE statement and the SUBSTRING and DATALENGTH functions to load the correct data in the final table. The FileType=2 data will load as-is, but I still put in the temporary table for consistency (easier programming).

The SQL statements that create the temporary and final table are shown here.

CREATE TABLE StudentList
(
StID int IDENTITY NOT NULL,
StFName varchar(50) NOT NULL,
StLName varchar(50) NOT NULL,
StEmail varchar(100) NOT NULL,
OrderID int NOT NULL
)
go
CREATE TABLE TmpStList
(
stFName varchar (50) NOT NULL,
stLName varchar (50) NOT NULL,
stEmail varchar (100) NOT NULL
)
go

The procedure used to implement the data loading is shown here.

SET QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE ps_StudentList_Import
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS

--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
BEGIN
-- Valid format: "John","Smith","john@smith.com"
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
END
ELSE
BEGIN
-- Valid format: John,Smith,john@smith.com
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
END

--Step 2: Execute BULK INSERT statement
EXEC (@SQL)

--Step 3: INSERT data into final table
INSERT StudentList (StFName,StLName,StEmail,OrderID)
SELECT CASE WHEN @FileType = 1 THEN SUBSTRING(StFName,2,DATALENGTH(StFName)-1)
ELSE StFName
END,
SUBSTRING(StLName,1,DATALENGTH(StLName)-0),
CASE WHEN @FileType = 1 THEN SUBSTRING(StEmail,1,DATALENGTH(StEmail)-1)
ELSE StEmail
END,
@OrderID
FROM tmpStList

--Step 4: Empty temporary table
TRUNCATE TABLE TmpStList
go

The first thing you need to know is that SET QUOTED_IDENTIFIER is set to OFF because double-quotes are used to set the value of a variable. Dynamic SQL is used to create the BULK INSERT statement on-the-fly, and double-quotes are required to do this. The final BULK INSERT statement is a function of both the @PathFileName and @FileType parameters. Once built, it is executed with the EXEC() statement and the source data is loaded into the temporary table.

Once the data is in TmpStList, the next step is to load it into the final table. I use the CASE statement to determine the value in the @FileType parameter and manipulate accordingly. When @FileType=1, the SUBSTRING and DATALENGTH functions are used to remove the double-quotes from the StFName and StEmail columns. When FileType=2, the data is loaded as is and no manipulation is required.

After the data is loaded I empty the temporary table with the TRUNCATE TABLE statement. I could have used DELETE to accomplish this, but TRUNCATE TABLE has less of an impact on the transaction log.

The following shows the way to call the procedure specifying a different FileType value for each call.

EXEC ps_StudentList_Import 'c:\TxtFile1.txt',1, 1
EXEC ps_StudentList_Import 'c:\TxtFile2.txt',1, 2

Different Options for Importing Data into SQL Server

Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis. One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use either DTS (SQL 2000) or SSIS (SQL 2005). In this tip we take a look at some of these other options for importing data into SQL Server.

In addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server. Some these other options include bcp, BULK INSERT, OPENROWSET as well as others. The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

---

BCP
This is one of the options that is mostly widely used. One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command. This command allows you to both import and export data, but is primarily used for text data formats. In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

For more information about bcp click here.

---

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL. This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest FROM 'C:\ImportData.txt' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

For more information about BULK INSERT click here.

---

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest. The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

For more information about OPENROWSET click here.

---

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

For more information about OPENDATASOURCE click here.

---

OPENQUERY
Another option is OPENQUERY. This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table. There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command. This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'C:\ImportData.xls', NULL, 'Excel 8.0' GO INSERT INTO dbo.ImportTest SELECT * FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

For more information about OPENQUERY click here.

---

Linked Servers
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server. This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\ImportData.xls',
NULL,
'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

Insert data from Excel to SQL Server 2005 by using copy and paste commands

Loading data from SQL Server to Excel is a common practice in many automated and ad-hoc processes completed by DBAs on a daily basis. Traditionally loading data from Excel to SQL Server has been completed by DTS, SSIS, BCP, OPENROWSET, Import\Export Wizard, etc. Unfortunately, for a simple ad-hoc process this can be a time consuming task with a fair amount of clicks and\or coding. With all of the new features with in SQL Server 2005, are any new tricks available to streamline the Excel to SQL Server loading process?

Yes - SQL Server 2005 Management Studio provides the ability to copy and paste columns directly from Excel to SQL Server when the table and column names match up. Let's walk through a simple example. Assume you have a database called 'Test' with a table called 'MyTable' consisting of two columns (MyID, MyDesc). In addition, you have an Excel worksheet where you want to load data directly from Excel to SQL Server. Let's walk through setting up and testing that scenario.

Step 1 - Create the Database and Table

CREATE DATABASE Test GO CREATE TABLE [dbo].[MyTable]( [MyID] [int] NOT NULL, [MyDesc] [varchar](100) NOT NULL)

Step 2 - Open the Excel worksheet, select data only and copy the data 'Ctrl +C'

Step 3 - Open SQL Server 2005 Management Studio and navigate to the table

Step 4 - Right click on dbo.MyTable, choose 'Open Table' then left click on below area

Step 5 - The area below will be highlighted, then right click and choose 'Paste'

Step 6 - Validate the data will be inserted into the table

Export data from SQL Server to Excel

Exporting data from SQL Server to Excel can is possible in different ways.
1) Data Transformation Services (DTS)
2)SQL Server Integration Services (SSIS)
3)Bulk Copy
4)OPENROWSET

Loading XML data into SQL Server 2008

SQL Server DBA’s will often come across a scenario where they need to load data from an XML document into SQL Server 2005 or 2008. As XML is being used widely by many organizations to communicate and share data, it is becoming a common task for DBA’s to load XML data into SQL Server. This article provides a step by step guide on how to create an configure an SSIS package which can be used to import data from XML file to SQL Server 2008.

The first step is to launch BIDS (aka SQL Server Business Intelligence Development Studio). In BIDS select File | New | Projects, the will pop up the New Project screen. From here we need to select Business Intelligence Projects under the Project Types and then we need to select Integration Services Project under the Templates. Then you need to provide the SSIS Project name, location etc. Once you have entered the details left click OK to create the solution. The project creation screen will look as shown in the image below:



BIDS will open up the SSIS Package Development Studio Environment. The Screen will have a Toolbox on the left and Solution Explorer and Properties on the right side of the screen. One of the most important spaces which we need to notice is the Connection Manager space which is at the bottom of the screen. In the center of the screen you will see the work space which has four tabs namely, Control Flow, Data Flow, Event Handler and Package Explorer.



To build the SSIS package you need to drag Data Flow Task from the toolbox and drop it on the Control Flow tab. Then you need to rename the Data Flow Task to Load XML and double click on it to open the Data Flow Task. By doing so, you will notice that you are now in the Data Flow Tab of BIDS.



To buid a Data Flow, drag the XML Source from the Data Flow Sources Toolbox and drop it on the Data Flow designer surface and rename the task as XML Source Data File.



You need to double click the XML Source Data File task which will open the XML Source Editor where you need to select the XML file location as the value for Data Access Mode. Then you need to specify the actual path for the xml data file for XML Location and finally click the Generate… button to create and save the XSD file.



Next you need to Drag the SQL Server Destination Data Flow task from the toolbox and rename it to Load XML Data to SQL Server. Below are the following steps for the remainder of the configuration

1) You need to connect the green arrow from the XML Source Data File to the Load XML Data to SQL Server task 2) Double click the Load XML Data to SQL Server task to open up the SQL Destination Editor and click the New… button which will pop up the Configure OLE DB Connection Manager.



3) For a valid connection to the SQL Server Destination database one needs to click on the New… button which will once again pop up the Connection Manager where the Provider needs to be selected as Native OLE DB\SQL Server Native Client 10.0, SQL Server name, Authentication Mode and finally the destination database name. Once added all the details click OK to save the changes.



4) The table name needs to be provided where the data imported from the XML file will be stored in the destination database. You then need to click the Mappings to check whether the elements in the XML document are mapped correctly to the table column in SQL Server Table. Click OK to save the settings. The final screen will look as shown below:



5) The package screen in BIDS will look as shown in the following image:



6) Select the SSIS package named “LoadXML.dtsx” if renamed otherwise by default BIDS names it as “package.dtsx”. Right click “LoadXML.dtsx” package which will popup a window where you need to select the Execute Package option, this will run the SSIS package.



7) Once the package has successfully executed you can see the tasks turning green and it will also mention the number of records transformed and published to SQL Server.



8) The sample XML data file used in this example is shown in the following image:



9) The data which was loaded into the SQL Server Table by the SSIS package is shown below:



10) Save the SSIS package by pressing CTRL + S