none
Error running SSIS packages using Script Task RRS feed

  • Question

  • Hello,

    I'm struggling with a SSIS package that uses a Script Task component. This particular package is run a few times via a SQL Agent schedule using different config files. The basic premis of the package is to move/copy files from one location to another depending on the values set in the config file. The same package is used elsewhere on other servers and it runs correctly.

    When I run the package from BIDS it responds as I would hope.
    When I deply the package to the server and manually run it it responds as I would hope.
    When I schedule the package to run now it responds as I would hope.
    When I schedule the package to run overnight it fails.

    The SQL Log records the following error:

    Date  25/08/2011 22:00:00
    Log  Job History (EDI File Management)

    Step ID  1
    Server  SERVERNAME
    Job Name  EDI File Management
    Step Name  Copy Files to EDIMerge folder
    Duration  00:00:16
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    Executed as user: MYDOMAIN\AGENTACCOUNT. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  10:00:00 PM  Error: 2011-08-25 22:00:14.84     Code: 0x00000001     Source: File Action Task      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'c:\SourceFolder'.     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)     at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)     at System.IO.DirectoryInfo.GetFiles(String searchPattern, SearchOption searchOption)     at ScriptTask_fba5e5340fb8488089644cf316381c7a.vbproj.ScriptMain.Main()     --- End of inner exception stack trace ---     at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)     at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)     at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)     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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)     at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:00:00 PM  Finished: 10:00:15 PM  Elapsed:  15.563 seconds.  The package execution failed.  The step failed.

    I also find a number of errors in the Windows Event Logs (slightly different time though) along the lines of:

    Event Type: Warning
    Event Source: Visual Studio - VsTemplate
    Event Category: None
    Event ID: 1
    Date:  8/25/2011
    Time:  10:38:00 PM
    User:  N/A
    Computer: SERVERNAME
    Description:
    Error in Template (C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\ItemTemplates\CSharp\Web\1033\AdoNetDataServiceCSharpWap.zip), file (WebDataService.vstemplate).  Unknown attribute (_locID).

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    This all seems to imply that something is not quite installed correctly on my server, probably arround the VSTA area, but what?

    • How can I identify the real error?
    • How can I resolve the error?

    Any thoughts greatly appreciated,

    Mike

    Friday, August 26, 2011 10:22 AM

All replies

  • Please check if user used to run the SQL Server Agent Service has rights to your folder 'c:\SourceFolder'. or specify a proxy account to run your job

     

    Source: File Action Task      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'c:\SourceFolder'.


    --------------------------------------------------------

    Surender Singh Bhadauria

     

    Friday, August 26, 2011 11:19 AM
  • Surender,

    The 'C:\SourceFolder' doesn't exist on the server as it was only used in the development of the package. The actual folder should be acquired from the config file, but it apparently is not. I understand that the sequence of config file lookups has changed a bit with SQL Server 2008 R2 and that if the specified file can not be found (or the property is missing) then the development paramenters will be used. I guess this is what is happening in this case, but I don't understand why it is not finding the specified config file.

    Mike

    Friday, August 26, 2011 12:15 PM
  • Mike,

    Do you pass your config file in the SQL Agent Job Step (Under tab configurations?) If there is something wrong with your configuration it would help if you could explain it in detail.

    Friday, August 26, 2011 1:30 PM
  • Bram,

    Yes the config file is specified in the SQL Agent Job Step in the configurations tab.

    I'm not aware of anything being wrong with the configuration, and when I run the job manually it appears to use it successfully.

    Mike

    Friday, August 26, 2011 2:12 PM
  • I do not think the Windows Log message is related to the one you got reported by the package.

    Is that possible at night the c:\SourceFolder is not there or being "maintained" somehow?

    I would literally be there at the package run time to figure out what's going on.

     


    Arthur My Blog
    By: TwitterButtons.com
    Friday, August 26, 2011 2:19 PM
    Moderator
  • Arthur,

    The C:\SourceFolder is never there on the server since that folder was only ever present on the development PC used to build the package. The actual 'source folder' is (should be) retrieved from the config file when the package runs, but obviously this is sometimes not happening.

    I've just watched a live schedule successfully complete (the package is in a different time zone and so I can see it on its normal schedule) and this time it worked without intervention.

    I'm not happy that the package sometimes works and sometimes does not, but neither can I see a pattern at the moment that might indicate why it does fail sometimes.

    Mike

    Friday, August 26, 2011 2:31 PM
  • What folder name did you use in the config?
    Arthur My Blog
    By: TwitterButtons.com
    Friday, August 26, 2011 2:33 PM
    Moderator
  • Ah,

    I see your point. The config file sets the sourcefolder parameter to "D:\csserver\cs3hk\edi_out" but that does exist and has done for many years.

    Friday, August 26, 2011 2:45 PM
  • Good so far, and where do you store and how do you instruct your package to find the config file?
    Arthur My Blog
    By: TwitterButtons.com
    Friday, August 26, 2011 3:17 PM
    Moderator