Recently after deploying the SSIS packages, on one of the packages that uses Script Component, we started getting below error message
This happened because SSIS packages had target version SQL 2017 and the SQL Server version was 2016 (select @@VERSION)
The fix is to update the target version that matches the SQL Server Version.
Right click and go to project properties and update the Target Version to match and redeploy the project.
Hope this helps..
Recently while trying to use KingswaySoft’ s CRM Connection Manager in the Script Component we got the below error
Turned out the issue was because of the wrong version of KingswaySoft.IntegrationToolkit.DynamicsCrm assembly being referred.
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\KingswaySoft.IntegrationToolkit.DynamicsCrm
Referencing the correct one fixed it.
Hope it helps..
Here’s an article I wrote for @NigelFrank detailing how Dynamics 365 for Customer Service users can integrate their solution with XML. I hope you find it useful! #MSDyn365 #MsDynCRM
Integration with XML for Dynamics 365 for Customer Service users: my illustrated guide for @NigelFrank
Follow the link here
https://www.nigelfrank.com/blog/integrate-dynamics-365-for-customer-engagement-with-xml/
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.
Precedence Constraint component in SSIS allows us to control the flow of the execution of tasks within the Control Flow.
Suppose we have the below package wherein we are traversing through the folder using Foreach Loop Container and are processing XML and docx files within that folder.
And we want them to be processed differently.
We have the following user-defined variables specified.
Within the Script Task, we have specified FilePath as the ReadOnlyVariables to read the full file path and figure out the extension and FileExtension as the ReadWriteVariables to which we will write the result.
Below is our code for the Script Task, here we are reading the file path and getting the extension and setting the FileExtension variable.
Now back in our control flow, open the Precedence Constraint Editor and select Evaluation expression as Expression and Constraint.
The constraint should be success and for the expression, we have defined the FileExtension variable’s value to be .docx, and both the condition to be true i.e. Logical And.
Similarly, define the Precedence Constraint for the second brand of the Script Task as shown below.
Now run the package and based on the file extension we would see the execution taking the appropriate path, processing the next component and showing the correct message.
Hope it helps..
Recently while working on an integration requirement we had to loop through all the XML Files within a folder and subfolder and create corresponding records inside Dynamics 365 CE.
For this, we used the XML Source component which is part of KingswaySoft’s Productivity Pack https://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/json-and-xml-processing/xml-source
Below are the steps we need to perform to loop through the files.
Create an SSIS Project and drag the Foreach Loop Container to the control flow.
Here we are using 3 variables, one for the full path to the file, the other for extension and the third one to specify the folder.
Double click and the Foreach Loop Editor, set the enumerator as Foreach File Enumerator and set the Directory and FileSpec property expressions to use the variables that we had defined.
Check the Traverse Subfolder if we want to traverse the subfolders.
Click on Variable Mapping and set the FilePath variable that will be used to iterate through the files in the Folder and Subfolder specified.
To see it in action, we have added a Script Task inside the container.
Here we have set the FilePath variable as the ReadOnlyVariables that we will read within the Script Task code and show it in the Message Box.
Our source code that reads the FilePath and displays it in the Message Box.
Now just execute the package and we will see it looping through all the XML files with the folder specified.
Hope it helps..
Nishant Rana's Weblog 