How to use HTTP Connection Manager in Script Component? (Not Script Task)

Question

I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit rows into the Data Flow pipeline.

http://www.sqlis.com/sqlis/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

My target platform is SQL Server 2008 and therefore C#. The MSDN documentation gives examples of File and SQL Connection Managers but not HTTP ones.

https://msdn.microsoft.com/en-us/library/ms136060%28v=sql.100%29.aspx

The specific problem is that I can NOT figure out why there's no HttpClientConnection constructor in my current context. The MSDN documentation of that class does not seem to apply in the case of Script Components and translating this to something useful is apparently beyond me.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.httpclientconnection.httpclientconnection%28v=sql.100%29.aspx

My non-working code looks like this -

using System; 
using System.Data; 
using System.Text; 
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; 
using Microsoft.SqlServer.Dts.Runtime.Wrapper; 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] 
public class ScriptMain : UserComponent 
{ 
 public override void AcquireConnections() 
 { 
 IDTSConnectionManager100 connMgr = this.Connections.MyWebServer; 
 } 
 public override void PreExecute() 
 { 
 base.PreExecute(); 
 HttpClientConnection clientConn = new HttpClientConnection(connMgr)); 
 Byte[] buffer = clientConn.DownloadData(); 
 String Document = Encoding.ASCII.GetString(buffer); 
 } 

What am I missing?

• billinkc: I'll try to look at this tonight but take a peek at how Matt handles an OLE DB CM Might need to take a similar approach
• Jeff Sacksteder: I've seen plenty of examples with DB connections- HTTP Connection Managers are apparently the odd man out.
• Jeff Sacksteder: I got nothing. Apparently I'm the first person to ever do this.
• billinkc: While I beat my head against some solid surfaces to get this working, what are you trying to do? I've done a bit with SSIS, used web services, etc but haven't gone this route to do things. If nothing else, the more we talk, the less apt I am to get distracted by squirrels
• Jeff Sacksteder: The JSON src component on Codeplex doesn't install correctly, so until I get that working I thought I'd just use a script component and a http connection manager.

Answer 1

i know it's been a while since the question but i found a possible solution.

This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx

The portion of code that we are interested is this:

IDTSConnectionManager100 connMgr;
HttpClientConnection100 hcc;
IDTSComponentMetaData100 compMetadata = this.ComponentMetaData; //Just for output purposes. 
connMgr = this.Connections.MyWebServer;
hcc = (HttpClientConnection100)cm.AcquireConnection(null);
compMetadata.FireInformation(1, "Message: ", "URL: " + hcc.ServerURL + " User: " + hcc.ServerUserName + " Pwd: " + hcc.GetServerPassword(), "", 0, ref fail);

Answer 2

this is also working query.

IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));
//Read data from table or view to data table
string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
// string query = "Select * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);
DataTable dtExcelData = new DataTable();
adapter.Fill(dtExcelData);
myADONETConnection.Close();