Search code examples
pythonssissql-server-data-toolsssis-2012msbi

How to pass a variable value in SSIS to Python Script : Execute Process task

I've a execute process task which executes python Scriptenter image description here

enter image description here

I want to pass root variable value in python from SSIS when I give the project parameter value in Python script it is throwing an error how do I do this?

Solution

  • As you have in your screenshot,

    root = $Project::NewIngestionPath;

    Is not going to be correct python as we don't use semicolons there. As well as the python engine will have no context of how to work with $Project::NewIngestionPath as that's an SSIS thing.

    The piece you're missing is in your first picture: Arguments

    The Execute Process Task accepts arguments for the called process. When it runs now, it's analogous to

    cd C:\Python
"C:\Program Files\Python310\python.exe" C:\Python\Pre_Ingestion_Fix_Script.py

    What you are looking to do is to add another argument of

    "C:\Program Files\Python310\python.exe" C:\Python\Pre_Ingestion_Fix_Script.py \\server\path\to\new\ingestion

    This gets tricky for a few reasons. First of all, the DOS command arguments get weird when there are spaces involved as classically a space was interpreted as the next argument. Some tools can only inject one set of double quoted arguments when it calls CMD.exe and I honestly don't remember how SSIS works in this case. Further muddying the waters, we have the Arguments option for an Execute Process Task or you can use StandardInputVariable. And you can make that more complex by using the Expressions tab on Execute Process Task to supply the values needed.

    enter image description here

    I would create a variable of type String in SSIS called CommandArguments. In the Expression section of the Variable, I'd use the following

    "C:\Python\Pre_Ingestion_Fix_Script.py " + $Project::NewIngestionPath

    The resulting value will be something like

    C:\Python\Pre_Ingestion_Fix_Script.py \\server\path\to\new\ingestion

    You can put a breakpoint on your Execute Process Task and visually inspect the value for @[User::CommandArguments] Do they look right? They should as they would be the same as the design time value for the variable but this is important as we're about to use Expressions on a Task and there is no opportunity to examine the expressed value, it is hidden.

    In optimal case, you can simply delete what is in Arguments and in the drop down box for StandardInputVariable, select our CommandArguments. Run the package and see if it works as expected. Your python package should have some logging so you can examine the results of the command line arguments.

    If it fails, then delete the variable from StandardInputVariable and click on Expressions tab. Wire up our variable to Arguments and run the package again, does it work

    enter image description here

    One of those should, I simply can't say with certainty as there's plenty of other places this can go wrong (permissions, the actual value of our Project Parameter, etc) but that is how one passes a project parameter into the call to a python file.

    The python specific bit

    Finally,

    root = $Project::NewIngestionPath

    isn't going to work so we need to adjust the script to access the parameters supplied at runtime. That's our friend sys.argv

    import sys
root = sys.argv[1]
# rest of code here