No, I've dumped that idea after our discussion. My approach is more to give them the pros and cons of each method allowing them to make informed decisions. If they...

Thanks for this Jeff, I am enjoying this interesting discussion.

Ah... no. CTE's are absolutely wonderful for building "top down", highly readable, highly effective code. I just wouldn't build a query...

Ok great. I thought that might promote some discussion!

So lets start with Table Variables. Basicially never use these, regardless of the row size or application (unless it's a UDF). Lack...

Great, thanks for your help. I was using single quotes for the zero which was causing me an issue.

Apologies I have been attempting to correct this but cannot get the syntax right. I have tried to add something along the lines of RIGHT('0' + but I just cannot...

Is it possible to store more than the 1,024 columns in SQL Server 2008?

I am going to carry out some peformance testing on each one and see which one works the best

Thanks both,

I have looked at both solutions.

Dave - I have never used the ON statement with a BETWEEN before.

Chris - Very interesting solution thanks

Thanks Dave,

I have a date table and this is what I was looking at. But my main issue is to break it out by day, do I use PIVOT perhaps?

I...

Ok so you for a solution I would recommend the following.

1. Create your audit table

CREATE TABLE [Audit].[DataLoad] (

[Audit_Key] ...

What type of config are you using XML? Are you setting the value of the connection manager directly from the configuration?

Thanks Jeff, some good points.

What I have managed to find out is that the table actually has in excess of 6k columns! However, only 1k of these are useful.

I...

I believe so yes. I am yet to actually see the data. At the moment I just know it is a 'big' issue so I am trying to get ahead...

Thanks both. I am glad there wasn't something obvious I was missing! My thought was to partition into smaller tables and use a view over the top so this seems...

Thanks Gift,

In terms of connectivity I know there are a couple of OLEDB drivers to use within SSIS. An IBM one and an MS one which is supposedly better but...