Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Use SSIS or SQL Server Agent to post to HTTPS site Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
I have a SSIS package that modifies a vendor-generated .txt report. Now I need to automatically publish (post or push) this report to an external HTTPS website. I know the firewall holes are burned. I will have access to the user name and password shortly. But I don't have the foggiest notion of how to build in this push to the website.

Do I use a .bat or .cmd file called by SQL Server Agent? Is there something in SSIS that can do the pushing for me?

Any assistance would be greatly appreciated. My Google-Fu gets me plenty of links on how to set up a server for receiving HTTPS files, but nothing on sending them.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562252
Posted Wednesday, April 16, 2014 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 246, Visits: 661
SSIS File System Task:

http://technet.microsoft.com/en-us/library/ms140185(v=sql.105).aspx
Post #1562277
Posted Wednesday, April 16, 2014 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
Wow. I completely missed that one.

But what type of connection manager would I use for the destination?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562278
Posted Wednesday, April 16, 2014 7:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 246, Visits: 661
You can use a file connection manager, or build one dynamically using variables and\or expressions. Dynamic is good for things like adding the time to a file, such as

dailyfile20140414
dailefile20140415
...
Post #1562288
Posted Wednesday, April 16, 2014 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
That won't work. A file connection manager does not give me the option of using a user name and password to connect to the HTTPS server.

Thanks for the suggestion, though.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562296
Posted Wednesday, April 16, 2014 7:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 885, Visits: 2,468
File system task is used to manipulate files similar to how you might with Windows Explorer. In this instance that is not going to help you move the file to an external HTTPS site.

I would wait and see the type of access the username and password are going to offer. If the upload method provided by the vendor is simply a form entry it will be difficult to automate that with SSIS.

If the vendor also offers secure FTP you could use SSIS to do this but would require a third party add-on or script since SSIS FTP task does not support security FTP. The other option might be sending this to a web service through an XML format, there is a task in SSIS for doing this.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1562298
Posted Wednesday, April 16, 2014 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
Shawn Melton (4/16/2014)
If the vendor also offers secure FTP you could use SSIS to do this but would require a third party add-on or script since SSIS FTP task does not support security FTP. The other option might be sending this to a web service through an XML format, there is a task in SSIS for doing this.


The vendor really really really wants me to use HTTPS and is pushing back on SFTP as a last resort.

Webservice, huh...

I did find this link to C# code (code at the bottom) that I might put in a script task, but I don't see a line where it's actually pushing a file. Just connecting. Though, I'm wondering if I could throw some sort of Stream writer (C# novice here) that would do the trick.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562306
Posted Wednesday, April 16, 2014 8:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 885, Visits: 2,468
So the HTTPS access they are providing is just a form entry?

Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1562327
Posted Wednesday, April 16, 2014 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
I'm assuming so. The link they point to in their help document is http://www.w3.org/TR/html4/interact/forms.html.

HelpDoc

For automatic submission, post the embedded file over HTTPS using multipart/Form-data (MIME) encoding.


It tells me to have my "automated program" log into the account, provide credentials, then gives me an http header format spec as well as

HelpDoc

The body of the http message must contain an embedded file in the required multipart encoded format (see below for details).


The details being the reference to the w3.org page and then a specific line terminator for each line in the file.

The part I keep getting hung up on is the "automated program" part of the document. What program should I be using and is there a way for me to do it using the SQL Server tools I have access to?

I'm sure I'm missing something obvious.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562339
Posted Wednesday, April 16, 2014 8:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 885, Visits: 2,468
Brandie Tarvin (4/16/2014)
The part I keep getting hung up on is the "automated program" part of the document. What program should I be using and is there a way for me to do it using the SQL Server tools I have access to?

I would probably opt to figure out scripting the upload of a file using PowerShell. You can then simply set this up as a step in a SQL Agent job. I have used it playing with downloading files but not uploading. I found this that shows the class you would use is WebClient.UploadFile.

You would also have to test to see if this supports using HTTPS, which I think it would.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1562345
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse