SSIS Excel导入-工作表变量还是通配符? [英] SSIS Excel Import - Worksheet variable OR wildcard?

问题描述

我有一个SSIS数据导入包，该包使用源Excel电子表格，然后将数据导入到SQL Server数据库表中.由于Excel文件的工作表名称每天都在更改，因此我无法自动执行此过程.因此，我不得不在每天运行导入之前手动更改工作表名称.请注意，永远不会有其他工作表.

I have a SSIS data import package that uses a source Excel spreadsheet and then imports data into a SQL Server database table. I have been unsuccessful in automating this process because the Excel file's worksheet name is changed every day. So, I have had to manually change the worksheet name before running the import each day. As a caveat, there will never be any other worksheets.

我可以为工作表名称创建一个变量吗? 我可以使用通配符而不是工作表名称吗? 在启动导入作业之前，我会更好地创建一个Excel宏或类似的方法来更改工作表名称吗?

Can I make a variable for the worksheet name? Can I use a wildcard character rather than the worksheet name? Would I be better off creating an Excel macro or similar to change the worksheet name before launching the import job?

推荐答案

在相同的情况下，如果对您或其他人有帮助，这对我非常有用:

This works perfectly for me with the same scenario, in case it helps you or someone else:

必需的包级字符串变量2:

• varDirectoryList-您将在SSIS内部将其用于每个循环变量映射

• varDirectoryList - You will use this inside SSIS for each loop variable mapping

varWorkSheet-这将保存您更改的工作表名称.由于您只有1，所以非常完美.

varWorkSheet - This will hold your changing worksheet name. Since you only have 1, it's perfect.

设置:

• a.为每个循环添加SSIS
• b. Excel Connection Manager(在测试时连接到第一个工作簿，然后最后进入属性，并在表达式内部添加"Excel File Path"您的varDirectoryList.将DelayValidation True以及Excel Source任务设置为True.通过文件夹中的每个工作簿)
• c.在您的For Each循环内，添加一个Scrip Task C#，标题为获取更改工作表 命名为变量"或您的偏好.
• 数据流任务以及您的Excel源到SQL Table目标.

• a. Add SSIS For Each Loop
• b. Excel Connection Manager (connect to first workbook as you test, then at the end you will go to properties and add inside expression "Excel File Path" your varDirectoryList. Set DelayValidation True as well as your Excel Source task. *This will help it go through each workbook in your folder)
• c. Inside your For Each Loop add a Scrip Task C#, title it "Get changing worksheet name into variable" or your preference.
• Data Flow Task with your Excel Source to SQL Table Destination.

在您的Scrip任务中添加以下代码:

using System.Data.OleDb;


    public void Main()
    {
        // store file name passed into Script Task
        string WorkbookFileName = Dts.Variables["User::varDirectoryList"].Value.ToString();

        // setup connection string
        string connStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"EXCEL 12.0;HDR=Yes;IMEX=1;\"", WorkbookFileName);

        // setup connection to Workbook
        using (var conn = new OleDbConnection(connStr))
        {
            try
            {
                // connect to Workbook
                conn.Open();

                // get Workbook schema
                using (DataTable worksheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    // in ReadWrite variable passed into Script Task, store third column in the first 
                    // row of the DataTable which contains the name of the first Worksheet
                    Dts.Variables["User::varWorkSheet"].Value = worksheets.Rows[0][2].ToString();

                    //Uncomment to view first worksheet name of excel file. For testing purposes.
                    MessageBox.Show(Dts.Variables["User::varWorkSheet"].Value.ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
        }


    }

设置并运行之后，您将获得一个消息框，显示每个工作簿的工作表名称更改.

After you have this set up and run, you will get a message box displaying the changing worksheet names per workbooks.

• 如果使用的是Excel Source SQL Command，则需要第三个字符串 像varExcelSQL这样的变量，在里面像SELECT这样的表达式 来自['varWorkSheet $']的列，这些列将动态更改以匹配 每个工作簿.您可能需要也可能不需要单引号，请更改为 varExcelSQL中需要.
• 如果您不使用Excel Source SQL，而直接从 桌子;进入Excel Source Properties-> AccessMode-> 从变量打开OpenRowSet->选择varWorkSheet.

• If you are using Excel Source SQL Command you will need a 3rd string variable like: varExcelSQL and inside that an expression like: SELECT columns FROM ['varWorkSheet$'] which will dynamically change to match each workbook. You may or may not need the single quotes, change as needed in varExcelSQL.
• If you are not using Excel Source SQL and just loading straight from the Table; go into Excel Source Properties --> AccessMode --> OpenRowSet from Variable --> select varWorkSheet.

只要列结构保持不变，就应该解决这一问题.

That should take care of it, as long as the column structures remain the same.

如果您碰巧获得的文件在一列中具有多种数据类型；您可以在连接字符串中使用IMEX = 1，从而在导入时将数据类型强制为DT_WSTR.

If you happen to get files where it has multi data types in one column; you can use IMEX=1 inside your connection string which forces the datatypes to DT_WSTR's on import.

希望这会有所帮助:-)

Hope this helps :-)

这篇关于SSIS Excel导入-工作表变量还是通配符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！