SSIS - Move Excel File with OLEDB Connection to Ar

问题:

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?

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

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.".

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.

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().

---

Update:

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

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

    }
}

回答1:

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

for the executable

c:\windows\system32\cmd.exe

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)