none
Dynamically creating columns of Excel to Sql table using SSIS

    Question

  • Hi all,

    I have loading the data from excel to Sql table on daily basis from one folder,some times users added the new(addtional) columns in the excel file so at that time iam geeting error.

    I need to identify new columns in each and every file and add the respective column to the table. and finally data to be loaded to sql. 

    Please provide me .net script for this.

    Colud you please any one suggest on this since this very urgent requirement.


    Arun



    • Edited by Arunagiri Saturday, May 04, 2013 4:22 AM
    Friday, May 03, 2013 4:38 AM

All replies

  • Hi Arun,

    SSIS doesn't support dynamic column mapping, what I would suggest you to do is create your own script component as source and achieve this. I'm sure it is not very straight forward. But worth gving a try. All the best.

    Refer this http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/71a1a7b6-3245-4298-86e4-227e63c67927


    Rajkumar

    Friday, May 03, 2013 5:30 AM
  • Hi arunagiri,

    I also face the same problem but there is no support for this in SSIS.

    I can give u Suggestion how i have solved this kind of problem using Script componet.

    Check all the columns comming in daily basis of course it will have certain number.

    Create a statging table for that where all the columns will be present .

    In Script component read the excel file and the column which are comming in excel file set it to the column name of table

    and colum which are not present set it to null according to data types of ur table(Use Case). i am giving code below which will help u how to write Script of ur own .

    ' Microsoft SQL Server Integration Services user script component

    ' This is your new script component in Microsoft Visual Basic .NET

    ' ScriptMain is the entrypoint class for script components

    Imports System

    Imports System.Data

    Imports System.Data.OleDb

    Imports System.IO

    Imports System.Windows.Forms

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

        Inherits UserComponent

        Public Overrides Sub CreateNewOutputRows()

            GetDataFromExcel()

       

        End Sub

        Public Function GetDataFromExcel() As DataSet

            Dim ds As New DataSet()

            Dim Vars As IDTSVariables90

            Dim filepath As String

            VariableDispenser.LockOneForWrite("User::filePath", Vars)

            filepath = Convert.ToString(Vars("User::filePath").Value)

            Vars.Unlock()

            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties= Excel 8.0")

            'Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\venkat\Desktop\ExcelFile\Test2.xls ;Extended Properties= Excel 8.0")

            Try

                cn.Open()

            Catch ex As OleDbException

                Console.WriteLine(ex.Message)

            Catch ex As Exception

                Console.WriteLine(ex.Message)

            End Try

            ' It Represents Excel data table Schema.'

            Dim dt As New System.Data.DataTable()

            dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then

                'For sheet_count As Integer = 0 To dt.Rows.Count - 1

                Try

                  

                    Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", cn)

                    da.Fill(ds)

                  

                    Dim row As DataRow

                    For Each row In ds.Tables(0).Rows

                    

                        'MessageBox.Show("Processing Detail {0}", strDetail)

                        With FileListBuffer

                            .AddRow()

                            Dim dc As DataColumn

                            For Each dc In ds.Tables(0).Columns

                                'MessageBox.Show("Header is " & dc.ColumnName.ToString())

                                Select Case dc.ColumnName.ToString()

                                    Case "Office"

                                        .Office = row("Office").ToString()

                                    Case "ClientCode"

                                        .ClientCode = row("ClientCode").ToString()

                                    Case "TaskID"

                                        .TaskID = row("TaskID").ToString()

                                    Case "SystemOrderNum"

                                        .SystemOrderNum = row("SystemOrderNum").ToString()

                                    Case "ActualDefPkgQty"

                                        .ActualDefPkgQty = CInt(CUInt(row("ActualDefPkgQty").ToString()))

                                    

                                End Select

                            Next

                           

                        End With

                    Next row

                Catch ex As DataException

                    MessageBox.Show(ex.Message)

                Catch ex As Exception

                    MessageBox.Show(ex.Message)

                End Try

            End If

            cn.Close()

            Return ds

        End Function

    End Class

    Friday, May 03, 2013 6:15 AM
  • Hi Arunagiri,

    Check here


    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by Tinkureddy Friday, May 03, 2013 5:14 PM
    • Unproposed as answer by Arunagiri Saturday, May 04, 2013 4:21 AM
    Friday, May 03, 2013 12:01 PM