March 20, 2017 at 10:34 pm
When I Deploy SSIS Project from Visual Studio - package works as expected.
When I deploy from the server
Information Services Catalogs
SSISDB
------Folder
------------Project (right click Deploy Project)
Something goes wrong.
I execute package without errors.
Everything works except Script Task.
It does not do the job.
March 21, 2017 at 4:35 am
What do the error messages display in the log? What is your script task doing? We need more detail. "Goes Wrong" means nothing.
Can you provide much more concise information please.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 21, 2017 at 7:56 am
That's a puzzle.
When I Execute package from Informati0on Services Catalogs
I don't see any errors.
All steps SUCCESS / Green.
Script Task
checks if the file exists and assigns a variable User::a_FileExists.
But it does not matter what's the content of the Script Task.
I did a dummy test package with two steps.
1st step Execute SQL --------------------------------------------------------------------------------------- works
2nd step Script Task ( build Query and cmd.ExecuteNonQuery() ) ---------------------- not working and no errors
I suspect it has something to do with
ISDeploymentWizard.exe version.
The version on my PC does the job.
Maybe the version that is installed on our QA server is not good?
Original package Script Task VB code:
Public Sub Main()
Dim l_InputFolder As String
Dim l_FileMask As String
l_InputFolder = ReadVariable("User::c_InputFolder").ToString
l_FileMask = ReadVariable("User::c_FileMask").ToString
Dim objDir As DirectoryInfo = New DirectoryInfo(l_InputFolder)
Dim objFile As FileInfo() = objDir.GetFiles(l_FileMask)
If objFile.Length > 0 Then
Dts.Variables("User::a_FileExists").Value = True
Else
Dts.Variables("User::a_FileExists").Value = False
End If
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex '//Handle things your way or throw back
End Try
Return result
End Function
March 21, 2017 at 8:06 am
So what does it do? You said it's not working, not working how?
Quick question as well, why are you using a separate function to get the value of a variable? Why not just:l_InputFolder = Dts.Variables("User::c_InputFolder").Value
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 21, 2017 at 12:51 pm
The file is there.
But Script Task reports file does not exist.
If you use this method:
l_InputFolder = Dts.Variables("User::c_InputFolder").Value
You need to list all the variables in Script Task Editor.
With my method, I don't care.
ReadOnlyVariables
ReadWriteVariables
are empty in my Script Task Editor.
March 21, 2017 at 2:41 pm
Are you deploying on right server ? Also check the file path is correct
March 22, 2017 at 3:00 am
RVO - Tuesday, March 21, 2017 12:51 PMThe file is there.
But Script Task reports file does not exist.If you use this method:
l_InputFolder = Dts.Variables("User::c_InputFolder").Value
You need to list all the variables in Script Task Editor.
With my method, I don't care.ReadOnlyVariables
ReadWriteVariables
are empty in my Script Task Editor.
You don't need to list them all, only the one's you care about, in your case, User::c_InputFolder and User::c_FileMask and read, and User::a_FileExists as ReadWrite. That's 3 variables. That's not a lot.
Hoever, considering that you stated ReadWrite Variable has no variables; you don't ever unlock your variable User::a_FileExists, so how are you assigning to it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 22, 2017 at 8:19 am
Thom A.
My method works. I like it. If you don't understand it, that's fine.
It has nothing to do with the topic I am discussing.
Don't deviate from the main issue.
I did a test.
I setup SSIS with T-SQL:use SSISDB
DECLARE @ProjectBinary VARBINARY(MAX);
DECLARE @operation_id BIGINT;
SET @ProjectBinary =
(SELECT * FROM OPENROWSET(BULK '\\XXXXXXX\XXXXX.ispac', SINGLE_BLOB) AS BinaryData);
EXEC catalog.deploy_project
@folder_name = 'XXXX',
@project_name = 'XXXX_NewETL',
@Project_Stream = @ProjectBinary,
@operation_id = @operation_id out;
Then I setup SQL Agent job with Step "Run AS" SSISProxy account
that has access to file/folders.
I execute the job.
It deploys project ok.
I execute new package.
Script Task works.
BINGO !!
It all depends on who deploys ISPAC.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply