10/25/2016

Resolving Dts.Variables in Script Component of SSIS to call REST API

For a project, I was tasked with making a REST API call to send up some data using JSON, then receive the returned values and place into a SQL Server table.
 
So started out using a Script component.  And pieced together some code, to get the REST API call to work successfully.  In order to do that I first had to figure out the correct syntax.  I used the built in plug-in using Chrome.
 
 
Click the Launch button and another window appears:
 
 
 
After entering the URL for the REST API, I selected POST.
 
Then tweaked the JSON data until it processed success. So the REST API call was accessible.
 
Next, went into SSIS using Visual Studio 2015, added some code, including a Script component.  Then pieced together some code to send a JSON statement to the service using a hard coded JSON string.  And that worked.
 
Next, had to feed data into the Script component, using an Execute SQL script.
 
Next, added a For Each Loop component and set the result set to a variable: User:ResultSet
 
 
 
Then created 4 User variable to hold the contents of the result set.
 
Then within the For Each container, added a Script component, selecting "Source", not "Destination" nor "Transformation".  And attached a Destination table in SQL Server.
 
 
 
Then you set the Output expected Returned values within the Script component.
 
Then edit the script.
 
I got it to run success when hard coding the JSON string.  Of course we have to then set the thing to run using real data, flowing in from our Source data from our Execute SQL component. 
 
So we set our "ReadOnly" variables, not "Read/Write", added the 4 User variables and head back to the Edit Script again.
 
And in the code, we swap out our hard coded JSON statement with a well defined code substitution our 4 User:Variables.
 
But we ran into an issue.  It wasn't able to read our variables.
 
Error CS0234 The type or namespace name 'Variables' does not exist in the namespace 'Microsoft.SqlServer.Dts' (are you missing an assembly reference?)

I read some post online that suggested many possible solutions, none worked.  Then I remembered that you have to add the Microsoft.SQLServer.ManagedDTS.dll to the project, which I did, still threw an error.
  
Then searched in a few places, and created a Matrix, comparing the DLL values from the SQL Server 2016 box to the development box that has Visual Studio 2015:
 
 
You can see that the DLL could reside in multiple places, specifically the SDK directory and the GAC.  The 592 and 593 were the file sizes to I could identify the differences.
 
It turns out, the version in the GAC needs a different version so I overwrote it with a newer version.
 
Reopened Visual Studio:
 
string strStreet = this.Variables.street.ToString();
 

I changed the syntax slightly, and it resolved correctly and was able to flow a record through and it showed up in the database table as expected. 
 
So I opened up the SQL statement and removed the "top 1" and it ran entire way through without errors.
 
So it seems that the dll version must have corrected the issue, as well as having to change the syntax slightly for from:
 
Dts.Varialbles.["Street"].ToString().
 
to
 
this.Variables.Street.ToString(). 
 
That's my story and I'm sticking to it.

 

No comments:

Post a Comment

Root Cause Analysis