Calling APIs and Stored Procedures From SSIS

The script tasks in SSIS can be a bit limited in what they allow you to do as NuGet packages can’t be used and not all class libraries seem to be available. Given that, below is my code for calling an external API and logging it’s results to a SQL database using a stored procedure.

This was done in SSIS in SQL 2012 which uses Visual Studio 2008, the script task is set as a destination from a SQL source that points to a table, this table outputs MobileNumber and OrderReferenceNumber.

Rather than hardcoding the connection string into the script and ADO.NET connection should be set up to the database where the stored procedure to be called is, this then needs to be added in the “Connection Mangers” tab of the script properties, in the below case this is called “DestinationConnection”. This connection will then be available for use in the “Connections” class.

The body of this request is XML stored in the static class “Static.RequestTemplate”, this is a bit of a dirty hack but as it’s only for one call I’m not too offended by it.

public override void Mobile_ProcessInputRow(MobileBuffer Row)
{
	string username = Variables.apiusername;
	string password = Variables.apipassword;
	string uri = Variables.apiuri;
	string requestTemplate = Static.RequestTemplate;
	requestTemplate = requestTemplate.Replace("{MobileNumber}", Row.MobileNumber);
	requestTemplate = requestTemplate.Replace("{OrderReferenceNumber}", Row.OrderReferenceNumber.ToString());
	
	using (WebClient client = new WebClient())
	{
		client.Headers[HttpRequestHeader.ContentType] = "text/xml";
		client.Credentials = new NetworkCredential(username, password);
		
		XmlSerializer serializer = new XmlSerializer(typeof(SavePropertiesResponse));
		
		using (TextReader reader = new StringReader(client.UploadString(uri, requestTemplate)))
		{
			SavePropertiesResponse result = (SavePropertiesResponse)serializer.Deserialize(reader);
			
			using (SqlConnection connection = (SqlConnection)Connections.DestinationConnection.AcquireConnection(null))
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandText = "sp__insert_api_history";
				command.CommandType = CommandType.StoredProcedure;
				command.Parameters.AddWithValue("@mobile_number", Row.MobileNumber);
				command.Parameters.AddWithValue("@order_reference_number", Row.OrderReferenceNumber);
				command.ExecuteNonQuery();
			}
		}
	}
}

If using a plain script task rather than a Source/Destination/Transformation one in a Data Flow the connection manager tab isn’t available and the connection needs to be accessed by name like so.

using (SqlConnection connection = (SqlConnection)
(Dts.Connections["DestinationConnection"].AcquireConnection(Dts.Transaction) as SqlConnection)
{
	SqlCommand command = new SqlCommand();
	command.Connection = connection;
	command.CommandText = "sp__insert_attribute_definition";
	command.CommandType = CommandType.StoredProcedure;
	
	command.Parameters.AddWithValue("@file_fk", fileId);
	
	attributeId = (int)command.ExecuteScalar();

}

Dts.TaskResult = (int)ScriptResults.Success;