Exporting Data from Multiple Workbooks to SQL using SSIS

1. Declare the following package level variables

Name	Scope	Data Type	Value
filename	Package	String	D:\Dileep\SSIS\1.xlsx
flagXL2007	Package	Boolean	True
lstSheetNames	Package	Object	System.Object
sheetName	Package	String	Sheet1$

 

• 
  

  Make sure that the value of value of
  the variable
  fileName is pointing to any existing file in a specific folder

• 
  

  And value of the variable
  sheetName
  should also point to any one of the sheets in the excel work with file name
  given above.

• 
  

  flagXL2007 is set to true if the
  excel file is of 2007 version else(excel 2003) it is set to false

• 
  

  
  lstSheetNames
  variable holds the all sheet names in the corresponding workbook.
  
  

2. Drag Foreach Loop Container to
   design and open Foreach Loop Editor

• 
  

  In Collection:

  • 
    

    Set the Enumerator to
    Foreach File Enumerator

  • 
    

    Set the folder location (Here it
    is  D:\Dileep\SSIS)

  • 
    

    Set Files to
     *.xls*
    to pick both .xls
    and .xlsx files
    

  • 
    

    
    
     
    Set the Retrieve file name to default –Fully Qualified

• 
  

  In Variable Mappings:

  • 
    

    Select User::fileName in the
    variable dropdown  and set
    Index to 0

3. Drag Script Task into
   Foreach Loop Container and open the Script Task
   Editor

• 
  

  
  
    In Script::

  • 
    

    Select
    User::fileName
    in ReadOnlyVariables

  • 
    

    Select User::flagXL2007,
    User::lstSheetNames
    in ReadWriteVariables

• 
  

  Edit the script

public
void Main()

{

// TODO: Add your code
here

string[]
listSheetNames = GetExcelSheetNames(Dts.Variables[0].Value.ToString());

Dts.Variables[2].Value = listSheetNames;

//MessageBox.Show(Dts.Variables[0].Value.ToString());

if
(Dts.Variables[0].Value.ToString().Contains(".xlsx"))

 {

Dts.Variables[1].Value =
true;

}

else

{

Dts.Variables[1].Value =
false;

}

Dts.TaskResult = (int)ScriptResults.Success;

}

public
string[] GetExcelSheetNames(string
excelFileName)

{

OleDbConnection
con = null;

DataTable
dt = null;

String
conStr =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" +
excelFileName + ";Extended
Properties=Excel 12.0;";

con = new
OleDbConnection(conStr);

con.Open();

dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);

con.Close();

if
(dt == null)

{

return
null;

}

String[]
excelSheetNames = new
String[dt.Rows.Count];

int
i = 0;

foreach
(DataRow row
in dt.Rows)

{

excelSheetNames[i] = row["TABLE_NAME"].ToString();

i++;

}

return
excelSheetNames;

}

 

4. Drag two Foreach loop container below the script component as shown in
   the figure

 

5. Connect the two foreach loop container with a connector each and set the
   evaluation operation to Expression instead of constraint as show in the
   figures below

Connector properties for jthe first foreach loop container

 

Connector properties for the second foreach loop container

 

6. These two foreach loop container are used to iterate over every sheet of
   the workbook. One for excel 2007  work book and the other for excel
   2003 workbook
7. 
   

   Edit the first foreach loop container

• 
  

  In Collection:

  • 
    

    Set the Enumerator to
    Foreach From Variable Enumerator

  • 
    

    Set the variable to
    User::lstSheetNames

• 
  

  In Variable Mappings:

  • 
    

    Select User::sheetName in the
    variable dropdown  and set
    Index to 0

8. Edit the second foreach loop container with the settings same as above.
9. Place a data flow task inside each foreach loop container
   
   • In DFT_XL2007 drag excel source and create a connection manager of
     excel version 2007
   • Set the data aceess mode to Table name or View name variable
   • Set the variable name to User::sheetName
   • Drag a oledb destination and connect it to Excel source.
10. Similary edit DFT_XL2003 with the settings above. Give the same oledb
    destination in the two data flow tasks.
11. Now you are ready to go. Just run the package.