SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use SSIS or SQL Server Agent to post to HTTPS site


Use SSIS or SQL Server Agent to post to HTTPS site

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14494 Visits: 8989
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
tim_harkin
tim_harkin
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 920
SSIS File System Task:

http://technet.microsoft.com/en-us/library/ms140185(v=sql.105).aspx
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14494 Visits: 8989
Wow. I completely missed that one.

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

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
tim_harkin
tim_harkin
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 920
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
...
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14494 Visits: 8989
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2225 Visits: 3512
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14494 Visits: 8989
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2225 Visits: 3512
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14494 Visits: 8989
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2225 Visits: 3512
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search