SSIS Startup directory not found

  • This seems to be a common issue throughout my packages. Anywhere I try to access anything in the startup directory. The startup directory is a server URL mapped to Z:\. In this example package. The task that is failing is a script task that verifies the components required are in the startup directory.  This is just a script task with a Directory.Exists(StartPath) check.

    Z:\ = \\SQLServer\SSIS

    Startup directory  \\SQLServer\SSIS\GMS\ACS_Returns

     

    Executed as user:

    Invalid Input Path Z:\GMS\ACS_Returns\   <-- This is my message

    Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6560.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.

    Started:  7:43:27 PM

    Error: 2020-11-30 19:43:27.66     Code: 0x00000008

    Source: House Keeping

    Description: The script returned a failure result.

    End Error

    DTExec: The package execution returned DTSER_FAILURE (1).

    Started:  7:43:27 PM  Finished: 7:43:27 PM  Elapsed:  0.187 seconds.

    The package execution failed.  The step failed.

     

        Public Sub Main()
    Dim StartPath As String = ""
    If Dts.Variables.Contains("StartPath") = True Then
    StartPath = Dts.Variables("StartPath").Value
    Else
    Dts.Events.FireWarning(0, "Start Path not found", "The path or variable is missing", String.Empty, 0)
    Dts.TaskResult = ScriptResults.Failure
    GoTo MainExit
    End If

    Try
    If Directory.Exists(StartPath) Then
    Dts.Variables("TotalsQuery_conn").Value = StartPath & "QueryFor_ACS_Export.sql"
    Dts.Variables("Export_htm_conn").Value = StartPath & "ACS_Export.htm"
    Dts.Variables("Export_xsl_conn").Value = StartPath & "ACS_Export.xsl"
    Dts.TaskResult = ScriptResults.Success
    Else
    Console.WriteLine("Invalid Input Path " & StartPath)
    Dts.Events.FireWarning(0, "Start Path not found", StartPath, String.Empty, 0)
    Dts.TaskResult = ScriptResults.Failure
    End If

    Catch ex As Exception
    Dim strMsg As String = "Invalid Input Path " & StartPath & vbCrLf & ex.Message
    Send_Error_Email(strMsg)

    End Try

    MainExit:

    End Sub

     

  • This looks like an easy one to me - Z:\ is mapped for you, not mapped for all users on the server.  So the SQL Agent service account doesn't have a Z drive.

    Use the UNC path and you will likely not have the failures as long as the correct permissions are in place.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply