Thanks Koen,

I think I may have found a solution. Both instances run OK, but you need to use SSMS 2008 to manage the SSIS 2008 instance and SSMS...

Hi both,

Yes just having the dbo does return leading zeros. Also this does too:

SELECT staff_id, unique_name, name, title, sex, address1, address2, address3, address4, postcode, room_id,...

I've also just discovered that if I give the full path to the source DB and run the query from that DB is also gives the leading zeros.

Rather strange, but...

No this is just in Management Studio.

If I run the Query from another database with the full reference to the source database then the leading zeros don't get dropped.

e.g. SELECT...

Table Structure is:

USE [Academic_Year_2011_2012]

GO

/****** Object: Table [dbo].[CT_STAFF] Script Date: 06/08/2012 11:12:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CT_STAFF](

[staff_id] [int] NOT NULL,

[unique_name] [varchar](64) NOT NULL,

[name]...