Primary Key With Non Clustered Index

28 Apr

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

Advertisements

The Joys of @@IDENTITY, Scope_Identity and IDENT_CURRENT

31 Mar

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

SSISDB and Catalog – Part 4 – Parameters

21 Mar

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.

Continue reading

Checking SQL Backups Automatically

25 Jan

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

SSISDB and Catalog – Part 3 – Copying a Package Between Servers

1 Dec

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

SSISDB Error “The version number of the package is not valid”

23 Sep

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.

SSISDB and Catalog – Part 2 – Adding the SSIS Package

13 Sep

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