December 1, 2020 at 3:19 am
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
December 1, 2020 at 2:14 pm
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