SSIS will not warn if metadata is out-of-date for a Lookup

I ran into this problem today that didn't take a long time to resolve, but took me a little by surprise. Most of my SSIS packages use the Lookup transformation component to look up keys used in source systems and match them to the data warehouse. When SSIS packages fail at the Lookup component, the most common cause is that the source system's key does not exist in the data warehouse and must be added first. This time was different, however.

Creating an Azure SQL DB Firewall Rule from SSMS 2016 and Error 401

When connecting to an Azure SQL DB endpoint (somename.database.windows.net), your IP must be listed in that endpoint's (server's) firewall: If you're connecting to your Azure SQL DB from SQL Server Management Studio 2016 and your IP address is not in the list, SSMS will offer to add it: You'll need to sign in with a … Continue reading Creating an Azure SQL DB Firewall Rule from SSMS 2016 and Error 401

Performing a Silent Install of SQL Server Management Studio (2016)

With the release of SQL Server 2016, SQL Server Management Studio (SSMS) is now a stand-alone tool. It requires no licensing and can be downloaded without as much as registering. Because it is a separate download, it requires a separate installation. The computer labs I manage have SQL Server installed, including Management Studio. Prior to … Continue reading Performing a Silent Install of SQL Server Management Studio (2016)

T-SQL window functions talk summary and sample scripts

Yesterday, I spoke to the Steel City SQL User group on T-SQL window functions. Below is a short summary and links to the sample scripts and database. The T-SQL window functions came about in SQL Server 2005, but you really want SQL Server 2012, because many functions were added and also because support for PARTITION … Continue reading T-SQL window functions talk summary and sample scripts

SQL Saturday #342 in Mobile is tomorrow

Here is a summary of my session, at 3 PM in "Room 2" (that just sounds too easy...) Of course, you really want to sign up for the other speakers, but just maybe at 3 PM my session will appeal to you. P.S.: Bring your family to Mobile! Tomorrow they can get free admission to … Continue reading SQL Saturday #342 in Mobile is tomorrow

Removing an Upgraded Component from SSIS Designer Results in Error

While working with an SSIS package that uses a custom component (in this case the RegexClean transformation from Konesans) that was upgraded from SSIS 2008 to SSIS 2012, I ran into this error trying to delete it from the designer when I realized I couldn't work with it: "SSIS Designer does not allow this component … Continue reading Removing an Upgraded Component from SSIS Designer Results in Error

Copy or Duplicate an SSIS environment in the SSIS 2012 Catalog (SSISDB)

As part of my research for my Managing SSISDB talk, I came across an interesting script by Mike Davis. The script intends to copy an environment in the SSISDB. Mike does a good job explaining why this is useful. The SSIS Catalog does not provide a UI for that. Mike's script seems to work, but it has … Continue reading Copy or Duplicate an SSIS environment in the SSIS 2012 Catalog (SSISDB)