User Review
( votes)While implementing integration packages between XML and Dynamics 365 CE, we had a requirement which required us to write a Script Component (custom code) in Data Flow task to check the status of a record in CRM and based on the value of it take appropriate action.
The initial thought was to create a user-defined variable and update the variable in the Script Component and use its value inside a new Derived Column and further in the CRM Destination Component.
However, to our surprise, although we can see the variable’s value being updated within Script Component while debugging, the same was not being reflected in the Derived Column and the Destination Component in that Data Flow task.
But within the next Script task of that Control Flow, we were getting the updated value.
Below is our sample source XML File
The package
The data flow task reads the above XML file and within Script Component it sets the value of fullname variable and we are putting its value within a Derived Column. SQL Server Destination is inserting all the values into a SQL table.
The Script Component à
The Derived Column à
The SQL Server Destination à
On running the package, the result à
It is still showing the default value.
Although we can see the value correctly updated
Now within the Script Task in the control flow, we are using a message box to show the value of that variable.
The result à
The reason for the above problem is SSIS package variables does not update during the execution of the data flow task. Those variables get updated once the Data flow task finishes. Therefore, we cannot use the updated value of the variable during the execution of a dataflow task.
Even though you could change the variable values within pre-execution or post-execution methods, it only takes effect on the value of the variable after the execution of the data flow task.
http://dineshpathirana.blogspot.com/2011/05/ssis-package-variables-cannot-update.html
So here instead of using the User Defined Variable, we used the Output column of the Script Component.
And we set the same inside the script editor.
Hope it helps.
