myADONETConnection = [SqlConnection][Dts. Kết nối[“DB_Connection”]. Có được kết nối [Dts. Giao dịch] dưới dạng SqlConnection];
// Đọ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áp
Nế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
Thông tin tệp nhật ký. Trong trường hợp Gói SSIS của bạn bị lỗi. Tệp nhật ký sẽ được tạo trong cùng thư mục nơi Tệp Excel của bạn sẽ được tạo. Nó sẽ có cùng ngày giờ với Tệp Excel của bạn.
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; } }
Bước 5.
Lưu tập lệnh trong Script Task Editor và đóng cửa sổ. Chạy Gói SSIS của bạn. Trong tác vụ tập lệnh, nó sẽ thực thi Thủ tục lưu trữ và chúng kết xuất kết quả trả về bởi thủ tục lưu sẵn vào tệp Excel.
Tôi đã thực thi Gói SSIS của mình vài lần và nó đã tạo ra các tệp excel như hình bên dưới
Cách tải kết quả Thủ tục được lưu trữ vào Tệp Excel một cách linh hoạt trong Gói SSIS bằng cách sử dụng Script Task -C# Scripting Language
Làm cách nào để xuất dữ liệu từ SQL sang Excel bằng SSIS?
Thực hiện theo các bước bên dưới để chỉ định các thuộc tính cần thiết để kết nối với phiên bản SQL Server. Mở ADO. NET Source và thêm một kết nối mới. Nhập thông tin máy chủ và cơ sở dữ liệu của bạn tại đây. Trong menu Chế độ truy cập dữ liệu, chọn "Bảng hoặc dạng xem" và chọn bảng hoặc dạng xem để xuất thành ExcelLàm cách nào để xuất dữ liệu SQL Server thành nhiều trang tính Excel bằng SSIS?
Giải pháp. .Bước 1. Tạo Gói SSIS mới và tạo BiếnCộtNameForGrouping. Cung cấp cột bạn muốn sử dụng để tạo dữ liệu trang tínhExcelFileName. Cung cấp tên tệp excel bạn muốn tạoĐường dẫn thư mục. Cung cấp Đường dẫn nơi bạn muốn tạo tệp excelLàm cách nào để tạo tệp Excel động trong gói SSIS?
Tạo tệp Excel động trong SSIS .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. .Bước 2. Tạo ADO. NET trong Gói SSIS để sử dụng trong Tác vụ tập lệnh. .Bước 3. Thêm Biến vào Tác vụ Tập lệnh để sử dụng từ Gói SSISChúng tôi có thể lưu giá trị tham số đầu ra của thủ tục được lưu trữ vào biến SSIS không?
Khi Tác vụ SQL Thực thi sẽ được thực thi, Quy trình được lưu trữ sẽ thực thi bằng cách sử dụng tham số đầu vào và giá trị trả về cho tham số đầu ra [PkgExecKey] và lưu vào Biến SSIS [ PkgExecKey] . Bạn có thể sử dụng biến này sau các tác vụ khác nhau trong Gói SSIS của mình. Giá trị này có thể được sử dụng để cập nhật dbo.