-2

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

    }
}
Community
  • 1
  • 1
Unknown User
  • 3,598
  • 9
  • 43
  • 81
  • Obviously the file most be opened or being accessed by another process. Ensure that you release the file after importing before trying to archive it. – zulqarnain Jul 14 '15 at 10:57
  • How to release it? I tried in google I couldn't find anything. – Unknown User Jul 14 '15 at 11:06
  • Please show your C# code. – zulqarnain Jul 14 '15 at 11:13
  • From this link - http://bicortex.com/how-to-programmatically-archive-file-into-a-directory-using-script-task-in-ssis/ I'm using the C# code. – Unknown User Jul 14 '15 at 11:48
  • What error are you getting from your script task? – Tab Alleman Jul 14 '15 at 13:17
  • possible duplicate of [SSIS (File System Task) - Unable to move file](http://stackoverflow.com/questions/31246988/ssis-file-system-task-unable-to-move-file) – Tab Alleman Jul 14 '15 at 13:22
  • As Tab as identified, Don't ask the same question. Continue editing your question until it's something that can be solved. – billinkc Jul 14 '15 at 13:56
  • @TabAlleman - I've updated with the error. But if I run the same code line by line in the debug mode. It's working fine. When I execute the entire container I get this error. Please help.. – Unknown User Jul 14 '15 at 14:44
  • That's not an error message. It's just a snippet of a stack trace showing things running smoothly. Is there more to the output? Look for something with the word "error" in it. And try the suggestions I added to your original question. – Tab Alleman Jul 14 '15 at 14:48
  • Spit balling an answer here: Add a Script Task prior to the File System Task and manually close the Excel Connection Manager – billinkc Jul 14 '15 at 14:53
  • @billinkc - How can I do that? How can I close the OLEDB connection that I've created using Script task? I'm searching a lot on how to close to the connection. But I'm not getting anything. – Unknown User Jul 15 '15 at 07:44
  • @TabAlleman - I get a alert box with this message as title - `DTS Script Task: Runtime Error`. And headers as `DTS Scipt task has encountered an exception in user code: Project name: ST_123979jkajsdlk. Exception has been thrown by the target of invocation.` And inside a textbox the above text what I've mentioned in the question. – Unknown User Jul 15 '15 at 07:46
  • @TabAlleman - But when I checked in the debug mode. I found no errors. It's working fine. When I execute the container without the debug mode. That's when I get that popup. – Unknown User Jul 15 '15 at 07:47
  • @TabAlleman - Please see the question on the update part. – Unknown User Jul 15 '15 at 08:14
  • Sounds like the breakpoint is giving your package time to close the connection. Try the solution here: http://stackoverflow.com/questions/20659223/ssis-the-process-cannot-access-the-file-because-it-is-being-used-by-another-pr – Tab Alleman Jul 15 '15 at 13:05
  • @TabAlleman - That's not working either. After the foreach added a SQL Task and gave 20 secs wait and then added the script task to move the files. But still I get that popup and the file is not moved to archive. And if I run only that task. Then the file is getting moved. Is there any other solution? – Unknown User Jul 15 '15 at 14:47
  • Did you try the solutions here?: http://stackoverflow.com/questions/2758209/ssis-for-each-file-loop-and-file-system-task-to-copy-files – Tab Alleman Jul 15 '15 at 14:55
  • @TabAlleman - I'm not referring to the actual file. I've created a variable for the filename and the variable value is set to a differenct file. And I've set the `DelayValidation: True`. As I've used variable, the file what ever I put in the directory, it automatically comes in the schema. – Unknown User Jul 16 '15 at 06:40
  • @TabAlleman - And I've got series of tasks. Like I run the tasks for multiple folders, for about 5 folders. I thought a work around like I'll import all the data from 5 directories to the db and the write one script task to move all the files from each directory to the archive. And even trying with this, I'm getting the same error. Also tried running the script task outside the sequence container, but the script task doesn't run when I put it outside the container. – Unknown User Jul 16 '15 at 06:44
  • When I use a excel connection manager instead of .Net providers of OleDb. It works fine. I should create a connected with .Net provider because I wanted to loop through the sheets. – Unknown User Jul 16 '15 at 06:55
  • @TabAlleman - After a big struggle, finally was able to archive the file with the help of this post - http://stackoverflow.com/questions/7366298/good-and-clean-way-to-wait-until-the-file-closes. I added a script task after the foreach loop container for the workbook and it worked fine. Thank you very much for your help Tab :) – Unknown User Jul 16 '15 at 09:45

1 Answers1

0

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)

V2k
  • 344
  • 2
  • 4
  • 14