Using Script Component to specify input value to OLE DB Source Component in SSIS

Recently we had a requirement to write an SSIS package that will fetch the data from SQL Server and will insert it (or create records) into Dynamics 365 CE.

For connecting and fetching data from SQL Server, we were using OLE DB Source Component with data access mode as SQL Command which had one parameter, whose value we had to fetch from Dynamics 365 CE.

For passing the value to this parameter we had defined a variable within our Package named LastUpdateFromCRM.

Now as we wanted to set the variable value from one of the records in Dynamics 365 CE, we used KingswaySoft’s CDS/CRM Source Component Editor, that will return us one row using fetch xml + max rows returned property.

Then we added a script component to the output of the CDS/CRM Source Component.

Here double-clicking the Script Component and selecting Input Columns inside the editor, we can select the input columns that we want to use within the Script Editor.

Next, we selected Script section inside the editor, there we selected the user variable as Read Write variable (as we are setting its value inside our script) and clicked on Edit Script button to open the VSTA Project.

Within VSTA project, inside main.cs we defined a date time variable to hold value coming from the CRM Source Component i.e. the value of last update field.

Inside ProcessInputRow method, we then set the value of that variable and finally within Post Execute method we use it to set the value of our user-defined variable.

Now as we have specified the value to our LastUpdateFromCRM variable, its value was passed to the SQL Command of OLE DB Source component as intended.

Hope it helps..