Dot Net Code Snippets

Good Morning to Everyone.

Today I am going to discuss about the SSIS (Sql server Integration Services) ie: how to Export the XML file to Sql Server with Column name and Data.

What is SSIS ?

 - To solve complex busniess problems.

 - sending and downloading files
 
 - sending email messages in response to events

 - Update Datawarehousing

 - Cleaning and Data mining

 - Managing Sql server data objects

Its the concept of ETL - ie. Extract - Transform - Load

Purpose:

1) Automate maintenance of sql server Database.

2) Update MultiDimensional Cube data as well.

I have the situation that xml file can be imported to the SQL server ie: i have the xml file look like this

<dataset>
<items>
 <empid>1</empid> 
 <empname>ela</empname> 
 <empcity>madhurai</empcity> 
 <empsalary>10000</empsalary> 
 </items>
 <items>
 <empid>2</empid> 
 <empname>arun</empname> 
 <empcity>chennai</empcity> 
 <empsalary>20000</empsalary> 
 </items>
 <items>
 <empid>3</empid> 
 <empname>kumar</empname> 
 <empcity>bangalore</empcity> 
 <empsalary>25000</empsalary> 
 </items>
 </dataset>

I have convert this XML file to DB tables. It should be same schema , contraints, Relationship, trigger etc..
For this SSIS is a easy way to convert any XML , text , Word , Excel file to Sql Database.

So you have to choose Business Intelligence and Development Studio (BIDS) to do the task

There are three ways to create a packages.

1) SSIS Designer(BIDS - Business Inelligence Development Studio)

2) API Programming

3) Import and Export Wizard

Here I am using the BIDS its a Graphical tool to create a packages, withoud need to write single line of code.

Open the new Project on Sql Server Integration Services.

Here is the steps to follow.

1. Create a new SSIS project in VS2005

2. Create a new data flow task - Double-click on the added task

3. Drag "xml source" from toolbox into data flow panel,Double Click on the XML

DataSource - a window opens select the xml file, and then generate XSD schema if not.

4. You could drag a "SQL Server detination" to the panel as data destination, connect XML source and SQL Server Destination, and use a new table.

Suppose if you getting Package Validation Error - Use the "data conversion" component in a data flow.
There may be the Datatype can be mismatched on Output columns. so at that time you can directly change the Datatype follow the below steps.

1. Right click the XML source->Show Advanced editor->Input and Output properties

2. Expand inputname->External Columns->Column name

3. On right panel, try to change the Datatype directly.

OR you could use "data conversion" component to convert the field that does not have the proper data type.

Finally if you check the DB it contains the New Table with the column Name and  its all Data available in the xml file.

Ref:

http://www.sql-server-performance.com/articles/dba/Loading_XML_data_into_SQL_Server_2008 _p1.aspx

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-dts/10784/Importing-XML-files-to-SQL -Database-using-SSIS