locked
Entering one excel row data in database table and into two different row RRS feed

  • Question

  • Hello All,

    I'm validating an excel sheet in SSIS 2008 and the data I'm getting after doing all the validation is spread accross arond 14 columns. Now the probel is data that is present in first 10 columns need to be enetered in one table and the data that is present in last 4 column need to be inserted into different table. Also for the last 4 columns of validated data, the table is designed in such a way that first, first two column's data will be entered in first row and then the last two column will be enetered in second row and the cycle will run till the end of the file. I tried many couple of procedure etc but couldn't get success.

    Any help you could provide, would be a great help.

    Thursday, January 5, 2012 2:45 PM

Answers

  • Hi abhi_ashu,

    Shashi's solution seems good, and like Phaneendra said, you need load 11th and 12th columns into the destination table before loading 13th and 14th columns. If you do need this, please take the following steps as reference:

    1. Create a data flow task 1(DFT1) for loading 11th and 12th columns to destination table, put DFT1 in one sequence container(SC1).
    2. Create a data flow taks 2(DFT2) for loading 13th and 14th columns to destination table, put DFT2 in one sequence container(SC2)
    3. Put a Success precedence constraint from SC1 to SC2.

    And then, we could meake sure load 13th and 14th columns into the destination table after loading 11th and 12th columns.

    For more information about Sequence Container, please see: http://msdn.microsoft.com/en-us/library/ms139855.aspx

    Thanks,
    Eileen
    • Edited by Eileen Zhao Thursday, January 12, 2012 12:43 PM
    • Marked as answer by abhi_ashu Tuesday, January 17, 2012 9:39 AM
    Thursday, January 12, 2012 12:43 PM

All replies

  • Hi Abhi,

    Please let me know if it is mandatory to load the last 4 columns data to table in the same order. For example, can we load the 11th and 12th columns data into second table and then move on to loading 13th and 14th columns or should we follow the order of of loading 11th and 12th columns data of one row into the destination table immediately followed by inserting the 13th and 14th column as second row and move forward.

    Regards,

     


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Thursday, January 5, 2012 3:34 PM
  • If you have different sheet names, you can rename the excel sheets with one unique name by using C# (microsoft Interop) code.


    THis doesnt sound easy, so sheet1 gets renamed to S1 and the package will only read SELECT * FROM [S1$],

    -load the data in table ,

    -go to next sheet ,

    - but first rename S1 to SheetDone1

    - now go to next sheet , sheet2

    - rename to S1$

    .

    .

    .

    .

     

    you will have to keep track of the sheet names and what has been processed and you will have issues in SSIS Auditing if you have any, you wont be able to know what sheet was processed because you have renamed them

    loop through excel sheets is very easy

               If flagSourceMultiSheet = True Then
                    strConnectionString = Dts.Variables("uVar_SourceConnectionString").Value.ToString
                    oledbExcelConnection = New OleDbConnection(strConnectionString)
                    oledbExcelConnection.Open()
    
                    ExcelDataTable = oledbExcelConnection.GetSchema("Tables")
                    SheetCount = ExcelDataTable.Rows.Count
    
                    For Each ExcelSheet In ExcelDataTable.Rows
                        CurrentSheet = ExcelSheet.Item("TABLE_NAME").ToString
                        CurrentSheet = CurrentSheet.Replace("'", "")
    
                        If Right(CurrentSheet, 1) = "$" Then
                            LoopForNumberOfRealTables += 1
                            ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1)
                            ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet
                        End If
                    Next
                    oledbExcelConnection.Close()
                Else
                    ReDim Preserve ListOfExcelSheets(0)
                    ListOfExcelSheets(0) = Dts.Variables("uVar_SourceActiveSheetName").Value.ToString
                End If
    

     

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, January 5, 2012 9:26 PM
  • Hi Nik,

    Thanks for letting me know the code. It definitely helpful to me and the questioner. However, I am expecting some best option for the following scenario instead of SQL Command mode.

    "I'm validating an excel sheet in SSIS 2008 and the data I'm getting after doing all the validation is spread accross arond 14 columns. Now the probel is data that is present in first 10 columns need to be enetered in one table and the data that is present in last 4 column need to be inserted into different table. Also for the last 4 columns of validated data, the table is designed in such a way that first, first two column's data will be entered in first row and then the last two column will be enetered in second row and the cycle will run till the end of the file. I tried many couple of procedure etc but couldn't get success.

    Any help you could provide, would be a great help."


     

    Thursday, January 5, 2012 9:44 PM
  • Hi Phaneendra,

    Your this assumption " we follow the order of of loading 11th and 12th columns data of one row into the destination table immediately followed by inserting the 13th and 14th column as second row and move forward" is correct in my case.

    Regards,

    Friday, January 6, 2012 7:00 AM
  • Thanks Nik for the reply and code but I'm not sure whether it will be helpful in the given scenario.

    Regards,

    -Abhi

    Friday, January 6, 2012 7:01 AM
  • Hi,

    Please try like this and let me know if it works. I assumed that your second table contains 2 columns. If not then just remove Union All and Map directly:

     

    • Proposed as answer by Shashikanta Friday, January 6, 2012 7:55 AM
    Friday, January 6, 2012 7:39 AM
  • Hi Shashi,

    How would this address the requirement of loading 11th and 12th columns into the destination table followed by 13th and 14th and continue that way to load entire data of those last 4 columns in to the destination database? I think this is something which is tricky and making it little challenging.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Friday, January 6, 2012 8:17 AM
  • Hi abhi_ashu,

    Shashi's solution seems good, and like Phaneendra said, you need load 11th and 12th columns into the destination table before loading 13th and 14th columns. If you do need this, please take the following steps as reference:

    1. Create a data flow task 1(DFT1) for loading 11th and 12th columns to destination table, put DFT1 in one sequence container(SC1).
    2. Create a data flow taks 2(DFT2) for loading 13th and 14th columns to destination table, put DFT2 in one sequence container(SC2)
    3. Put a Success precedence constraint from SC1 to SC2.

    And then, we could meake sure load 13th and 14th columns into the destination table after loading 11th and 12th columns.

    For more information about Sequence Container, please see: http://msdn.microsoft.com/en-us/library/ms139855.aspx

    Thanks,
    Eileen
    • Edited by Eileen Zhao Thursday, January 12, 2012 12:43 PM
    • Marked as answer by abhi_ashu Tuesday, January 17, 2012 9:39 AM
    Thursday, January 12, 2012 12:43 PM