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

1 Dec old dog

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 dog_horse

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

SSISDB and Catalog – Part 1 – Creating the DB

7 Sep newtricks

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

SSIS Error – “To run a SSIS package outside of SQL Server Data Tools you must install…”

25 Jul cute-but-not-helpful

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.

Investigations

Running the installation of SQL server (or executing a Service Pack installation), up to the point where it shows what is already installed shows that the Integrated Services shared components are not installed:

SSIS_01

On this machine, although SQL server is installed, the shared components required for Integration Services are not. In this situation it is still possible to run very simple SSIS packages, because ‘dtsexec.exe’ is installed. This explains why I can see an Agent job that runs a package for running backups (created by the Maintenance Plan Wizard) but cannot execute my package.

Solution

Rerunning the installation confirms the absence of Integration Services components:

SSIS_02

I installed the required components and then confirmed their presence afterwards:

SSIS_03

Now the SSIS package executes successfully.

 

LDAP – Using SQL to get Active Directory details – Basic Examples

14 Jul

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

Partitioned Views

17 Dec PayCheck - from www.moviescreenshots.blogspot.com

Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.

What is a Partitioned View?

Where data has been partitioned horizontally (across several tables), a partitioned view can show a unified view of these tables and can also make any data selections only from the required table, without accessing all tables that the view comprises of. The tables can be in one database, several databases on the same server or on databases on linked servers.

Creating the Test Environment

For this, I have three databases:
TestDB and SecondTestDB, which are on the ‘main’ server.
ThirdTestdb which is on a linked server, named ‘ARCHIVEWAREHOUSE’.

Listing 1: Tables for ‘main’ server

USE TestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

USE SecondTestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

Listing 2: Tables for the linked server

USE ThirdTestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

Listing 3: Create test data on ‘main’ server

USE TestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 1, 'WIDG-001', 10),
		( 1, 'WIDG-005', 21),
		( 1, 'WIDG-007', 0),
		( 1, 'WIDG-008', 6),
		( 1, 'WIDG-009', 9);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 2, 'WIDG-001', 104),
		( 2, 'WIDG-015', 211),
		( 2, 'WIDG-017', 90),
		( 2, 'WIDG-048', 78),
		( 2, 'WIDG-079', 7);

GO

USE SecondTestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 3, 'WIDG-001', 5),
		( 3, 'WIDG-006', 77),
		( 3, 'WIDG-007', 67),
		( 3, 'WIDG-008', 9),
		( 3, 'WIDG-009', 24);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 4, 'WIDG-003', 22),
		( 4, 'WIDG-014', 85),
		( 4, 'WIDG-017', 45),
		( 4, 'WIDG-048', 6),
		( 4, 'WIDG-079', 89);

GO

Listing 4: Create Test Data on Linked Server

USE ThirdTestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 5, 'WIDG-122', 10),
		( 5, 'WIDG-135', 21),
		( 5, 'WIDG-155', 0),
		( 5, 'WIDG-157', 6),
		( 5, 'WIDG-177', 9);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 6, 'WIDG-255', 104),
		( 6, 'WIDG-257', 211),
		( 6, 'WIDG-266', 90),
		( 6, 'WIDG-298', 78),
		( 6, 'WIDG-299', 7);

GO

Six tables spread across three databases, all with identical structure. The data is spread across these tables, with the value of ‘WarehouseID’ controlling which table the row is stored within. At this point there is no control to prevent a row being inserted into the ‘wrong’ table, but that will be developed as the example continues.

Listing 5: Create View on ‘main’ server

USE TestDB;
GO

CREATE VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
);
GO

Using the view

Setting STATISTICS IO ON and showing the actual execution plan, shows that in its current structure the view will access all tables, regardless of whether or not a filter is applied.

Figure 1: SELECT results without a filter

Partititioned_Views_02

Figure 2: Statistics and query plan without a filter

Partititioned_Views_01

Figure 3: SELECT results with a filter

Partititioned_Views_03

Figure 4: Statistics and query plan with a filter

Partititioned_Views_04

With this configuration, this is just a normal view and makes no distinction between the various tables it is accessing. Figure 2 and Figure 4 show no difference in the tables accessed, with or without the filter being applied. At this stage this is not a partitioned view.
To change this to a partitioned view actually requires changes to the source tables.

Creating the partitioned view

With the data structure used here, the column ‘WarehouseID’ is being used to signify which table the various rows should be in. To ensure that this actually works a constraint needs to be applied to the column. Once the constraint is used, if the view is filtered using the column that has the constraint then it will only use the appropriate table, based upon the filter value.

Listing 6: Create the constraint on the ‘main’ server

USE TestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 1);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 2);
GO

USE SecondTestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 3);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 4);
GO

Listing 7: Create the constraint on the linked server

USE ThirdTestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 5);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 6);
GO

Repeating the queries used earlier shows that the view focuses resources on the required table only.

Figure 5: Statistics and query plan with a filter and constraints created

Partititioned_Views_06

The work required by the query has now been reduced substantially.

Of course, to some degree it must still be accessing the other table – to check the metadata if nothing else. This can easily be proved by taking ‘SecondTestDB’ offline and querying the view again.

Figure 6: Querying the view with a database offline

Partititioned_Views_05

Even though no rows are required from the table in the database ‘SecondTestDB’ an error has been raised from the view, showing that tables it does not need to select from still needs to be available.

Adding a table with a different structure

Because this is a view, it is possible to add a table to this that does not have an identical structure. However, it must have the same column for the constraint, otherwise the view will access all of the tables that it has been constructed from, ignoring the constraint control created by Listings 6 and 7.

Listing 7: Adding a table with a different format to TestDB

USE [TestDB]
GO

CREATE TABLE [dbo].[Warehouse3](
	[WarehouseName] VARCHAR(10) NOT NULL,
	[WarehouseID] [tinyint] NOT NULL,
	[ProductCode_1] [varchar](20) NOT NULL,
	[ProductCode_2] [varchar](20) NOT NULL,
	[Quantity_In] [int] NOT NULL,
	[Quantity_Out] INT NOT NULL,
 CONSTRAINT [PK_Warehouse3] PRIMARY KEY CLUSTERED 
(
	[WarehouseName] ASC,
	[ProductCode_1] ASC,
	[ProductCode_2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[Warehouse3]  WITH CHECK ADD  CONSTRAINT [C_Warehouse3] CHECK  (([WarehouseID]=(7))) ---WarehouseID has to be present for the partitiion functionality
GO

Listing 8: Alter View

USE TestDB;
GO

ALTER VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
UNION ALL
SELECT [WarehouseID],
	   ltrim(RTRIM(ProductCode_1)) + ltrim(RTRIM(ProductCode_2)) AS [PartCode],
	   [Quantity_In] - [Quantity_Out] AS [Quantity]
FROM dbo.Warehouse3
);
GO

Listing 9: Add test data to ‘Warehouse3’ table

USE [TestDB]
GO

INSERT INTO [dbo].[Warehouse3]
           ([WarehouseName],[WarehouseID]
           ,[ProductCode_1]
           ,[ProductCode_2]
           ,[Quantity_In]
           ,[Quantity_Out])
     VALUES
           ('Seven'
		   ,7
           ,'ABCD'
           ,'EFGH'
           ,10
           ,3)
GO

With the addition of the ‘Warehouse3’ table, the partitioning behaviour still works, because the column ‘WarehouseID’ and the associated constraint are in the new table. The SELECT within the view has transformed the appearance of the data from that table to match the other tables.

Figure 7: View results with different structured source table

Partititioned_Views_07

So, the tables don’t have to be identical, when selecting data – as long as the tables have the same constraint and can be structured within the view to match the other tables (because of the ‘UNION ALL’).

Updating data via the view

To simplify this exercise, the remote server will be removed from the view, as well as the table that has a different structure (as detailed in Listing 7).

Listing 10: Alter view to one server with identical table structures

USE TestDB;
GO

ALTER VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
);
GO

This view now accesses the ‘Warehouse1’ and ‘Warehouse2’ tables from ‘TestDB’ and ‘SecondTestDB’. Twenty rows of data across four tables.

Figure 8: Altered view results

Partititioned_Views_08

Inserting a row of data via the view means that we aren’t concerned with selecting the ‘correct’ table (based upon the WarehouseID constraint). The view will insert into the correct table, based upon the value assigned to the column with the constraint.

Listing 11: Insert via the view

USE [TestDB]
GO

INSERT INTO [dbo].[StockLevels]
           ([WarehouseID]
           ,[PartCode]
           ,[Quantity])
     VALUES
           (2
           ,'WIDG_1234'
           ,33)
GO

Figure 9: SELECT to show the inserted row

Partititioned_Views_09

As shown in Figure 9, the row of data has been inserted into the table ‘Warehouse2’ of ‘TestDB’, which is the table with the constraint that checks for ‘WarehouseID’ having a value of 2.

Listing 12: UPDATE via the view

UPDATE [dbo].[StockLevels]
SET Quantity = 30
WHERE WarehouseID = 2
  AND Partcode = 'WIDG-079';

Figure 10: SELECT to show the updated view

Partititioned_Views_10

UPDATE and DELETE work in the same way.

Behind the scenes, one large difference between using SELECT on a partitioned view and INSERT/UPDATE/DELETE, is that the query accesses all of the tables – as shown in the query plan.

Figure 11: Query plan for a DELETE from a partitioned view

Partititioned_Views_11

The savings in processing when using SELECT are not evident when using INSERT/UPDATE/DELETE, although it removes the decision-making about which source table requires the action.