Nhập dữ liệu từ excel vào máy chủ sql bằng gói ssis

Giả sử chúng ta nhận được một hoặc nhiều tệp Excel trong thư mục nguồn. Tệp Excel có thể có nhiều sheet. Nhưng chúng tôi chỉ muốn tải một trang tính có tên "CustomerSheet" từ mỗi Tệp Excel. Bạn sẽ tạo Gói SSIS của mình như thế nào

Các tệp Excel mẫu có nhiều trang tính và chứa một Trang tính có Tên Trang tính khách hàng

Cách tải Bảng tính Excel có Tên cụ thể từ Tệp Excel sang Bảng SQL Server trong SSIS

Dung dịch

Chúng tôi sẽ sử dụng Tác vụ tập lệnh với Ngôn ngữ tập lệnh C# trong Gói SSIS của chúng tôi để chỉ tải một Trang tính có tên từ một hoặc nhiều tệp excel vào Bảng máy chủ SQL

Hãy tiếp tục và tạo Gói SSIS mới và đổi tên thành Tải dữ liệu từ tệp Excel cho trang tính cụ thể bằng cách sử dụng Tên thành Bảng máy chủ SQL

tạo dbo. Bảng khách hàng trong Cơ sở dữ liệu của bạn để chúng tôi có thể tải dữ liệu Bảng khách hàng vào đó

CREATE TABLE dbo.Customer [
    id INT
    ,name VARCHAR[50]
    ,dob DATE
    ]
GO

Bước 1. Tạo biến trong SSIS để làm cho gói SSIS của bạn trở nên năng động

Tạo các biến bên dưới trong Gói SSIS của bạn

Đường dẫn thư mục. Trong biến này, bạn sẽ lưu đường dẫn từ nơi bạn muốn đọc Tệp Excel

Tên lược đồ. Lược đồ của Bảng đích của bạn

TableName. Bảng mà bạn muốn tải dữ liệu

SheetNametoLoad. Cung cấp Tên trang tính bạn muốn tải từ mỗi Tệp Excel

Cách tải Trang tính Excel vào Bảng máy chủ SQL bằng cách sử dụng Tên trang tính Excel trong Gói SSIS- Tác vụ tập lệnh

Bước 2. Tạo ADO. NET trong Gói SSIS để sử dụng trong Tác vụ tập lệnh

Tạo ADO. NET Connection Manager để chúng ta có thể sử dụng trong Script Task để tải dữ liệu từ Excel Sheets vào SQL Server Table

Tạo ADO. NET trong Gói SSIS để sử dụng trong Tác vụ tập lệnh để tải trang tính Excel theo tên

Bước 3. Thêm Biến vào Tác vụ Tập lệnh để sử dụng từ Gói SSIS

Mang Tác vụ tập lệnh trên Ngăn luồng điều khiển trong Gói SSIS và mở bằng cách nhấp đúp vào Hộp kiểm ở phía trước biến để thêm vào Tác vụ tập lệnh

Thêm biến vào Tác vụ tập lệnh để sử dụng từ Gói SSIS cho Trang tính Excel Tải theo tên - Tác vụ tập lệnh C# Excel

Bước 4. Thêm Tập lệnh vào Trình chỉnh sửa tác vụ tập lệnh trong SSIS Để tải Trang tính Excel cụ thể vào Bảng máy chủ SQL

Nhấp vào nút Chỉnh sửa và nó sẽ mở Trình chỉnh sửa tác vụ tập lệnh

Trong Không gian tên #region, tôi đã thêm mã bên dưới

using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

Dưới public void Main[] {

Tôi đã thêm mã bên dưới.  

string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString[];
            string TableName = Dts.Variables["User::TableName"].Value.ToString[];
            string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString[];
            string SheetNameToLoad = Dts.Variables["User::SheetNametoLoad"].Value.ToString[];
            
            var directory = new DirectoryInfo[FolderPath];
            FileInfo[] files = directory.GetFiles[];

            //Declare and initilize variables
            string fileFullPath = "";


            SqlConnection myADONETConnection = new SqlConnection[];
            myADONETConnection = [SqlConnection][Dts.Connections["DBConn"].AcquireConnection[Dts.Transaction] as SqlConnection];

            //Get one Book[Excel file at a time]
            foreach [FileInfo file in files]
            {
                fileFullPath = FolderPath + "\\" + file.Name;

                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR = "YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                OleDbConnection cnn = new OleDbConnection[ConStr];

                //Get Sheet Name
                cnn.Open[];
                DataTable dtSheet = cnn.GetOleDbSchemaTable[OleDbSchemaGuid.Tables, null];
                string sheetname;
                sheetname = "";
                //Only read data from provided SheetNumber
                
                foreach [DataRow drSheet in dtSheet.Rows]
                {
                   
                        if [drSheet["TABLE_NAME"].ToString[].Contains["$"]]
                        {
                            sheetname = drSheet["TABLE_NAME"].ToString[];
                           // MessageBox.Show[sheetname];
                        
    //Load the Data if Sheet Name Matches
                            if[sheetname==SheetNameToLoad+"$"]
                            {

                          

                            //Load the DataTable with Sheet Data so we can get the column header
                            OleDbCommand oconn = new OleDbCommand["select top 1 * from [" + sheetname + "]", cnn];
                            OleDbDataAdapter adp = new OleDbDataAdapter[oconn];
                            DataTable dt = new DataTable[];
                            adp.Fill[dt];
                            cnn.Close[];

                            //Prepare Header columns list so we can run against Database to get matching columns for a table.
                            string ExcelHeaderColumn = "";
                            string SQLQueryToGetMatchingColumn = "";
                            for [int i = 0; i < dt.Columns.Count; i++]
                            {
                                if [i != dt.Columns.Count - 1]
                                    ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ",";
                                else
                                    ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'";
                            }

                            SQLQueryToGetMatchingColumn = "select STUFF[[Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                                TableName + "' and Table_SChema='" + SchemaName + "'" +
                                "and Column_Name in [" + @ExcelHeaderColumn + "] for xml path['']],1,1,''] AS ColumnList";

                            // MessageBox.Show[SQLQueryToGetMatchingColumn];
                            // MessageBox.Show[ExcelHeaderColumn];


                            //Get Matching Column List from SQL Server
                            string SQLColumnList = "";
                            SqlCommand cmd = myADONETConnection.CreateCommand[];
                            cmd.CommandText = SQLQueryToGetMatchingColumn;
                            SQLColumnList = [string]cmd.ExecuteScalar[];

                            // MessageBox.Show[" Matching Columns: " + SQLColumnList];


                            //Use Actual Matching Columns to get data from Excel Sheet
                            OleDbConnection cnn1 = new OleDbConnection[ConStr];
                            cnn1.Open[];
                            OleDbCommand oconn1 = new OleDbCommand["select " + SQLColumnList + " from [" + sheetname + "]", cnn1];
                            OleDbDataAdapter adp1 = new OleDbDataAdapter[oconn1];
                            DataTable dt1 = new DataTable[];
                            adp1.Fill[dt1];
                            cnn1.Close[];


                            //Load Data from DataTable to SQL Server Table.
                            using [SqlBulkCopy BC = new SqlBulkCopy[myADONETConnection]]
                            {
                                BC.DestinationTableName = SchemaName + "." + TableName;
                                foreach [var column in dt1.Columns]
                              BC.ColumnMappings.Add[column.ToString[], column.ToString[]];
                                BC.WriteToServer[dt1];
                            }

                        }
                    }
                   
                }
               
            }

Bước 5. Kiểm tra Gói SSIS của bạn để tải Trang tính Excel theo Tên trong Gói SSIS bằng cách sử dụng Tác vụ tập lệnh

Lưu tất cả các thay đổi trong Script Task Editor và thoát. Chạy Gói SSIS của bạn và nó sẽ có thể tải trang tính Excel từ mỗi tệp excel có tên bạn đã cung cấp trong biến FileNameToLoad

Trong trường hợp của tôi, tôi đã cung cấp Bảng khách hàng và nó đã tải dữ liệu của cả hai trang tính từ hai tệp excel như hình bên dưới

Cách tải Trang tính Excel theo tên từ một hoặc nhiều Trang tính Excel sang Bảng máy chủ SQL trong Gói SSIS bằng cách sử dụng Tác vụ tập lệnh bằng cách sử dụng Ngôn ngữ tập lệnh C#