Call web page page from SSIS

Here’s how to hit a web page from SSIS.

Create a Script task.

At the top of the Script, add this directive:

using System.Net;

In the Main() function, add the following:

String url = "https://glutenfreesql.wordpress.com";
WebRequest request = null;
WebResponse response = null;

try
{
request = HttpWebRequest.Create(url);
request.Timeout = 7200000;
response = request.GetResponse();
response.Close();
}
catch
{
  Dts.TaskResult = (int)ScriptResults.Failure;
}

My page was for unzipping a file, so I set the Timeout to two hours. With that line omitted, it would default to 100 seconds. Set it to whatever value makes sense for you.  The value is in milliseconds.

In the catch clause, it sets the result to failure. In that case, the SSIS task will fail, if the page did not return a response within the timeout period.

In order to read the results of that page for errors, here is what I am using.  If an error occurred, then the SSIS package can report failure if we use “Dts.TaskResult = (int)ScriptResults.Failure;”  My code mostly came from this MSDN page, and I added the part about looking for the word “Error”.

using System.IO;
using System.Text;

...

String url = "https://glutenfreesql.wordpress.com";
WebRequest request = null;
WebResponse response = null;
 
try
{
request = HttpWebRequest.Create(url);
request.Timeout = 7200000;
response = request.GetResponse();

Stream receiveStream = response.GetResponseStream();
Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
StreamReader readStream = new StreamReader(receiveStream, encode);

Char[] read = new Char[256];
int count = readStream.Read(read, 0, 256);

while (count > 0)
{
String str = new String(read, 0, count);
if (str.Contains("Error Exec") || str.Contains("Query") )
{
Dts.TaskResult = (int)ScriptResults.Failure;
}

count = readStream.Read(read, 0, count);

response.Close();
}
catch
{
  Dts.TaskResult = (int)ScriptResults.Failure;
}
Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s