SSIS - 将Excel文件与OLEDB连接移动到存档 [英] SSIS - Move Excel File with OLEDB Connection to Archive

问题描述

我已经使用 Microsoft Office 12.0 Access数据库引擎OLE DB提供程序作为Excel模式创建了一个连接，以循环遍历Excel文件中的所有表格，如此问题所示如何循环通过Excel文件，并使用SSIS包将它们加载到数据库中？

I've created a connection using Microsoft Office 12.0 Access Database Engine OLE DB Provider as Excel Schema to loop through all the sheets in the Excel file as demonstrated in this question How to loop through Excel files and load them into a database using SSIS package?

并使用 Foreach ADO.NET Schema Rowset Enumerator 循环遍历excel文件。

And using Foreach ADO.NET Schema Rowset Enumerator to loop through the excel files.

所有内容都正常工作，但从Excel导入数据后，我想将该文件移动到Archive文件夹。并尝试使用文件系统任务，但是我收到错误为

Everything is working fine now, but after importing the data from Excel, I wanted to move that file to Archive folder. And tried using a File System Task, but I get the error as

[文件系统任务]错误：出现以下错误消息时出错：进程无法访问该文件，因为它正在被另一个进程使用。。

[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".

而且我也尝试了这个链接。但是我收到一些错误，无法解决这个错误，因为我在C＃上没有任何知识。

And I also tried with script task from this link. But I was getting some error and couldn't solve the error as I've got zero knowledge on C#.

以下是我尝试使用的错误使用脚本任务移动文件。

Below is the error I've got when I tried to move the files using a script task.

在System.RuntimeMethodHandle.InvokeMethod（Object target，Object [] arguments，Signature sig，Boolean constructor ）
在System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal（Object obj，Object [] parameters，Object [] arguments）
在System.Reflection.RuntimeMethodInfo.Invoke（Object obj，BindingFlags invokeAttr，Binder binder，Object []参数，CultureInfo文化）
在System.RuntimeType.InvokeMember（String name，BindingFlags bindingFlags，Binder binder，Object target，Object [] providedArgs，ParameterModifier [] modifiers，CultureInfo culture，String [] namedParams）
在Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript（）。

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript().

---

<强>更新：

这是我正在尝试移动文件的完整代码。

Here's my complete code with which I'm trying to move the files.

如果我在枚举ScriptResults 中添加断点，我没有得到该弹出窗口，任务已成功完成，并且该文件也被移动到归档，但如果我不添加C＃代码中的任何断点，我得到该流行，文件不被移动到存档。

If I add a breakpoint at enum ScriptResults, I don't get that popup and the task gets completed successfully and the file is also been moved to archive, but if I don't add any breakpoint in the C# code, I get that pop and the file is not moved to archive.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

namespace ST_9fc6ad7db45c4a7bb49f303680f789ef
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain :     Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            DirectoryInfo di = new     DirectoryInfo(Dts.Variables["SplitSkill_FolderPath"].Value.ToString());

            FileInfo[] fi = di.GetFiles("*.xlsx");
            String filename = fi[0].Name;

            string sourceFileName = filename;
            string destinationFile = @"D:\Flipkart\Data\Split     Skill\Archive\" + sourceFileName;
            string sourceFile = @"D:\Flipkart\Data\Split Skill\" +     sourceFileName;

            if (File.Exists(destinationFile))
                File.Delete(destinationFile);
            // To move a file or folder to a new location:
            System.IO.File.Move(sourceFile, destinationFile);

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

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

推荐答案

据我所知，我认为你有一个循环使用工作表，另一个用于循环文件的任务。所以你可能在foreach循环中有两个任务。尝试使用系统任务

As far as I understand, I think you have a task for looping through sheets and another for looping through files. So you probably have 2 tasks inside a foreach loop. Try making a copy of the file inside the foreach loop with a system task

为foreach循环创建文件的副本

for the executable

c:\windows\system32\cmd.exe

和对于参数soemthing像

and for the arguments soemthing like

C COPY "C:\xxx\abc\\Destination_template.accdb" "C:\xxx\abc\\Destination_template.accdb"Destination_template - Kopie.accdb"

然后创建一个文件系统任务将该副本移动到您的存档
这应该做的伎俩（也许不是最好的方法，但应该工作）

Then create a file system task which moves that copy to your archive. This should do the trick (Maybe not the best approach but should work)

这篇关于SSIS - 将Excel文件与OLEDB连接移动到存档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！