A Fine Slice Of SQL Server
Andy Brownsword fixes a pair of issues:
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.
Read on for the problem and solution for each.
Comments closedRod Edwards continues a series on Always Encrypted:
So now, lets see how it plays with another one of those common toolsets that you may use alongside your Encrypted data. In this post, i’ll be talking about accessing and importing data using SSIS, nothing fancy, just reading data from an Excel sheet, and piping into our Always Encrypted table, encrypting as we go.
I’m not saying to use Excel for housing confidential data either!… as no one does that…oh no, not anywhere, ever….</sarcasm>.
As previously, this focuses on using Azure Key Vault for securing Encryption keys required.
Considering that all corporate data is in Excel someplace (some variant of which may eventually become Feasel’s Second Law), of course that sensitive and confidential data will be in a plain Excel file that people e-mail around.
Comments closedAndy Brownsword shuffles things around:
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 shuffle files around: Move and Rename.
Read on to see how it works and how you can turn this into an archival process for files.
Comments closed…when one of our earliest SSIS framework customers reached out to share – in the very nicest way possible – that Fail Application on Package Failure in our SSIS framework was “not working.” My response was, “Great Scott!”
The customer is running SQL Server 2016. That’s fine for EDNA’s SSIS frameworks because we have framework versions that work with several versions of SSIS and SQL Server from 2012 forward. I maintain a collection of virtual servers built using the tools available for each version, including the operating systems and versions of Visual Studio, SSIS, and SQL Server.
Read on for the context, the story, and what Andy was able to do about it.
Comments closedGreg Low does a bit of parsing:
There was a question on the Q&A forums today, asking how to read data using SSIS, when it’s in this format:
|Col1| |Col2|Col3|Col|
|101| |A|21|DC|One of the concerns was that there was a leading pipe. This is not a problem. When you have data like that, and you set | as the delimiter, because there are 6 delimiters, then there are 7 columns output.
Read on for Greg’s answer. This is a big part of why I recommend people to use Parquet (or ORC, if you’re an iconoclast like I am): not needing to deal with whatever silly, arbitrary file formats people come up with.
Comments closedAndy Leonard fills us in on what’s happening in the world of Integration Services:
I’m not sure why the decision was made to “fork” development and maintenance of the Integration Services extension for Visual Studio 2022. Based upon experience with SSIS, Visual Studio, and the Microsoft SSIS team, I am confident there’s a very good reason. As one can glean from my appointment screenshot above, I save the links to both pages. The links are:
SSIS Pre-2022: https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects&ssr=false#overview
SSIS 2022: https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices
Read on to see what’s new in both of those projects.
Comments closedAndy Brownsword doesn’t just leave passwords in plaintext:
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.
Read on to learn how to make an SSIS project parameter sensitive, as well as how to use them afterward.
Comments closedAndy Brownsword continues a series on SSIS:
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 configurations for the same packages.
Read on for some examples of how you might use them, as well as the process to create one.
Comments closedAndy Brownsword continues diving into SSIS projects:
Last week we looked at project parameters in SSIS to help us configure our projects. This can help us to tailor the project to the package its in for example. Here we’ll look at using Configurations within Visual Studio to manage those parameters for multiple environments.
Read the whole thing.
Comments closedLast week we looked at configuring SSIS packages using package configuration. This week we’ll look at another approach for configuration: Project Configuration
Project Configuration is the standard way to configure projects. This took over from the legacy approach which we looked at last week. This method allows us to share common parameters and connections across an entire project to help managing a number of packages more efficient.
Read on to see how it works.
Comments closed