When ingesting files in SSIS via Flat File Connections, a consistent format is key. Sometimes that isn’t the case. Here we’ll look at an example where the carriage return (CR, \r) may or may not be included in the file. The problem A file format can change as easy as tweaking the file in an editor […]
When constructing or investigating a SSIS package we can run into a variety of issues. To help resolve there are various techniques which can be used to troubleshoot the package. Whilst we have the Progress tab for the package to tell us what’s happened during execution, it’s usually more effective to debug packages in flight. […]
Package configuration for SSIS packages allows us to store configuration for the packages in a variety of locations. One of those which can be particularly beneficial – database configuration. Here we’ll look at 3 benefits to leverage when using database configuration: If you wanted a quick refresh, I’ve previously looked at Package Configuration and how we set […]
Case sensitivity in data can trip us up unknowingly and Integration Services is no exception. Depending on the components we’re using we may notice different behaviour. Without understanding the way case sensitivity is treated within SSIS packages, we may see unexpected results with string comparisons. Let’s clear things up and dive into a few examples […]
When handing data we can make use of temporary tables to aid with separation or performance. However, they don’t always play nice with Integration Services packages. If we set a source to call a procedure returning the contents of a temporary table we’ll see an error like below: The process is unable to determine the […]
Last week we looked at using the Azure Feature Pack for SSIS to interface with Azure Storage. Today we’re going to solve a couple of the common issues I’ve seen when using this pack. Bad or timed out requests You can set up a connection to the storage and it tests fine, but when you come to […]
Migrating to the cloud can be disruptive to existing processes. Moving storage to Azure isn’t a simple configuration change for SSIS packages. SSIS doesn’t have native connections for Azure. That doesn’t mean we need to completely re-engineer the process or change technology though. How can we take the simple package below and move to using […]
Integration workflows will typically involve handling files. As part of that we’ll need to move them around, for example moving into an archive directory. The File System Task component can be used for a variety of operations such as creating directories or copying files. Here we’re going to look at two specific operations to help […]
Configuring SSIS projects or packages can necessitate parametering information which may include sensitive values such as authentication details. Parameters are stored as plain text in the database by default. We’ll demonstrate how to protect these values using Sensitive parameters. The problem With a SSIS project deployed, our parameters are stored in plain text in the […]
Yep it’s more SSIS again this week. Here we’ll be looking at using Environment configuration within the SSIS catalog. This allows sets of parameters to be defined and used across multiple projects and packages which share common values. This approach can either be used as a central point for configuration, or you could use multiple […]
Andy Brownsword 