Dalam proyek terbaru kami perlu membuat paket SSIS untuk mengekspor data dari tabel SQL Server ke file CSV. Salah satu persyaratannya adalah menambahkan tanggal secara dinamis ke nama file setiap kali file dibuat. Posting ini akan membahas cara membuat nama file dinamis dengan menyertakan tanggal (format YYYYMMDD) dalam nama file Show
Anda seharusnya sudah membuat file keluaran di manajer koneksi. Jika Anda belum melakukannya—selesaikan dulu Cara membuat nama file dinamis Saat membuat nama file dinamis, ekspresi untuk CSV harus diperbarui. Klik kanan CSV di Connection Manager dan klik Properties. Ini akan membuka bagian di sisi kanan yang berisi baris ekspresi
Klik kanan CSV di Connection Manager dan klik Properties
Mengklik elips akan membuka bagian tempat Anda dapat membuat koneksi properti baru
Pernyataan di bawah ini dapat disalin dan ditempelkan ke dalam kotak ekspresi untuk membuat jalur
“\\\\ Servername\\SHARE\\FileName _”+(DT_STR,4,1252)DATEPART( “yyyy” . csv”
Perhatikan bahwa bagian yang disorot perlu diperbarui ke lingkungan Anda—di mana Anda meletakkan file yang dibuat. Garis miring ke depan ekstra di jalur file digunakan untuk menghindari garis miring ke depan lainnya untuk jalur file. Menambahkan satu garis miring di awal setiap garis miring (termasuk masing-masing untuk dua garis miring di awal jalur file) akan memastikan bahwa string jalur file Anda diinterpretasikan dengan benar. Hasilnya akan menempatkan nama file “FileName_YYYYMMDD” di dalam folder bernama SHARE Anda juga dapat memindahkan bagian ekspresi jika Anda menginginkan format yang berbeda Ada kalanya Anda memerlukan tanggal ekspor atas nama file Excel yang baru dibuat untuk validasi atau pelacakan versi Memuat file Excel dengan skema yang sama tetapi nama file atau nama sheet berbeda merupakan tantangan bagi banyak pengembang SSIS. Microsoft tidak memiliki komponen pemuatan Excel Dinamis apa pun yang mencari file Excel menggunakan pola dan mengidentifikasi lembar berisi data dan memuatnyaArtikel ini membantu Anda menghadapi skenario seperti itu. Paket yang dijelaskan di sini dapat mencari file Excel menggunakan pola dan memuatnya tanpa campur tangan pengguna. Sheet berisi data yang tidak dimulai dari baris pertama atau kolom pertama. Fitur
Membangun paket
4. Menyiapkan variabel paket Extraction_Statement
Tulis Kode Otomasi1. Cari file excel menggunakan pola yang ditentukan dalam variabel DataFile_Search_PatternPola ini dipisahkan dengan ekstensi menggunakan format titik koma2. Menghitung lembar Excel untuk file yang diperoleh dengan Pencarian
Menggunakan KodeCara kerjanya (Konsep)
Sekarang mari kita menganalisis konsep (perspektif kode) seperti di bawah ini. Metode berikut mencari file di jalur yang disediakan. Menggunakan pola pencocokan sebagai parameter private List<String> GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = this.GetConnectionString(excelFile); // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } List<String> excelSheets = new List<string>(); // Add the sheet name to the collection. foreach (DataRow row in dt.Rows) { string WorkSheet = row["TABLE_NAME"].ToString(); WorkSheet = WorkSheet.StartsWith("'") ? "[" + WorkSheet.Substring(1) : WorkSheet; WorkSheet = WorkSheet.StartsWith("[") ? WorkSheet : "[" + WorkSheet; WorkSheet = WorkSheet.EndsWith("'") ? WorkSheet.Substring(0, WorkSheet.Length - 1) + "]" : WorkSheet; WorkSheet = WorkSheet.Substring(0, WorkSheet.Length - 1).EndsWith("$") ? WorkSheet : WorkSheet.Substring(0, WorkSheet.Length - 1) + "$]"; if (!excelSheets.Exists(delegate(string k) { return (k.ToLower() == WorkSheet.ToLower() || k.ToLower() + "$" == WorkSheet.ToLower()); })) excelSheets.Add(WorkSheet); } return excelSheets; } catch (Exception ex) { return null; } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }_0 dan menggunakan parameter private List<String> GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = this.GetConnectionString(excelFile); // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } List<String> excelSheets = new List<string>(); // Add the sheet name to the collection. foreach (DataRow row in dt.Rows) { string WorkSheet = row["TABLE_NAME"].ToString(); WorkSheet = WorkSheet.StartsWith("'") ? "[" + WorkSheet.Substring(1) : WorkSheet; WorkSheet = WorkSheet.StartsWith("[") ? WorkSheet : "[" + WorkSheet; WorkSheet = WorkSheet.EndsWith("'") ? WorkSheet.Substring(0, WorkSheet.Length - 1) + "]" : WorkSheet; WorkSheet = WorkSheet.Substring(0, WorkSheet.Length - 1).EndsWith("$") ? WorkSheet : WorkSheet.Substring(0, WorkSheet.Length - 1) + "$]"; if (!excelSheets.Exists(delegate(string k) { return (k.ToLower() == WorkSheet.ToLower() || k.ToLower() + "$" == WorkSheet.ToLower()); })) excelSheets.Add(WorkSheet); } return excelSheets; } catch (Exception ex) { return null; } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }1 sebagai filter C# public string SearchFile(string path, string extension, string searchstring) { DirectoryInfo di = null; if (Directory.Exists(path)) { di = new DirectoryInfo(path); } else return "Directory Does not Exist"; string newestFile; IEnumerable<System.IO.FileInfo> fileList = di.GetFiles("*" + searchstring.ToLower() + "*"); //Create the query IEnumerable<System.IO.FileInfo> fileQuery = from file in fileList where (extension.ToLower().Contains(file.Extension.ToLower())) orderby file.LastWriteTime select file; try { var FileSearchedResult = (from file in fileQuery orderby file.LastWriteTime select new { file.FullName, file.Name, file.CreationTime }).Last(); newestFile = FileSearchedResult.FullName; FileSearchedResult = null; fileList = null; di = null; return newestFile; } catch { fileList = null; di = null; return null; } } Metode GetExcleSheetNames berikut menghitung semua sheet yang tersedia di private List<String> GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = this.GetConnectionString(excelFile); // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } List<String> excelSheets = new List<string>(); // Add the sheet name to the collection. foreach (DataRow row in dt.Rows) { string WorkSheet = row["TABLE_NAME"].ToString(); WorkSheet = WorkSheet.StartsWith("'") ? "[" + WorkSheet.Substring(1) : WorkSheet; WorkSheet = WorkSheet.StartsWith("[") ? WorkSheet : "[" + WorkSheet; WorkSheet = WorkSheet.EndsWith("'") ? WorkSheet.Substring(0, WorkSheet.Length - 1) + "]" : WorkSheet; WorkSheet = WorkSheet.Substring(0, WorkSheet.Length - 1).EndsWith("$") ? WorkSheet : WorkSheet.Substring(0, WorkSheet.Length - 1) + "$]"; if (!excelSheets.Exists(delegate(string k) { return (k.ToLower() == WorkSheet.ToLower() || k.ToLower() + "$" == WorkSheet.ToLower()); })) excelSheets.Add(WorkSheet); } return excelSheets; } catch (Exception ex) { return null; } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }2 yang disediakan sebagai parameter. Ini menggunakan driver OLEDDB untuk menghubungkan file Excel dan membaca Skema menggunakan GetOleDbSchemaTable. C# private List<String> GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = this.GetConnectionString(excelFile); // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } List<String> excelSheets = new List<string>(); // Add the sheet name to the collection. foreach (DataRow row in dt.Rows) { string WorkSheet = row["TABLE_NAME"].ToString(); WorkSheet = WorkSheet.StartsWith("'") ? "[" + WorkSheet.Substring(1) : WorkSheet; WorkSheet = WorkSheet.StartsWith("[") ? WorkSheet : "[" + WorkSheet; WorkSheet = WorkSheet.EndsWith("'") ? WorkSheet.Substring(0, WorkSheet.Length - 1) + "]" : WorkSheet; WorkSheet = WorkSheet.Substring(0, WorkSheet.Length - 1).EndsWith("$") ? WorkSheet : WorkSheet.Substring(0, WorkSheet.Length - 1) + "$]"; if (!excelSheets.Exists(delegate(string k) { return (k.ToLower() == WorkSheet.ToLower() || k.ToLower() + "$" == WorkSheet.ToLower()); })) excelSheets.Add(WorkSheet); } return excelSheets; } catch (Exception ex) { return null; } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } } Metode berikut ScanWorkSheet membangun DataSet untuk 1000 baris pertama dari private List<String> GetExcelSheetNames(string excelFile) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { // Connection String. Change the excel file to the file you // will search. String connString = this.GetConnectionString(excelFile); // Create connection object by using the preceding connection string. objConn = new OleDbConnection(connString); // Open connection with the database. objConn.Open(); // Get the data table containg the schema guid. dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } List<String> excelSheets = new List<string>(); // Add the sheet name to the collection. foreach (DataRow row in dt.Rows) { string WorkSheet = row["TABLE_NAME"].ToString(); WorkSheet = WorkSheet.StartsWith("'") ? "[" + WorkSheet.Substring(1) : WorkSheet; WorkSheet = WorkSheet.StartsWith("[") ? WorkSheet : "[" + WorkSheet; WorkSheet = WorkSheet.EndsWith("'") ? WorkSheet.Substring(0, WorkSheet.Length - 1) + "]" : WorkSheet; WorkSheet = WorkSheet.Substring(0, WorkSheet.Length - 1).EndsWith("$") ? WorkSheet : WorkSheet.Substring(0, WorkSheet.Length - 1) + "$]"; if (!excelSheets.Exists(delegate(string k) { return (k.ToLower() == WorkSheet.ToLower() || k.ToLower() + "$" == WorkSheet.ToLower()); })) excelSheets.Add(WorkSheet); } return excelSheets; } catch (Exception ex) { return null; } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }3(parameter) C# private SourceFileDetails ScanWorkSheet(string excelFile, string WorkSheet, string ExtractionStatement, string FindColumn, out bool Success) { Success = false; System.Data.DataSet excelDataSet = new DataSet(); string connectionString = this.GetConnectionString(excelFile); using (OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(connectionString)) { try { objConn.Open(); OleDbDataAdapter cmd = new OleDbDataAdapter("select top 1000 * from " + WorkSheet, objConn); cmd.Fill(excelDataSet, WorkSheet); cmd.Dispose(); } catch { } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } } } SourceFileDetails sd = ScanRows(excelDataSet, ExtractionStatement, FindColumn); if (sd != null) { Success = true; sd.FileNamePath = excelFile; sd.ConnectionString = this.GetConnectionString(excelFile, true); return sd; } return (SourceFileDetails)null; }_ Metode berikut ScanRows memindai setiap baris untuk menemukan kolom yang kami minati. Ini adalah cara mengidentifikasi lembar yang berisi data, dan alamat awal data. Langkah kunci mereka dalam metode ini adalah membuat daftar kolom/bidang yang kami minati dan mencarinya. Di sini kami menganggap bidang/kolom tersebut muncul dalam 1000 baris. 1000 baris dipilih di sini untuk kinerja dan tidak ada yang membuat file excel berisi data setelah mengosongkan 1000 baris pertama. C# public SourceFileDetails ScanRows(DataSet excelDataSet, string ExtractionStatement, string FindColumn) { if (excelDataSet.Tables.Count < 1) return (SourceFileDetails)null; string ExtractFields = this.ReplaceString(this.ReplaceString(ExtractionStatement, "SELECT", ""), "From [Data$]", ""); List<string> FindStrings = ExtractFields.Split(',').Select(s => s.Trim().Replace("[", "").Replace("]", "").Replace("#", ".").Replace("(", "").Replace(")", "")).ToList(); foreach (DataTable dt in excelDataSet.Tables) { List<ItemValueHolder> FoundAddress = new List<ItemValueHolder>(); int iDuplicates = 0; foreach (DataRow dr in dt.Rows) { int iItemColumnIndex = 0; foreach (var fieldValue in dr.ItemArray) { object cellData = fieldValue; string sCellData = cellData.ToString().Replace("#", ".").Replace("[", "").Replace("]", "").Replace("(", "").Replace(")", ""); if (cellData != null) if (FindStrings.Exists(delegate(string k) { return k.ToLower() == sCellData.ToString().ToLower(); })) { if (!FoundAddress.Exists(delegate(ItemValueHolder t) { return t.Item.ToLower() == sCellData.ToLower(); })) FoundAddress.Add(new ItemValueHolder(sCellData, dt.Rows.IndexOf(dr), iItemColumnIndex)); else iDuplicates++; } iItemColumnIndex++; } int iTotalFields = FindStrings.Count - iDuplicates; if (100 * FoundAddress.Count / (float)iTotalFields >= 90.00) { var query = FoundAddress.GroupBy( item => item.ItemValue, (itemvalue, items) => new { Key = (int)itemvalue, Count = items.Count(), }); int dataStartAddress = (from p in query where p.Count == (query.Max(it => it.Count)) select p.Key).Max(); dataStartAddress += 1; string SheetName = "[" + dt.TableName + "$A" + dataStartAddress.ToString() + ":IV]"; string _selectCommand = this.ReplaceString(ExtractionStatement, "[Data$]", SheetName); string reportDate = ""; if (!string.IsNullOrEmpty(FindColumn)) try { int ColIndex = (from p in FoundAddress where p.Item.ToLower() == FindColumn.ToLower() select p.ColumnIndex).First(); DataRow drRowReport = dt.Rows[dataStartAddress]; reportDate = drRowReport[ColIndex].ToString(); } catch (Exception e) { //log } string NewFields = GetNewFieldsAtSource(FindStrings, dr); return new SourceFileDetails("", "", "", _selectCommand, reportDate, NewFields); } } } return (SourceFileDetails)null; } Kelas berikut digunakan untuk menyimpan detail file Excel yang dapat kita gunakan untuk memodifikasi string koneksi secara dinamis. Kelas tidak lain adalah kumpulan informasi; . C# using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ST_b050e37a03e84ca7b15b853949d29aa6.csproj { public class SourceFileDetails { private string _fileNamePath = ""; public string FileNamePath { get { return _fileNamePath; } set { _fileNamePath = value; } } private string _fileExtension = ""; public string FileExtension { get { return _fileExtension; } set { _fileExtension = value; } } private string _connectionString = ""; public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } } private string _extractQuery = ""; public string ExtractQuery { get { return _extractQuery; } set { _extractQuery = value; } } private string _reportDate = ""; public string ReportDate { get { return _reportDate; } set { _reportDate = value; } } private string _newFields = ""; public string NewFields { get { return _newFields; } set { _newFields = value; } } public SourceFileDetails() { } public SourceFileDetails(string fileNamePath, string fileExtension, string connectionString, string extractQuery, string reportDate, string newFields) { this.FileNamePath = fileNamePath; this.FileExtension = fileExtension; this.ConnectionString = connectionString; this.ExtractQuery = extractQuery; this.ReportDate = reportDate; this.NewFields = newFields; } } }_ Kelas berikut sekali lagi merupakan penyimpanan informasi perantara dan tidak ada fungsi lain. C# using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ST_b050e37a03e84ca7b15b853949d29aa6.csproj { public class ItemValueHolder { private string _item = ""; public string Item { get { return _item; } set { _item = value; } } private object _itemValue = ""; public object ItemValue { get { return _itemValue; } set { _itemValue = value; } } private int _columnIndex; public int ColumnIndex { get { return _columnIndex; } set { _columnIndex = value; } } public ItemValueHolder() { } public ItemValueHolder(string item, object itemValue, int columnIndex) { this.Item = item; this.ItemValue = itemValue; this.ColumnIndex = columnIndex; } } }_ Metode berikut membuat string koneksi untuk file excel tertentu yang disediakan. Itu dapat membangun string koneksi terlepas dari data yang tidak memiliki baris Header. Variabel HasHeader menentukan apakah kita memiliki baris Header atau tidak. C# public string GetConnectionString(string FileNamePath, bool HasHeader) { string ConnectionString = ""; string Extension = Path.GetExtension(FileNamePath).ToLower(); string BinaryExcelProvider = "Microsoft.Jet.OLEDB.4.0"; string XmlExcelProvider = "Microsoft.ACE.OLEDB.12.0"; string BinaryExcelExtProperties = "Excel 8.0"; string XmlExcelExtProperties = "Excel 12.0"; string XmlMacroExcelExtProperties = "EXCEL 12.0 Macro"; string Provider = ""; string ExtendedProperties = ""; switch (Extension) { case ".xls": Provider = BinaryExcelProvider; ExtendedProperties = BinaryExcelExtProperties; break; case ".xlsx": Provider = XmlExcelProvider; ExtendedProperties = XmlExcelExtProperties; break; case ".xlsm": Provider = XmlExcelProvider; ExtendedProperties = XmlMacroExcelExtProperties; break; } string Header = ";HDR=NO;IMEX=1"; if (HasHeader) Header = ";HDR=YES;IMEX=1"; string ConnectionStringFormat = "Provider={0};Data Source={1};Extended Properties=\"{2}{3}\";"; ConnectionString = string.Format(ConnectionStringFormat, Provider, FileNamePath, ExtendedProperties, Header); return ConnectionString; } Semua kode, paket (file Solusi Proyek SSIS), skrip sql untuk menghasilkan tabel tujuan dan contoh file excel yang dapat digunakan untuk pengujian dilampirkan Tempat Menarik Banyak file yang dimuat dengan untuk setiap pencacah file alih-alih menggunakan metode Pencarian juga diunggah. Silakan kembali dengan pertanyaan atau keraguan, saya akan dengan senang hati membantu Anda. Sejarahv3 LisensiArtikel ini, bersama dengan kode sumber dan file terkait, dilisensikan di bawah The Code Project Open License (CPOL) Ditulis oleh Kuthuparakkal Analis Bisnis Amerika Serikat Anggota ini belum memberikan Biografi. Asumsikan itu menarik dan bervariasi, dan mungkin ada hubungannya dengan pemrograman Bagaimana cara mendapatkan nama file Excel secara dinamis di SSIS?Klik kanan pada Manajer Koneksi Excel Ekspresi Pilih string koneksi membangun ekspresi (misalnya. (DT_WSTR, 50) GETDATE() + @[pengguna. Nama Berkas] +". xlsx") Bagaimana cara membaca nama file dinamis di SSIS?Bekerja dengan flat file secara dinamis dalam paket SSIS . Buat Tugas Skrip di. paket SSIS Anda beri nama "set folder path" Gunakan "DirPath" (variabel. . Gunakan "DestinationPath" sebagai. . Tulis skrip ini di Main(). Buat Tugas Sistem File. . Gunakan "DestinationPath" sebagai. . Buat Tugas sistem File. . Gunakan "SourceFilePath" Bagaimana cara membuat manajer koneksi dinamis di SSIS?Semua balasan . Buat variabel bernama Sumber dengan nama Database String Koneksi Sumber OLEDB sebagai nilai Klik Manajer Koneksi Sumber OLEDB untuk menavigasi ke jendela Properti Klik “…”. Klik Manajer Koneksi Tujuan OLEDB untuk menavigasi ke jendela Properti Klik “…” Bagaimana cara membuat Excel Connection Manager di SSIS?Pada menu SSIS, pilih Sambungan baru. Dalam kotak dialog Tambahkan SSIS Connection Manager, pilih EXCEL lalu Add . Buat manajer sambungan pada saat yang sama saat Anda mengonfigurasi Sumber Excel atau Tujuan Excel pada halaman Manajer sambungan Editor Sumber Excel atau Editor Tujuan Excel. |