Working with SFTP sites in SSIS

The year is almost half over and I haven’t fished at all yet. I did no ice fishing this year and I haven’t gotten out in the first few weeks of the open water season either. Life has been busy, with a small increase in work responsiblities along with a large increase in family busyness. I hope to get out soon, and if so I will provide a fishing report. The spring was cold, so I expect a few weeks to pass before fishing really gets going this year anyway.

I often use SSIS for collection of files. My current employer pulls data from several different locations, and since my background is much more SQL based than programming based, I default to SSIS. One thing to note when working in SSIS is that while Integration Services has an ftp task, it doesn’t support pulling files from an sftp site. This can be confusing when you first try to use the ftp task to pull data from an sftp site. There is no explicit warning that you are using an incorrect component. You can adjust the port to the standard sftp port, but the connection to the site fails.

To get around this limitation, I use the scripting capabilities of WinSCP. WinSCP is a great tool – I love the UI for interacting with the ftp/sftp sites, the scripting language is intuitive and easy to use, and the options for more secure protocols have always met my needs.

To demonstrate how it works, I will show a package I set up to extract data and put it on an sftp site. The package has three components. The first is a data flow task which extracts the data from SQL Server and places it on the file system as a flat file. The second task is an Execute Process task which kicks off a batch file that runs the WinSCP script to transfer the file. The third task archives the exported file to an Archive folder. The process is set up to run daily, so each file has the date appended to its name.
20180515 SSIS Package
The batch file is quite simple and just kicks off the WinSCP scripting program with the pre-written script. Here is an example of such a batch file:

cd "Program Files (x86)\WinSCP" -script="C:\Project\3rd Party Vendor Unsubscribe Feed\lib\winscp ftp script.txt"

Finally, the WinSCP script is fairly simple too. First make sure to turn off any interactive prompts, since this will be run in an automated fashion. Then connect to the sftp site with the “open” command and place the file out there with the “put” command. (Similarly, to pull files you can use the “get” command). The WinSCP site has many great examples for writing scripts to work with ftp/sftp files and folders. The WinSCP scripting language is even robust enough to allow for variables, such as the %TIMESTAMP#yyyymmdd% one you can see in my script below.

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
open s -hostkey="ssh-rsa 2048 XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX"
put "C:\Project\3rd Party Vendor Unsubscribe Feed\Files\UnsubscribeEmails_%TIMESTAMP#yyyymmdd%.txt" "/subscriptions/unsub/"

Using this simple technique you can still use SSIS to transfer files to and from an sftp site.


Documentation – The ‘Hit-by-a-Bus’ Handbook

A huge part of success in fishing is preparation. Without careful preparation fishing will be slow, you can lose or break equipment, and you can even put yourself in danger. Several of my previous posts have detailed how preparing by looking at weather, depths, fishing reports, etc. can improve your fishing “luck”. Another part of preparation is inspecting your fishing equipment before use. I can remember at least three times where I’ve casted my lure right off the end of my line. It’s great to see a bomb cast really sail out there, but it’s not so great the moment you realize it’s no longer attached to the end of your line. Lastly, let’s not forget the thought of unhooking that deep hookset on a northern pike when you neglected to make sure your pliers was in your tackle box.
I was considering preparation this week after I read this great post by Monica Rathbun on how to survive as a Lone DBA. One of the great tips she has is to create a ‘Hit-by-the-Bus’ handbook. This would have all the information you need to pass on to the next DBA if you were to suddenly leave the job. With this documentation, the next DBA should be able to step right in without missing a beat. As I deeply considered my current environment, it became clear to me that, were I to suddenly leave my position, the next DBA would have a lot of questions that he or she would have to do some deep and time consuming investigation to answer. As such, I’ve decided to make a major commitment to improving my documentation. From a DBA perspective, this will include detailed documentation on:

  • Daily/Weekly/Monthly/Ad-Hoc tasks. How to do them and why they are done.
  • SQL Agent Jobs. What are they doing and how to troubleshoot if they break. How urgently do they need to be fixed?
  • SSIS packages. What are they doing at a quick glance? Stepping through an SSIS package to figure out what it is doing can be all kinds of frusterating.
  • Backups. What is our strategy? Where are backups going and how would I address various restore situations?

I believe this documentation will bring value both for myself (because my memory is not great) and for my successor, whenever that day may come.

Run an SSIS job from SSRS

An important part of being a responsible boater is to prevent the spread of invasive species. When pulling your boat out of the water, plants and animals can “hitch a ride” by attaching themselves to your boat or trailer or sitting in your boat’s livewell or bilge area. Then when you move to the next lake they will get off or out of your boat and begin reproducing. This can introduce species of plants and animals to other lakes where they don’t exist natively. The ecosystem of each lake can be delicate, and introducing a new species can severely impact the species that already thrive in the lake. As an example, zebra muscles are an invasive species that are wrecking havoc on the lakes in Southeastern Wisconsin. Zebra muscles look like tiny clams with stripes, and they attach themselves to anything (rocks, sand, plants) and filter particulates out of the water. This makes the water much clearer which impacts the predators, who will now need to find more effective ways to ambush their prey.
I was surprised to find a local lake closed due to an invasive plant species, Starry Stonewort. From what I’ve read, this plant grows so thickly that poison is ineffective in trying to destroy it. It also hardens almost like coral, and reduces the amount of area that fish can use to spawn. It is very important to check your boat and trailer after pulling out of the lake but before leaving the launch to make sure you are not carrying any aquatic hitchhikers.
SSIS is a great tool for many tasks, especially moving data into and out of SQL Server. However, this tool is not easily accessible to the majority of the members of many organizations. One good way to let these types of users move data around is to set up the SSIS packages to be run in an SSRS report. Unfortunately there are a few steps to get this set up, but once you have it up and running you can cut yourself out of a lot of simple but time consuming tasks by letting users load or extract their own data to use in Excel or anywhere else.
The first step is to create an SSIS package to do the actual movement of data. The input or output will usually have to have a standard naming convention and standard field configuration.
20150810 SSIS from SSRS SSIS package
Next, create a SQL Agent job that will run that SSIS package.
20150810 SSIS from SSRS SQL Agent Job
The next step is to create a stored procedure that will run that SQL Agent job.

-- =============================================
-- Author:		Gugg
-- Create date: 2015-08-10
-- Description:	Runs the SQL Agent job that imports the IQR files
-- =============================================



   EXEC msdb.dbo.sp_start_job  
    @job_name = N'AdHoc - DirectTech IQR Import'


Lastly, create the SSRS report that will use that stored procedure as a data source.
20150810 SSIS from SSRS SSRS Report
Now when the user clicks to view the report, the data will be successfully imported or exported into SQL Server.
20150810 SSIS from SSRS Report Icon
It can a good idea to make the SSIS package as robust as possible with error emails sent to those who you expect to be running the report, that way if the import or export fails, the user can figure out why (often a misnamed file or missing field) without having to get you involved.

Finding SSIS package passwords in SQL Agent jobs

It can be a daunting experience to try fishing a new lake. We can see what the lake looks like from above the water, but below there are innumerable structures and features to consider. Last summer I fished lake Winnebago for the first time with my pop and brother. Lake Winnebago is the 21st largest natural lake in the United States, covering about 215 square miles. As we motored out from the boat launch, the only thought was “where to start?” The vast lake was stretched out before us, but since we could only see the top of the water we had to use other clues to figure out where to start fishing.
It’s tough heading into the unknown. Earlier this year I started a new job that used SQL Server Integration Services (SSIS) jobs saved in the file system as steps in various SQL Agent jobs. This job was at a small shop where documentation was not well kept. The SSIS packages were password protected, but nobody knew the password! Fortunately I was able to find the passwords stored in the SQL Agent job steps using this SQL command:

FROM    msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE   sjs.subsystem = N'SSIS'

After running, I was able to find the /DECRYPT password part of the command, which I used to decrypt and view/edit the packages using BIDS.  In the image below, you can see the password is “ssis”.

20141229 SSIS Package Passwords

Winnebago turned out to be a fun lake to fish.  We were catching fish all day, and ended up with a good amount to take home, after throwing back all the nasty sheepshead.