Link to home
Start Free TrialLog in
Avatar of rtay
rtayFlag for United States of America

asked on

SSIS HTTP Connection and Script Task to consume RESTful Services API

I am trying to connect to a RESTful API using SSIS.  I have an HTTP Connection Manager with the URL and the username and password to gain access to the API.  The problem comes when I am trying to insert variables into the URL to determine the date range.  I am using a Task Script component to try to by pass the url in the Connection Manager and insert the variables into the URL.  I am attaching the code I am using and the error I get.  The error only comes when I try to run the SSIS package.  

public void Main()
		{
            // Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
            object nativeObject = Dts.Connections["HTTP Connection Manager"].ConnectionString = ("https://api.apisource.com:8182/api/fleets/young/platforms/311305/data?descriptions=true&values=true&parameterId=10245&startTime=" + ((DateTime)Dts.Variables["startTime"].Value) + "&endTime=2015-09-09&maxCount=1000&startPort=-1&resolution=all");

            // Create a new HTTP client connection
            HttpClientConnection connection = new HttpClientConnection(nativeObject);

            // Download the file #1
            // Save the file from the connection manager to the local path specified
            string filename = "C:\\Temp\\Sample.txt";
            connection.DownloadFile(filename, true);

            // Confirm file is there
            if (File.Exists(filename))
            {
                MessageBox.Show(string.Format("File {0} has been downloaded.", filename));
            }


            // Download the file #2
            // Read the text file straight into memory
            byte[] buffer = connection.DownloadData();
            string data = Encoding.ASCII.GetString(buffer);

            // Display the file contents
            MessageBox.Show(data);


			
			Dts.TaskResult = (int)ScriptResults.Success;
		}
		}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of rtay
rtay
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rtay

ASKER

Thank you all!