Thursday, November 24, 2011

SSIS: From Excel to Sql server table with IDENTITY column

Recently I came across one of the question in a popular forum about inserting data from excel to SQL Table with Identity column values of SQL Table coming from Excel. This is very much possible that you have a SQL table with an Identity Column and you want to insert data from Excel file and point of interest is values for Identity column of SQL Table are coming from the excel file. Let us assume we have a SQL Table with following structure:
The ID column is an Identity column. The SQL nature is every time new row will be inserted in this table, the Identity column will populate automatically with the next incremented value. But in a scenario when the ID column values are coming from Excel File – What should be the behavior? Let us assume we have a excel file and we have data like this.
We want to store ID column values of Excel sheet in the SQL table “Excel_ID” and want to store it in ID column of SQL table. With SSIS this is pretty simple. All we have to do is create a Package and add a Data Flow Task into it. We need two connections here. One Excel connection to point to our excel file and second OLE DB Connection to point to our SQL Server and SQL table. Under Data Flow Tab we have following structure
In the OLE DB Destination component we need to check the Keep Identity checkbox.
This will ensure that the Identity check is disabled at the SQL Server table and whatever values are coming from Excel file is stored in the Identity column. After running the package we found that all the values from Excel Column ID have been successfully inserted in the SQL table.
The same thing can be achieved using Execute SQL Task with SSIS.
Labels:

Sunday, November 13, 2011

SSIS: Read and Export Excel data from nth Row

This post shows how we can read and export data rom excel starting from nth row.

In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 3 to Row 14 for Top 10 companies we can do this in SSIS.


To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components.



I have put a Data Viewer between Excel Source and Sort. Sort component has no use in my package I simply wanted to show the data in the Data Viewer. I setup my Excel source to read the data from excel file (shown in picture 1). After setting up the Excel Source I went to the Properties window and setup the OpenRowset property value to Sheet1$A3:B14. This is because I want to read the values of Top 10 Companies and their location.


After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.


In second attempt I want to read the Top 10 Companies of India. To do so I setup the OpenRowset property to Sheet1$A18:B27.


After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.




We can further export these data to any table or flat file we want to. This shows an example when we have a boundation to read the data from a excel file of a specific row. The same property can be setup programmatically using Script Component.

Related Article:
Labels: , , ,

Sunday, November 6, 2011

SSIS: How to pull Currency Rates from European Central Bank

This post shows how you can use European Central Bank daily currency feed to pull the Currency Rates in SSIS without using any web services.

Each country currency has different values. When you visit a particular country one of the important thing you want to know is what is the currency and what the currency rate is.

European Central Bank is one of many sources which publish the currency rates every day. The currency rates are published in Euro. If you visit their daily currency rates feed by navigating the following URL; you will find the currency rates in 33 different currencies.

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml




You can use the above European Central Bank daily feed in SSIS to pull the currency rates.

For the demonstration purpose I have create a table which has following three columns (Currency, Rate and LastUpdatedTime). This table stores the daily currency rates feed which is pulled from the above mentioned ECB URL.

To pull the currency rates from ECB daily I created one OLE DB connection in my SSIS project which points to the Database which has the above table. I created following task under the Control Flow tab.


The Execute SQL Task simply truncate all the rows from the SQL table


The Data Flow Task has following components


I created a package level variable to store the ECB daily feed URL.

The script component under Data Flow tab is a source. I have setup the ReadOnlyVariable values to User::ECBRL. I have added three output columns in inputs and outputs. The three output columns are Currency, Rates and LastUpdate.

I have following codes in the script component.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void PreExecute()
    {
        base.PreExecute();        
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        string strCurrency;
        string flRate;
        XmlDocument xm = new XmlDocument();
        xm.Load(Variables.ECBRL.ToString());

        XmlNodeList xnode = xm.GetElementsByTagName("Cube");
        
        foreach (XmlNode xmn in xnode)
        {
            XmlElement currencyElement = (XmlElement)xmn;
            if (currencyElement.HasAttributes)
            {                
                strCurrency = currencyElement.GetAttribute("currency").ToString();
                flRate =currencyElement.GetAttribute("rate");
                if (strCurrency.Length > 0)
                {
                    Output0Buffer.AddRow();
                    Output0Buffer.Currency = strCurrency;
                    Output0Buffer.Rate = flRate;
                    Output0Buffer.LastUpdate = DateTime.Now;
                }
            }

        }

        }


As you can see in the code we are pulling the Currency rates of each of the currency listed in the ECB feed using Syste.XML namespace.

The OLE DB Destination points to our table which is storing the Currency rates. I have mapped the Script Component output columns to the SQL table columns.

After setting up the Data Flow Task components I connected with the internet and run the package. It runs successfully and pulls the Currency rates values and updated them in the table.

This post shows one of the ways we can use to pull the Currency rates from one of the most trusted source i.e. European Central Bank. There are many ways to accomplish the same in SSIS.

I hope you enjoy reading the post. Thanks for the reading.

Related Article:
Labels: , ,
Subscribe to: Posts (Atom)

Popular Posts

Real Time Web Analytics