As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.
Continue reading
For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one command. Continue reading
Background
With the advent of the Integration Services Catalog, parameters for SSIS packages no longer need to be controlled using a configuration file (usually with the extension of ‘.dtsconfig’). Now the parameters can be stored within the Integration Services Catalog (and therefore, within the SSISDB database) and altered far more easily within an agent job.
Background
Untested backups. An awful lot is said about taking regular backups, although an inordinate number of people out there appear to ignore it. What is less talked about is actually checking that the backups are usable – it’s no good having a series of backups for that fateful day to discover that when they’re needed that they’re useless.
Continue reading
Background
In previous articles I have covered the creation of the SSISDB and adding the SSIS package. In this article I show how to move an SSIS package from one server’s SSISDB to another server. Continue reading
The full message will also contain “The version number cannot be greater than the current version number”.
Behaviour
I have an SSIS package installed in an SSISDB which is on a server that run SQL Server 2012 (server A) and it needed to be copied to another 2012 instance (server B). My workstation has SQL Server 2014 and I connect to the 2012 instance on the target server using my workstation instance.
I connected from my workstation to Server A and exported the SSIS package into the required ‘ispac’ file. Then changed my connection to point to Server B and imported the ispac file. So for both the export and import I was using my SQL Server 2014 to connect to SQL Server 2012 instances.
The export and import run without any issues, but when the associated job executed that ran the SSIS package on Server B, it failed. In this case the important part of the error report was:
“Package migration from version 8 to version 6 failed with error 0xC001700a. the version number in the package is not valid. The version number cannot be greater than the current version number.”
Cause
Exporting the SSIS package by using SQL Server 2014 (even though it is connecting to a 2012 instance) appears to have changed the ‘PackageFormatVersion’ property within the dtsx file, form ‘6’ to ‘8’. You can see this by opening the dtsx file in an editor such as Notepad++. Editing that value back to ‘6’ merely corrupts the package, so there are probably further changes made within the file.
Solution
Connect to the required instance using SQL Server Management Studio (SSMS) that matches that version in order to complete this export/import. In my case I could remote onto the required servers and use the SSMS for SQL Server 2012 that was installed there. The export and import worked as expected and this time the SSIS package ran without issue.
In the previous article I covered the creation of the SSISDB – a new feature within SQL Server 2012 that is used to store all of the required information for an SSIS package.
This article will cover the creation of a basic SSIS package that will require parameters to move data from a table on a database from one server to another. This package will be stored in the SSISDB and a job created to execute it.
Continue reading
Although introduced in SQL 2012 I’ve never encountered this feature before, as all sites where I have previously worked have started with much earlier versions, and the old habits of implementing SSIS packages and jobs have just continued. Continue reading
Background
On a Dev server I have used Visual Studio 2010 to build and test an SSIS package. Having completed all of my testing I have now created an Agent task with the SQL Server instance on the same box, that should execute the dtsx package.
However, when it attempts to execute it fails and I see the error message:
“To run a SSIS package outside of SQL Server Data Tools you must install Move file to Archive of Integration Services or higher.”
In this case ‘Move file to Archive’ is the name of a task within the SSIS package, which is of course, moving a file to an archive folder.
Not the most helpful error message.
Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like to think it wasn’t but, that is how it looks. Continue reading
Notes on SQL 