Invoke URL from SSIS?

Question

Invoke URL from SSIS?

Paul_Harvey

Hall of Fame

Points: 3822
More actions
March 21, 2012 at 4:06 am

#258097

As part of my data import, it has become a requirement to fire a URL for each "new" record, after the de-duplication process... ideally (but not necessarily) from within the same SSIS package. This will create the record in another system.
I have never attempted to do anything like this, and am not sure exactly where to start.
Each URL is unique for each "new" record. I am using a SQL Task to generate the URLs for each new record, and store them in a table.
So what I need is some method of invoking the URLs in a database table. Does anyone have a recommendation about how I can make a start on this?
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 1

I may not have been very clear on exactly what I need help with! My apologies.

I already have my data import process. This is a data warehousing solution that has been in operation for well over a year now.

The new requirement is to fire a URL (which ensures that new records are created in another system), is one which I would like to incorporate into the existing SSIS package that controls the data import.

As a starting point I have added a T_SQL task that builds the unique URLs for each of the new records. There are contained in a single column table. This table contains only a list of new URLS. It is my intention to clear this table down once the URLs have been invoked.

What I would like some advice on is the best way in which to read these URLs from my table, and have them "called".

Any advice would be well received.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Do you not have direct access to the system you're inserting rows in? I think it would be a lot more transparent, not to mention maintainable, to have your package run INSERT statements (or stored procedures that do the same) than to call URLs and attempt to handle any errors they may generate.

John

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 3

Hi John,

No I don't. This is a remote system. In truth this is a failsafe operation. We already have overnight full synchronisation with the remote system, but we have imports running throughout the day every 5 minutes or so. We would like to "fire" across new records (or actually a basic form of them) as they enter the system, then do the full sync overnight. There are some actions that will be taken by the remote system as soon as the new records hit.

Paul

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 4

In summary... I am not overly concerned about handling errors... I could look into error handling down the line... but initially I just want to call the URLs.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Paul

I don't know how you do that. There's probably someone here who does, but even so, you may be better off posting this on some sort of development forum.

John

Phil Parkin SSC Guru Points: 246695 More actions · Answer 6

Try this. Use a Script task and add the following two Usings:

using System.Net;

using System.IO;

Then change your Main sub - here's something I copied from elsewhere on the Web:

public void Main()

{

string url = "http://www.yahoo.com/";

WebRequest request = HttpWebRequest.Create(url);

WebResponse response = request.GetResponse();

StreamReader reader = new StreamReader(response.GetResponseStream());

string urlText = reader.ReadToEnd();

Dts.TaskResult = (int)ScriptResults.Success;

}

Might get you started.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 7

Thanks for the advice John. I thought it might simply be something that could be handled via T-SQL or some type of script task.

Obviously I can use Execute Process task to open IE at each URL in some sort of foreach loop... but closing IE would be more of a problem, and I had hoped for something more elegant!

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 8

Paul_Harvey

Hall of Fame

Points: 3822

March 21, 2012 at 9:43 am

#1462245

Thanks Phil!

I'll give it a go. 🙂

Lowell SSC Guru Points: 323504 More actions · Answer 9

Paul can you add a CLR?

if you can, it is REALLY easy.

here's an example call

select dbo.CLR_WebQuery('http://www.YourDomain.com?f=QueryString')

and the code for the CLR, which i just tested was incredibly simple:

'Imports System.Net

<Microsoft.SqlServer.Server.SqlFunction()>

Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' varchar(8000) = SqlString, SqlChars=varchar(max)

Dim request As WebRequest = HttpWebRequest.Create(URL)

Using response As WebResponse = request.GetResponse()

Using dataStream As Stream = response.GetResponseStream()

Using reader As New StreamReader(dataStream)

Dim responseFromServer As String = reader.ReadToEnd()

Return New SqlChars(New SqlString(responseFromServer))

End Using

request = Nothing

End Function

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 10

oops my error, i thought this was via TSQL and not in SSIS...sorry folks.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 11

Thanks Lowell

I am hoping to call via SSIS, but I could use the Execute SQL task within SSIS to run T-SQL. However I don't know anything about CLR... but intend to read about it now.

I seem to be getting somewhere using Phil's method tho, so thanks.

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 12

Thanks to all who replied, particularly Phil, who's method has worked for me.

I added the code to a Script Component, and created it as a Transformation, using an OLEDB Data Source to fetch the rows from the database.

I then replaced

string url = "http://www.google.com"

with

string url = row.url.ToString()

However this caused some issues. I think the special characters in the URL (eg ://) were somehow being changed (encoded?). as an error occurred with the "format of the URI".

I changed tactic then by building the URL as part of the script, rather than creating it first.

I now pass first name, last name and email from the database and build the URL:

string url = "https://www.example.com/API/atrib1=" + row.firstname.ToString() + "attrib2=" + row.lastname etc.

This now works very well.

I am sure I will have further hurdles in this process (error handling of course!) but I am now getting somewhere.

Thanks very much.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 13

Thanks to all who replied, particularly Phil, who's method has worked for me.

😎

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

maheshprajapati SSC Rookie Points: 35 More actions · Answer 14

maheshprajapati

SSC Rookie

Points: 35

April 28, 2014 at 10:29 am

#1709226

It worked!