通过SSIS将具有动态名称的Excel文件导入到SQL表中？ [英] Importing Excel file with dynamic name into SQL table via SSIS?

问题描述

我在这里做了一些搜索，虽然有些问题是

 使用系统; 
使用System.Data; 
使用System.IO; 
使用Microsoft.SqlServer.Dts.Runtime; 
使用System.Windows.Forms; 
 
命名空间ST_fe2ea536a97842b1a760b271f190721e 
 {
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] 
公共部分类ScriptMain：Microsoft.SqlServer.Dts.Tasks.ScriptTask .VSTARTScriptObjectModelBase 
 {
 public void Main（）
 {
 string folderInput = Dts.Variables [User :: FolderInput] Value.ToString（）; 
 string fileMask = Dts.Variables [User :: FileMask]。Value.ToString（）; 
 
 try 
 {
 var files = Directory.EnumerateFiles（folderInput，fileMask，SearchOption.AllDirectories）; 
 
 foreach（string currentFile in files）
 {
 Dts.Variables [User :: CurrentFileName]。Value = currentFile; 
 break; 
} 
} 
 catch（Exception e）
 {
 Dts.Events.FireError（0，Script overkill，e.ToString（），string.Empty ，0）; 
} 
 
 
 Dts.TaskResult =（int）ScriptResults.Success; 
} 
 
枚举ScriptResults 
 {
 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success，
 Failure = Microsoft.SqlServer.Dts。 Runtime.DTSExecResult.Failure 
}; 
} 
} 
  

决策树

鉴于上述问题的两个决议，您如何选择？通常情况下，人们会说依赖，但只有在可能的时候，如果在多个文件中存在的情况下载入文件夹。这就是ForEach枚举器比脚本任务更麻烦的一个例子。否则，正如我在原来的答复中所说，增加了开发，测试和维护项目的成本，没有明显的收益。

位和bobs

进一步解决问题的细微差别：配置Excel - 您将需要更具体的是什么不起作用。 Siva的SO答案和链接的blogspot文章都显示了如何使用变量I的值调用 CurrentFileName 来确保Excel文件指向正确文件。 p>

您将需要将Connection Manager和Data Flow的 DelayValidation 设置为True作为设计时值因为变量在程序包开始执行时将无效。请参阅此回答较长时间解释，但是再次，Siva在他们的答案中称这个。

I've done a few searches here, and while some issues are similar, they don't seem to be exactly what I need.

What I'm trying to do is import an Excel file into a SQL table via SSIS, but the problem is that I will never know the exact filename. We get files at no steady interval, and the file usually has a date/month in the name. For instance, our current file is "Census Data - May 2013.xls". We will only ever load ONE file at a time, so I don't need to loop through a directory for multiple Excel files.

My concept is that I can take this file, copy it to a "Loading" directory, and load it from there. At the start of the package, I will first clear out the loading directory, then scan the original directory for an Excel file, copy it to the loading directory and then load it into SQL. I suppose I may have to store the file names somewhere so I don't copy the same file into the loading directory in subsequent months, but I'm not really sure of the best way to handle that.

I've pretty much got everything down except the part that scans the directory for the Excel file and copies it to the loading directory. I've taken the majority of my info from this page, which (again) is close to what I want to do but not quite exactly the solution I need.

Can anyone get me over the finish line? I can't seem to get the Excel Connection Manager right (this is my first time using variables), and I can't figure out how to get the file into the Loading directory.

解决方案

Problem statement

How do I dynamically identify a file name?

You will require some mechanism to inspect the contents of a folder and see what exists. Specifically, you are looking for an Excel file in your "Loading" directory. You know the file extension and that is it.

Resolution A

Use a ForEach File Enumerator.

Configure the Enumerator with an Expression on FileSpec of *.xls or *.xlsx depending on which flavor of Excel you're dealing with.

Add another Expression on Directory to be your Loading directory.

I typically create SSIS Variables named FolderInput and FileMask and assign those in the Enumerator.

Now when you run your package, the Enumerator is going to look in Diretory and find all the files that match the FileSpec.

Something needs to be done with what is found. You need to use that file name that the Enumerator returns. That's done through the Variable Mappings tab. I created a third Variable called CurrentFileName and assign it the results of the enumerator.

If you put a Script Task inside the ForEach Enumerator, you should be able to see that the value in the "Locals" window for @[User::CurrentFileName] has updated from the Design time value of whatever to the "real" file name.

Resolution B

Use a Script Task.

You will still need to create a Variable to hold the current file name and it probably won't hurt to also have the FolderInput and FileMask Variables available. Set the former as ReadWrite and the latter as ReadOnly variables.

Chose the .NET language of your choice. I'm using C#. The method System.IO.Directory.EnumerateFiles

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_fe2ea536a97842b1a760b271f190721e
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string folderInput = Dts.Variables["User::FolderInput"].Value.ToString();
            string fileMask = Dts.Variables["User::FileMask"].Value.ToString();

            try
            {
                var files = Directory.EnumerateFiles(folderInput, fileMask, SearchOption.AllDirectories);

                foreach (string currentFile in files)
                {
                    Dts.Variables["User::CurrentFileName"].Value = currentFile;
                    break;
                }
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Script overkill", e.ToString(), string.Empty, 0);
            }


            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Decision tree

Given the two resolutions to the above problem, how do you chose? Normally, people say "It Depends" but there only possible time it would depend is if the process should stop/error out in the case that more than one file did exist in the Loading folder. That's a case that the ForEach enumerator would be more cumbersome than a script task. Otherwise, as I stated in my original response that adds cost to your project for Development, Testing and Maintenance for no appreciable gain.

Bits and bobs

Further addressing nuances in the question: Configuring Excel - you'll need to be more specific in what isn't working. Both Siva's SO answer and the linked blogspot article show how to use the value of the Variable I call CurrentFileName to ensure the Excel File is pointing to the "right" file.

You will need to set the DelayValidation to True for both the Connection Manager and the Data Flow as the design-time value for the Variable will not be valid when the package begins execution. See this answer for a longer explanation but again, Siva called that out in their SO answer.

这篇关于通过SSIS将具有动态名称的Excel文件导入到SQL表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！