April 16, 2014 at 6:09 am
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.
April 16, 2014 at 7:07 am
SSIS File System Task:
http://technet.microsoft.com/en-us/library/ms140185(v=sql.105).aspx
April 16, 2014 at 7:11 am
Wow. I completely missed that one.
But what type of connection manager would I use for the destination?
April 16, 2014 at 7:33 am
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
...
April 16, 2014 at 7:48 am
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.
April 16, 2014 at 7:49 am
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
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 16, 2014 at 7:54 am
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.
April 16, 2014 at 8:15 am
So the HTTPS access they are providing is just a form entry?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 16, 2014 at 8:31 am
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.
April 16, 2014 at 8:50 am
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
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 16, 2014 at 8:54 am
Powershell will do this?
ROCK ON. I've been looking for an excuse to learn this. Thanks for the links and the reference.
April 16, 2014 at 9:00 am
If you can do it in C# it should be possible, and more likely easier to write, in PowerShell.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 16, 2014 at 9:03 am
GAH.
Is this Powershell error normal?
Microsoft Team Foundation Server 2008 Power Tools\TFSSnapin.ps1 cannot be loaded because running scripts is disabled on this system.
It gives me a link for execution polices on Microsoft's servers, but at first glance, nothing that actually explains the error.
April 16, 2014 at 9:17 am
Brandie Tarvin (4/16/2014)
GAH.Is this Powershell error normal?
Microsoft Team Foundation Server 2008 Power Tools\TFSSnapin.ps1 cannot be loaded because running scripts is disabled on this system.
It gives me a link for execution polices on Microsoft's servers, but at first glance, nothing that actually explains the error.
Default on Windows (desktop and server) is for scripts to not be executed. You have to set this either at the domain level or per machine. This is the security by default.
The general guideline is setting it to RemoteSigned. This provides some level of security without causing to much hassle and requiring every little script to be signed.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 16, 2014 at 9:32 am
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply