Làm cách nào để xuất dữ liệu từ thủ tục được lưu trữ sang Excel bằng SSIS?
|
Tham số Đầu ra này cho phép tác vụ trên màn hình của bạn sử dụng dữ liệu Excel được xuất trong tác vụ máy chủ. Cuối cùng, thêm một Chỉ định vào quy trình của bạn và Trong bài viết trước, Học SQL. SQL Server Pivot Tables, chúng ta đã thảo luận về cách tạo báo cáo bằng cách sử dụng truy vấn bảng PIVOT Dưới đây là một ví dụ đơn giản về cách giải quyết vấn đề này bằng cách sử dụng dữ liệu bán hàng. Bước 2 - Tạo kết nối đến cơ sở dữ liệu máy chủ SQL của bạn. Trước khi tiếp tục, bạn cần đảm bảo rằng bạn đã tạo kết nối tới cơ sở dữ liệu SQL Server của mình. Để thực hiện việc này, trước tiên hãy nhấp chuột phải vào phần 'Trình quản lý kết nối' của Solution Explorer. Nhấp chuột phải để tạo trình quản lý kết nối mới. Cách xuất dữ liệu PostgreSQL sang tệp CSV hoặc Excel. Sửa đổi lần cuối. Tháng Tám 09, 2021. PostgreSQL có một số lệnh hay để giúp bạn xuất dữ liệu sang định dạng Giá trị được phân tách bằng dấu phẩy (CSV), sau đó có thể mở định dạng này trong Excel hoặc trình soạn thảo văn bản yêu thích của bạn. Để sao chép dữ liệu ra ngoài, trước tiên hãy kết nối với PostgreSQL của bạn thông qua dòng lệnh hoặc công cụ khác như PGAdmin. Dữ liệu được lưu trữ trong bảng cơ sở dữ liệu sẽ được chuyển đổi thành văn bản JSON trong thủ tục được lưu trữ và được trả về máy khách C# thông qua tham số đầu ra. Bảng máy chủ SQL. Hãy bắt đầu với bảng SQL Server, bảng này sẽ chứa một hàng dữ liệu bộ đếm hiệu suất mẫu. Cách giải quyết –. Tạo Sổ làm việc Excel sẽ là mẫu để xuất dữ liệu. Nhập tên cột [id], [url] và [tên thân thiện] cho cột A, B và C tương ứng. Đặt công thức Excel sau vào cột D. -. Kéo ô công thức này đến số hàng phù hợp với khối lượng dữ liệu dự kiến của bạn. Nhấp đúp vào Nguồn OLEDB và chọn "Chế độ truy cập dữ liệu" làm "Lệnh Sql". Đặt Văn bản SQLCommand là "EXEC sp_whatever". Cuối cùng, nhấp vào tab "Cột" một lần để nhận danh sách các cột được hiển thị bởi proc được lưu trữ của bạn. Quá trình thiết lập bộ điều hợp nguồn của bạn đã hoàn tất
myADONETConnection = (SqlConnection)(Dts. Kết nối[“DB_Connection”]. Có được kết nối (Dts. Giao dịch) dưới dạng SqlConnection); Show // Đọc danh sách các Bảng có Lược đồ từ Cơ sở dữ liệu truy vấn chuỗi = “CHỌN Schema_name(schema_id) AS SchemaName,name AS TableName TỪ sys. bảng WHERE is_ms_shipped = 0”; //Hộp tin nhắn. Hiển thị (truy vấn. ToString()); SqlCommand cmd = new SqlCommand(truy vấn, myADONETConnection); // myADONETConnection. Mở ra(); DataTable dt = new DataTable(); đt. Tải (cmd. ExecuteReader()); myADONETConnection. Đóng(); //Lặp qua datatable(dt) có lược đồ và tên bảng foreach (DataRow dt_row trong dt. hàng) { chuỗi SchemaName = “”; chuỗi TableName = “”; đối tượng [] mảng = dt_row. ItemArray; SchemaName = mảng [0]. ToString(); Tên bảng = mảng [1]. ToString(); chuỗi ExcelFileName = “”; ExcelFileName = SchemaName + “_” + TableName + “_” + datetime; OleDbConnection Excel_OLE_Con = new OleDbConnection(); OleDbCommand Excel_OLE_Cmd = new OleDbCommand(); //Xây dựng ConnectionString cho Excel string connstring = “Nhà cung cấp=Microsoft. ÁT CHỦ. OLEDB. 12. 0;” + “;” . 0 Xml;HDR=YES;\””; // Tải dữ liệu vào DataTable từ SQL ServerTable chuỗi queryString = “CHỌN * từ “ + SchemaName + “. ” + TableName; Bộ điều hợp SqlDataAdapter = new SqlDataAdapter(queryString, myADONETConnection); Bộ dữ liệu ds = Bộ dữ liệu mới(); bộ chuyển đổi. Điền(ds); //Nhận cột tiêu đề chuỗi TableColumns = “”; // Lấy Danh sách Cột từ Data Table để có thể tạo Sheet Excel có Header foreach (bảng DataTable trong ds. Những cái bàn) { foreach (Cột DataColumn trong bảng. Cột) { TableColumns += cột + “],[“; } } // Thay thế dấu phẩy bên phải nhất từ Columnlist TableColumns = (“[“ + TableColumns. Replace(“,”, “Văn bản,”). TrimEnd(‘,’)); TableColumns = TableColumns. Xóa (Cột bảng. Chiều dài — 2); //Hộp tin nhắn. Hiển thị (Cột bảng); //Sử dụng kết nối OLE DB và tạo trang tính Excel Excel_OLE_Con. ConnectionString = connstring; Excel_OLE_Con. Mở ra(); Excel_OLE_Cmd. Kết nối = Excel_OLE_Con; Excel_OLE_Cmd. CommandText = “Tạo bảng [“ + SchemaName + “_” + TableName + “] (“ + TableColumns + “)”; Excel_OLE_Cmd. ExecuteNonQuery(); // Ghi dữ liệu vào Excel Sheet từ DataTable động foreach (bảng DataTable trong ds. Những cái bàn) { Chuỗi sqlCommand Chèn = “”; Chuỗi sqlCommandValue = “”; foreach (DataColumn dataColumn trong bảng. Cột) { sqlCommandValue += dataColumn + “],[“; } sqlCommandValue = “[“ + sqlCommandValue. TrimEnd(‘,’); sqlCommandValue = sqlCommandValue. Xóa (sqlCommandValue. Chiều dài — 2); sqlCommandInsert = “CHÈN vào [“ + SchemaName + “_” + TableName + “] (“ + sqlCommandValue + “) VALUES(“; Bạn đang làm việc với tư cách là Nhà phát triển ETL / Nhà phát triển SSIS và bạn cần tạo Gói SSIS sẽ thực thi Quy trình được lưu trữ từ cơ sở dữ liệu SQL Server và tạo tệp excel cho dữ liệu được trả về bởi Quy trình được lưu trữ. Giả sử rằng trong kịch bản, Thủ tục lưu trữ không chấp nhận bất kỳ tham số nào. Các thủ tục được lưu trữ thường được tạo để chạy một loạt các truy vấn để tạo kết quả cuối cùng và tải nó vào tệp Excel. Quy trình được lưu trữ của bạn có thể đang sử dụng trục và nó có thể trả về các cột khác nhau trên mỗi lần thực hiện. Gói SSIS của chúng tôi sẽ có thể xử lý tình huống này. Nó phải luôn tạo một tệp Excel bất kỳ cột nào được trả về bởi Thủ tục được lưu trữ Tệp excel phải được tạo với Datetime trên mỗi lần thực hiện. Giải phápNếu chúng tôi cố gắng tạo Gói SSIS này với Đích Excel, sẽ rất khó xử lý các tình huống khi số lượng cột được trả về bởi Thủ tục được lưu trữ thay đổi. chúng tôi thường phải chỉnh sửa Gói SSIS để xử lý tình huống này Chúng tôi sẽ sử dụng tác vụ Script vì vậy chúng tôi không phải lo lắng nếu định nghĩa Thủ tục được lưu trữ thay đổi. Miễn là nó sẽ trả về dữ liệu cho chúng ta, chúng ta sẽ đổ vào tệp Excel mới trên mỗi lần thực hiện đây là Thủ tục lưu trữ mẫu của tôi. Create Procedure Dbo.usp_TotalSale AS BEGIN Select * From [dbo].[TotalSale] END
Bước 1. Tạo các biến để làm cho Gói SSIS của bạn trở nên năng động Tạo Gói SSIS của bạn trong SSDT (Công cụ dữ liệu máy chủ SQL). Khi Gói SSIS được tạo. Tạo các biến bên dưới. ExcelFileName. Cung cấp tên của Tệp Excel mà bạn muốn tạo Đường dẫn thư mục. Thao tác này sẽ lưu vào nơi bạn muốn tạo Tệp Excel Tên trang tính. Cung cấp Tên trang tính bạn muốn có trong Tệp Excel của mình StoredProcedureName. Cung cấp Tên thủ tục được lưu trữ với Lược đồ mà bạn muốn thực thi và kết xuất dữ liệu vào Tệp Excel mới được tạo Tạo các biến trong gói SSIS để tạo tệp Excel động từ thủ tục được lưu trữ trong 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 để thực thi Quy trình được lưu trữ và Kết xuất dữ liệu vào Tệp Excel trên mỗi lần thực thi 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 Sử dụng các biến trong Tác vụ tập lệnh trong Gói SSIS để tạo Excel động từ Quy trình được lưu trữ 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 Gói SSIS Để tạo tệp Excel cho kết quả thủ tục được lưu trữ 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 datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { //Declare Variables string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString(); string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString(); string StoredProcedureName = Dts.Variables["User::StoredProcedureName"].Value.ToString(); string SheetName = Dts.Variables["User::SheetName"].Value.ToString(); ExcelFileName = ExcelFileName + "_" + datetime; OleDbConnection Excel_OLE_Con = new OleDbConnection(); OleDbCommand Excel_OLE_Cmd = new OleDbCommand(); //Construct ConnectionString for Excel string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""; //drop Excel file if exists File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx"); //USE ADO.NET Connection from SSIS Package to get data from table SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection); //Load Data into DataTable from SQL ServerTable // Assumes that connection is a valid SqlConnection object. string queryString = "EXEC " + StoredProcedureName; SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection); DataSet ds = new DataSet(); adapter.Fill(ds); //Get Header Columns string TableColumns = ""; // Get the Column List from Data Table so can create Excel Sheet with Header foreach (DataTable table in ds.Tables) { foreach (DataColumn column in table.Columns) { TableColumns += column + "],["; } } // Replace most right comma from Columnlist TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(',')); TableColumns = TableColumns.Remove(TableColumns.Length - 2); //MessageBox.Show(TableColumns); //Use OLE DB Connection and Create Excel Sheet Excel_OLE_Con.ConnectionString = connstring; Excel_OLE_Con.Open(); Excel_OLE_Cmd.Connection = Excel_OLE_Con; Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")"; Excel_OLE_Cmd.ExecuteNonQuery(); //Write Data to Excel Sheet from DataTable dynamically foreach (DataTable table in ds.Tables) { String sqlCommandInsert = ""; String sqlCommandValue = ""; foreach (DataColumn dataColumn in table.Columns) { sqlCommandValue += dataColumn + "],["; } sqlCommandValue = "[" + sqlCommandValue.TrimEnd(','); sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2); sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue+ ") VALUES("; int columnCount = table.Columns.Count; foreach (DataRow row in table.Rows) { string columnvalues = ""; for (int i = 0; i < columnCount; i++) { int index = table.Rows.IndexOf(row); columnvalues += "'" + table.Rows[index].ItemArray[i] + "',"; } columnvalues = columnvalues.TrimEnd(','); var command = sqlCommandInsert + columnvalues + ")"; Excel_OLE_Cmd.CommandText = command; Excel_OLE_Cmd.ExecuteNonQuery(); } } Excel_OLE_Con.Close(); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" + Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log")) { sw.WriteLine(exception.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } } |
