Downloading SFTP Files with SSIS

Downloading SFTP Files with SSIS

If you’ve used SSIS for any significant amount of time, you’ve almost certainly run into this roadblock: You need to download a file stored on an SFTP (secure FTP) server, but SSIS does not natively support this operation. There is a built-in FTP task in SSIS, but it is very limited in its functionality and will not connect to an…

Synchronous Processing in the SSIS Script Component

Without a doubt, the most versatile data flow tool in the SSIS arsenal is the script component.  This control grants ETL developers complete control over the flow of data, leverages the full power of the .NET Framework namespaces, and can be used as a source, destination, or transformation in data flow operations.  Within the script logic, one can perform complex…

Building Better SSIS Packages

New Course: Building Better SSIS Packages–Denver and Boston

Earlier this week, I confirmed two full-day seminars I’ll be delivering in conjunction with SQL Saturday events this fall. I’ve got a brand new course entitled “Building Better SSIS Packages” which I’ll be delivering in Denver and Boston. Here’s a brief into to this course: SQL Server Integration Services is deceptively easy to use. With a few hours of instruction…

Row Numbers and Running Totals in SSIS

During data load or transformation processes, capturing a distinct row number for incoming data can be beneficial for the ETL process itself, as well as for use in the destination database. Having an arbitrary, incrementing row number assigned to each row can help to determine the order in which the rows of data were processed, and can provide a unique…


The SSIS Object Variable and Multiple Result Sets

In my most recent post in this series, I talked about how to use the SSIS object variable as an ADO recordset as a source in a data flow. By loading the result set of a query into this variable, the contents of the variable can be read by an SSIS script component and sent out through the SSIS pipeline….

Internet cable concept

Using the SSIS Object Variable as a Data Flow Source

Object variables in SSIS are incredibly versatile, allowing the storage of almost any type of data (even .NET objects). In my last post on this topic, I demonstrated how an SSIS object variable containing a .NET DataSet object could be used by the for each loop container as an iterator. In this post, I’ll continue the discussion by showing how…

Internet cable concept

Handling Mixed Format Data Files in SSIS

Some time back I wrote about how to use the script component to parse out ragged data files in SSIS. In this post, I’ll continue the discussion to describe how to handle mixed format data files – specifically, those with several different record types in each file. In a perfect world, a flat file will contain a single record type….

SSIS Training with Tim Mitchell

A full day with me, SSIS, and Edgar Allan Poe

When I think about Baltimore, I think about Edgar Allan Poe. Baltimore was Poe’s one-time home, and in fact there is a museum located in a home in which he once lived. I’ve been a Poe fan since high school, and am looking forward to seeing some of the Poe-related sights when I’m in town next month. Why am I…


Best of PASS Summit 2014: Building Bullet-Resistant SSIS Packages

This week, PASS announced the Best of PASS Summit 2014, a collection of the 10 highest-rated sessions from last fall’s PASS Summit in Seattle. I was pleasantly surprised to learn that my session, “Building Bullet-Resistant SSIS Packages”, was among these. All of these sessions (including lots of other content) are available for viewing on the PASS TV channel on YouTube….

Digital program code

Fix Inconsistent Line Terminators in SSIS

There is a flat file processing issue I’ve run into a number of times over the years, and it’s come up again several times recently. The issue relates to the line terminators used in data files. Occasionally, changes to the systems generating these data files, or perhaps even manual edits, can change the way the file marks the end of…