How pass parameters variables and use “Set Values” field from a “SQL Server Job” to a “SSIS Package”
In this example we create a simple “SSIS Package” that create a folder and use a path passed by a variable from “SQL Job” parameter.
Creation of a new “SSIS Package”
1. Create a new SSIS project “MyFirstParameterSSISPackage”.
2. Create a variable in your SSIS packages, for this example a variable “param_MyFirstParameter” type “string”.
3. Add a “Script Task” on your “Control Flow”.
4. Edit “Script Task” to add “param_MyFirstParameter” on “ReadOnlyVariables”.
5. Click on “Edit Script” button to open a new “.NET script environment”.
6. Add this code in “Public Sub Main”, the code create a directory in the path stored in variable “param_MyFirstParameter”, the code return “ScriptResults.Success” if directory is successfully created and “ScriptResults.Failure” if error.
Try
'Create directory with path stored in variable "param_MyFirstParameter"
IO.Directory.CreateDirectory(Dts.Variables("param_MyFirstParameter").Value.ToString)
Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
' Return error
Dts.TaskResult = ScriptResults.Failure
End Try
7. Save and close “Intergration Services Script Task”.
8. Edit the value of variable “param_MyFirstParameter” and add “C:\MyTestFolderSSISParameter” in value.
9. Run “SSIS Package” and verify that the folder “C:\MyTestFolderSSISParameter” exist in “C:” drive.
10. Delete folder and remove the value field in variable “param_MyFirstParameter”.
11. Save project.
Publish “SSIS Package” on the target server.
1. Select package “Package.dtsx” in “Solution Explorer”.
2. Open menu “File => Save Copy of Package.dtsx As…”.
3. Add the destination server for publication in field “Server”.
4. Add a “Package path” with the package name “/MyFirstParameterSSISPackage”, click “OK” button.
5. Verify that the package is successfully published in your destination “Integration Services” folder in “SQL Server”.
Create the new “SQL Server” scheduled task to run your package and pass parameters
1. Create a new job in your “SQL Server” named “JOB-MyFirstParameterSSISPackage”.
2. Add a new “New Job Step” named “STEP-MyFirstParameterSSISPackage”.
3. Select option “SQL Server Integration Services Package” in field “Type”.
4. In “General” tab configure “Package source”, “Server” and set value “\MyFirstParameterSSISPackage” in “Package” field.
5. In “Set values” tab, add a new “Properties Path” and “Value Field”.
“Property Path” => \Package.Variables[User::param_MyFirstParameter].Properties[Value]
“Value” => C:\MyTestFolderSSISParameter
6. Save and close your job.
7. Test your job “as step” and check that the folder is successfully created in “C:\MyTestFolderSSISParameter”.
Remark :
To pass a parameter from a “SQL Server” job to a “SSIS Package” use the convention “\Package.Variables[User::THE_VARIABLE_HERE].Properties[Value]” in “Property Path” on tab “Set values”
Download the SSIS Sample Package :
SSIS-MyFirstParameterSSISPackage.zip (85 ko)