У меня есть два файла Excel, и я хочу импортировать эти файлы во временную таблицу SQL.

Первый файл Excel:

  T1      T2      T3     T4  Total
 1,472   1,364   1,422  –   4,258 
-152.6  -152.6  -152.6  –   
 1,958   1,939   1,942  –   
-122.6  -123.7  -122.2  – 
  

Второй файл Excel:

  T1       T2     T3     T4  T5       Total
 1,472   1,364   1,422  –   12.2     4,258 
-152.6  -152.6  -152.6  –   1000.12
 1,958   1,939   1,942  –   50.23
-122.6  -123.7  -122.2  –   185.25
  

Есть ли какой-либо способ в SSIS идентифицировать файлы на основе количества столбцов? Мне нужно идентифицировать отчет на основе номера столбца.

Объекты от Microsoft.Офис.Взаимодействие.Пространство имен Excel может быть использовано в задаче скрипта C # для выполнения этого следующим образом. В этом примере имя файла и количество столбцов выводятся в объектную переменную SSIS ( User::SSISObjectVariable" ), которая может использоваться для применения дополнительной логики и обработки в пакете, такой как сохранение в таблице базы данных или иным образом. Полный путь к файлу — это первый столбец в переменной объекта, а количество столбцов — второй. Также обязательно добавьте ссылку на Microsoft.Пространство имен CSharp в скрипте. Переменную объекта необходимо будет включить в ReadWriteVariables поле задачи скрипта, и если исходная папка хранится в переменной (как сделано ниже), то добавьте эту переменную в ReadOnlyVariables поле.

 using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using System.Collections.Generic;

    List<string> excelFileList = new List<string>();
//get source directory
string filePath = Dts.Variables["User::FilePathVariable"].Value.ToString();
DirectoryInfo di = new DirectoryInfo(filePath);

System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("FilePath", typeof(System.String));
dt.Columns.Add("ColumnCount", typeof(System.Int32));

foreach (FileInfo fi in di.EnumerateFiles())
{
    //optional- check file extension and prefix
    if (fi.Extension == ".xls" amp;amp; fi.Name.StartsWith("Prefix"))
    {
        //get full file path
        excelFileList.Add(fi.FullName);
    }
}

foreach (string excelFile in excelFileList)
{

 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); ;
 Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(excelFile);
 Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];

 int columnCount;

//get number of columns
 columnCount = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
  System.Reflection.Missing.Value, System.Reflection.Missing.Value,
  Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
  false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;

//build data row to hold file path and column count
 DataRow dr = dt.NewRow();
 dr["FilePath"] = excelFile;
 dr["ColumnCount"] = columnCount;

 dt.Rows.Add(dr);

 xlApp.Workbooks.Close();
 xlApp.Quit();

 xlWorkbook = null;
 xlApp = null;
}

GC.Collect();
GC.WaitForPendingFinalizers();

//populate object variable
Dts.Variables["User::SSISObjectVariable"].Value = dt;
  

Если вам нужно импортировать excel с разными схемами, у вас есть два подхода:

(1) Подход SSIS: задача сценария 2 задачи потока данных

В случае, если у вас есть только две структуры, вы можете выполнить следующие действия:

1. Добавьте переменную типа System.Int32 example: @[User::ColumnsCount]
2. Добавьте переменную типа System.String для хранения пути к файлу пример: @[User::FilePath]
3. Добавьте задачу сценария с помощью и выберите @[User::FilePath] как переменную только для чтения и @[User::ColumnsCount] как переменную ReadWrite

4. Внутри скриптовой задачи напишите аналогичный скрипт:

    string FilePath = Dts.Variables["User::FilePath"].Value.toString();
   string ExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
                           "Data Source='"   FilePath   
                           "';Extended Properties="Excel 12.0;HDR=YES;"";
   
   using (OleDbConnection OleDBCon = new OleDbConnection(ExcelConnectionString))
           {
           if (OleDBCon.State != ConnectionState.Open)
               OleDBCon.Open();
   
               using (OleDbCommand cmd = new OleDbCommand(strcommand, OleDBCon))
               {
                   DataTable dtTable = new DataTable("Table1");
   
   
                   cmd.CommandType = CommandType.Text;
                   //replace Sheet1$ with the sheet name if it is different
                   cmd.CommandText = "SELECT * FROM Sheet1$"
                   using (OleDbDataAdapter daGetDataFromSheet = new OleDbDataAdapter(cmd))
                   {
                       daGetDataFromSheet.FillSchema(dtTable, SchemaType.Source);
                       Dts.Variables["User::ColumnsCount"].Value = dt.Columns.Count;
                   }
   
               }
   
           }
     

5. Добавьте две задачи потока данных, включенные для каждой структуры Excel

6. Свяжите задачу сценария с каждой из этих задач потока данных
7. Щелкните по каждому ограничению приоритета (связь между задачами) и измените тип предшествования на Выражение и ограничение и добавьте соответствующее выражение для каждого случая:

5 столбцов:

 @[User::ColumnsCount] == 5
  

6 столбцов:

 @[User::ColumnsCount] == 6
  

6. Установите для Delay Validation свойства значение True для обеих задач потока данных

TL DR: В случае, если у вас есть только две структуры, вы можете добавить две задачи потока данных (по одной для каждой структуры), затем вы можете использовать задачу сценария для определения количества столбцов и выполнения соответствующей задачи потока данных на основе количества столбцов (используя выражения с ограничениями приоритета).

(2) Подход C #: библиотека классов SchemaMapper

Недавно я запустил новый проект на Github, который представляет собой библиотеку классов, разработанную с использованием C #. Вы можете использовать его для импорта табличных данных из Excel, word, powerpoint, text, csv, html, json и xml в таблицу SQL server с другим определением схемы, используя подход сопоставления схемы. проверьте это на:

• SchemaMapper: библиотека классов сопоставления схем C #

Пошаговое руководство можно найти на этой вики-странице:

• Импортируйте данные из нескольких файлов в одну таблицу SQL пошаговое руководство