none
To Check only the 2nd row of Excel

    Question

  • In SSIS im using Excel source file to load data directly to Table.

    Here, the first row is the header row, and from the second row the data are present.

    Before loading we need to check only the second line if it is NOT EMPTY ROW.

    How can we check the excel only for the second row if it is empty row or not ?

    Thanks !


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    • Changed type Radhai Tuesday, January 08, 2013 10:39 AM its a question
    Tuesday, January 08, 2013 10:38 AM

Answers

  • In
    1 - Excel Source -> Variables -> In Data Access Mode select "Table name or view name variable"
    2- In the variable name select a variable that you had made before "MyVar"
    3- Go To variable select "MyVar" and type "TabName$A12:H125" your package -> data Flow

    basically what you are doing is that insted of setting the sheet name directly in the DFT-->ExcelSource-->VariableName you are setting a SSIS variable (lets call it uVar_SourceActveSheetName) the variable "uVar_SourceActveSheetName" will be in DFT-->ExcelSource-->VariableName and the value of the variable uVar_SourceActveSheetName will be "Sheet1$A2:H500" what ever range that you are looking for.

    good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 6:43 AM
    Tuesday, January 08, 2013 6:12 PM

All replies

  • Radhai,

    In the excel connection manager we can specify first row is column name. (Connection manger->First rows has column names)

    If you want to skip more than one rows then see below approach:

    There is property in excel source advance editor property OpenRowset (Right click excel source->Advanced editor)

    Regards,Eshwar


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, January 08, 2013 12:06 PM
  • Radhai,

    In the excel connection manager we can specify first row is column name. (Connection manger->First rows has column names)

    If you want to skip more than one rows then see below approach:

    There is property in excel source advance editor property OpenRowset (Right click excel source->Advanced editor)
    Regards,Eshwar


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    No Eshwar, actually i dont want to ignore any rows..

    But as a validation i need to check if the second row (data's first row) shouldn't be empty.

    it doesn't means that the second is always empty... it'll have values. But in case if the row is empty then we need to highlight it.

    Is there a way to check the Excel on particular row for all columns is null ? FOR A PARTICULAR ROW Only. in our case its second row..

    i dont know if any one have already worked on such requierment..

     please help me .


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    Tuesday, January 08, 2013 12:35 PM
  • Radhai,

    In the excel connection manager we can specify first row is column name. (Connection manger->First rows has column names)

    If you want to skip more than one rows then see below approach:

    There is property in excel source advance editor property OpenRowset (Right click excel source->Advanced editor)
    Regards,Eshwar


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    No Eshwar, actually i dont want to ignore any rows..

    But as a validation i need to check if the second row (data's first row) shouldn't be empty.

    it doesn't means that the second is always empty... it'll have values. But in case if the row is empty then we need to highlight it.

    Is there a way to check the Excel on particular row for all columns is null ? FOR A PARTICULAR ROW Only. in our case its second row..

    i dont know if any one have already worked on such requierment..

     please help me .


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    I don't know SSIS can do that. We did it by C#.
    Tuesday, January 08, 2013 2:03 PM
  • what i would do is

    first chesk if the second row exist and then use a DFT to process the excel sheet

    good to know is ..... http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa/


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Tuesday, January 08, 2013 2:42 PM
  • what i would do is

    first chesk if the second row exist and then use a DFT to process the excel sheet

    good to know is ..... http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa/


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Hi, Nik

    I check your old post, can you send me your package ?  my email:      leading120@gmail.com 

    I am quite confused after the first step.  Thank you. 

    In your package -> data Flow
    1 - Excel Source -> Variables -> In Data Access Mode select "Table name or view name variable"
    2- In the variable name select a variable that you had made before "MyVar"
    3- Go To variable select "MyVar" and type "TabName$A12:H125"

    Tuesday, January 08, 2013 3:44 PM
  • sorry i cant find the package, i am still going to look

    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Tuesday, January 08, 2013 5:51 PM
  • In
    1 - Excel Source -> Variables -> In Data Access Mode select "Table name or view name variable"
    2- In the variable name select a variable that you had made before "MyVar"
    3- Go To variable select "MyVar" and type "TabName$A12:H125" your package -> data Flow

    basically what you are doing is that insted of setting the sheet name directly in the DFT-->ExcelSource-->VariableName you are setting a SSIS variable (lets call it uVar_SourceActveSheetName) the variable "uVar_SourceActveSheetName" will be in DFT-->ExcelSource-->VariableName and the value of the variable uVar_SourceActveSheetName will be "Sheet1$A2:H500" what ever range that you are looking for.

    good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Marked as answer by Eileen Zhao Monday, January 14, 2013 6:43 AM
    Tuesday, January 08, 2013 6:12 PM