SSIS and your SFTP options

The SQL Server intergration Services (SSIS) has always had a FTP task but it is odd that it has never had an SFTP task (up to 2017). SFTP is the Secure File Transfer Protocol using the Secure Shell SSH. There are third party SFTP tasks that can be deployed to your Server running SSIS but the simplest way to use SFTP is through an execute process [...] Read more

Integration Task to gather API data using PowerShell

At the time of writing there isn't a SQL Server Integration Services (SSIS) task that will collect JSON or XML formatted data directly from an API call. There are various add-ons and products that are available however, it is straightforward to achieve this using PowerShell and since you are likely to use PowerShell to automate other tasks on [...] Read more

Zip and unzip tasks in SSIS

I've been doing a lot of work with SQL Server Integration Services (SSIS) downloading zip files and unzipping the data. There isn't a "native" zip task in SSIS at the time of writing but I've always used the Execute Query task with 7Zip since back in my SQL 2005 days (7zip has been around since 1999!). I'll go through that in more detail later in [...] Read more

Using Powershell in SSIS.

Powershell is a great tool for managing SQL Server, Azure and all things Microsoft (okay - most things Microsoft, I still haven't been able to automate many Reporting Services tasks). If you are a DBA managing SQL Server then I strongly recommend that you look at dbatools.io which will supercharge your Powershell toolset. Powershell can allow you [...] Read more