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..

Advertisements

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

3 thoughts on “Using Script Component to specify input value to OLE DB Source Component in SSIS”

Please share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.