1

First the basics: I'm using VS2015 SSDT to create a SSIS Package to pick up two files on a DFSR share (Windows 2012 R2) that are then imported into a SQL Server 2016 database. The package is run via SQL Server Agent. The SQL Server Agent is running under a domain account that has permissions to the DFSR folder and subsequent files.

The Problem: I'm getting a weird issue that I seem to have dwindled down to having SSIS script tasks being unable to change the value of a SSIS variables.

I've got a script task that uses VB2015 to check if two files exist. The file paths use variables to build the full path and then checks that both are present. If they are both present, it sets a variable to "True" and then exist. This runs just fine in VS2015 SSDT while testing the functionality, but the variable doesn't change when running under SQL Server Agent.

Here's the code:

Public Sub Main()
    Dim filepath As String = CStr(Dts.Variables("User::varFileDirectory").Value)
    Dim emplfile As String = filepath + CStr(Dts.Variables("User::varEmployeeCSVFile").Value)
    Dim histfile As String = filepath + CStr(Dts.Variables("User::varHistoryCSVFile").Value)
    If (File.Exists(emplfile) AndAlso File.Exists(histfile)) Then
        Dts.Variables("User::varFilesExist").Value = True
    Else
        Dts.Variables("User::varFilesExist").Value = False
    End If
    Dts.TaskResult = ScriptResults.Success
End Sub

enter image description here

If the files are found in the folder it set User::FilesExist to "True" and then is used in the subsequent constraint as an expression:

enter image description here

I pass this constraint while testing from SSDT 2015, but once it's deployed over to the SSIS repository on SQL Server 2016, it no longer get's passed this constraint when there are files there.

I've verified my package runs by simply setting the default value of "User::varFilesExist" to "True". The pakage runs flawlessly from SQL Server Agent when this is done. On the first run, it deletes the files that have been loaded, so when it runs a second time, it fails since those files are not present.

I feel like I'm missing something simple, but it appears that the script is not changing/updating the value of "User::FilesExist" within the script. I believe my VB2015 code is correct because it tests just fine when run within VS2015 SSDT

weekendclimber
  • 25
  • 5
  • It's not clear why you think that the package isn't changing the variable's value. What are you seeing that makes you think this? The only code you've shown us is the script that sets the value of the variable. We can't see how the variable gets used later. – Tab Alleman Jun 12 '18 at 19:29
  • I'll update the original post. I've since fixed this by using SSDT 2017, so it must be something withe SSDT 2015 or my configuration there of. – weekendclimber Jun 13 '18 at 20:21

1 Answers1

0

Today I installed SSDT 2017 and brought over my packages. Tested and then deployed and everything is working as expected. Not sure what was going on.

weekendclimber
  • 25
  • 5