SSIS: Configuration

Nearly everything in the package can be configured.

The main items you will want to configure are the passwords in server connections along with the name of the server and possible the initial catalog (database).
To set up a configuration option right click the package main window and select package configurations.
The options available are to use an XML file, Registry Entry, Parent Package Variable or a SQL database.
In this example I have a database called dataimports which I am going to use. In dataimports I have created a schema for each batch of work I use. This means I can separate out all the SSIS logging and configuration and I can give users access through the schemas.

Once you select the SQL server option you will need to select a database connection to use and then a table. If you already have config tables the drop down box will list them – else you can create a new one. You will need to type in a configuration filter. This is a key word that will match up the configuration settings in the table with this package. The name should be easily identifiably like >> DataDownload.
When you click next you will see a list of configurable items. If this is a new package there will not be very many.

You should try to restrict the configured values to the ones really needed else you will make reading the configuration table difficult which can lead to errors.

A config file should be used to store secure data – like passwords. It should also be used to store values which will or could change. For example a package which emails a user upon completion could have the To field of the email task configured. This way if you need to change the recipient you can change the config table. If a server name is going to change as the package is deployed then store the server name in a config file. You shouldn’t store static or trivial data like the protection level of a database connection – this will probably never change; or the connection string to a database and then all the individual elements of the connection. If you are using Windows Authentication you may not need to store any connection data.

What it looks like in the table:

I have created a primary key on ConfigurationFeature and PackagePath – the table is created without any primary key.

Note for passwords the value stored in the config file will be *****. This is a place holder. You will need to change this value to the actual password and set security on the table to stop people reading it.

Jobs failing with the error – job can not start as it is suspended

If you look in the SQL Agent error logs it should record the problem - Subsystem %s could not be loaded.

When this happens you need to run this script:

use msdb;
delete from msdb.dbo.syssubsystems;
exec msdb.dbo.sp_verify_subsystems 1;
go

Now restart the SQL Agent

Reference

http://support.microsoft.com/?kbid=914171

Properties and other dialogs failing in SSMS

Error Message in SSMS



TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.
------------------------------

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------
There is no row at position 0. (System.Data)



Problem is to do with system table spt_values being deleted.

Run this script to repair - the location may be different on your server.
C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\install\u_tables.sql

Remove a File and Filegroup from Database

Before a filegroup can be removed all files associated with it need to be deleted.

Before a file can be deleted all objects associated with it need to be removed.

Just removing a file not a filegroup

If you have multiple files on a filegroup and you just want to get rid of one you can run this command.

DBCC ShrinkFile(logical_file_name,EmptyFile);

This will move any objects in the file onto the other files in the filegroup. You can then delete the file.

ALTER DATABASE DatabaseName REMOVE FILE logical_file_name;

Removing a file and a filegroup

To do this you will have to make sure that here are no objects associated with the file group. This could be Tables etc, Indexes, or even Blob objects.

This bit of code will show you the objects on a file group.


SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id

AND o.type = 'U' -- User Created Tables



I got this from:

http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/

If there are objects you want to keep you will need to move them to another filegroup. To move a non-clustered index you will need to drop and recreate it on the new file group. To move a table you need to drop and recreate the clustered index.

Once the filegroup has no user objects empty you can run the same commands for removing a file as – shrinkfile and remove file.

Then you will need to run the code below to drop the filegroup:

DATABASE [databaseName] REMOVE FILEGROUP [Filegroupname];

Remember: you can not drop the Primary filegroup.

Table Diff: Compare the contents of two tables

Table diff will let you compare two tables and generate a change script to sync them up.

By default it is installed here:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"

In this example I am comparing two tables in the same database called Live_Support. The tables are LF29699_Orig and LF29699_New.

I have created a batch file called comapretables.cmd.

The contents of the table looks like this:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sqlmgmt1 -sourcedatabase Live_Support -sourcetable LF29699_Orig -destinationserver sqlmgmt1 -destinationdatabase colt_Live_Support -destinationtable LF29699_new -f c:\LF29699_diff.sql

Execute this and you get a file c:\LF29699_diff.sql which is a change script for that table:

-- Host: sqlmgmt1

-- Database: [Live_Support]

-- Table: [dbo].[LF29699_new]

SET IDENTITY_INSERT [dbo].[LF29699_new] ON

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=3 WHERE [pkid] = 3

UPDATE [dbo].[LF29699_new] SET [GroupIndex]=2 WHERE [pkid] = 683

SET IDENTITY_INSERT [dbo].[LF29699_new] OFF

You can see from the script that there are 2 differences between the tables. It does not tell you what the values in the table are currently; you do have the primary key value though so you can look it up.

The two tables you compare will need to have primary keys defined on them.

BOL tableDiff.Exe: 'http://msdn.microsoft.com/en-us/library/ms162843.aspx

Replication Error - Login Failure

When setting up replication you might get an error saying login failed.

This will show up in the SQL Error Logs as:

Login failed for domain\username

Followed by

Error: 18456, Severity: 14, State: 6

The error code means that a windows log-in was entered in the place of a SQL log-in. In the case I had it was in the Log Agent Reader security.

In the section connect to publisher I had entered a windows service account (although it says quite clearly here: using the following SQL Server Login). I didn’t see this though as I added replication through a script which I had adapted.

SQL Cursors

I had thought that if an item came within teh scope of a cursor it would remain there until removed. However, cursors work a little like views, if you cange a value which would remove an item from a view you will no longer see it in the view. If you change the value of a data row which would put it outside the scope of teh cursor the cursor will drop that row.

This was tested on SQL 2005 sp3 – I assume it will be the same on other versions but I haven’t tested them.

I have a simple table:

CREATE TABLE [dbo].[test](

[pkid] [int] IDENTITY(1,1) NOT NULL,

[descript] [varchar](5) NOT NULL,

[value1] [int] NOT NULL,

[value2] [int] NOT NULL,

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

[pkid] ASC

);

The table has 3 rows:

Pkid	descript	value1	value2
1	test1	0	0
2	test2	0	0
3	test1	0	0

I then execute this script against the table:

Declare @dbName as varchar(5);

Declare @x as int;

Set @x = 1;

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select descript From dbo.Test

WHERE value2 = 0;

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Print @dbName

Update dbo.Test

Set value2 = @x

Where descript = @dbname;

Set @x = @x+1;

FETCH NEXT FROM dbCursor INTO @dbName;

END

CLOSE dbCursor;

DEALLOCATE dbCursor;

The results in the table are:

Pkid	descript	value1	value2
1	test1	0	1
2	test2	0	2
3	test1	0	1

This shows that because the value2 value of pkid 3 changed from 0 to 1 on the first update it dropped out of the cursors scope.