Integration Services

Using Powershell to read and modify SSIS packages

Introduction

In these days developing in SSIS, you should be using a generation framework (BIML or EzApi. The benefits era a bit obvious:

  1. Your packages will have consistent layout and logic
  2. It’s easy and fast to apply the same change to all packages
  3. No more mouse programming (almost :))
  4. It’s the right way to do metadata driven ETL

The problem

In a client I had a project with 97 packages. Each one of them started as copy of the first one. The request was to change the value of a single variable on all of them.

How to access SFTP with SSIS using SSH.NET

Introduction

For the ones unware of SFTP (SSH File Transfer Protocol), it is a secure channel with SSH2 encryption established between two machines. You can think of it as FTP with encryption and a few more differences. For more information about this you can read this blog post.

There are some alternatives to get this done, ones paid and others free. I will focus on the use of the excellent SSH.NET because I couldn’t find a good guide to get it to work with SSIS. It’s worth mencioning WinSCP as well, and the excellent guide to make it work with Integration Services.