No prob, glad it could help 🙂

Sure - you can do it in two queries, that will work just fine, you don't even need to use variables. Or you can do it in one query, like...

As far as I know, if you try to make column headings dynamic, you're going to get inconsistent metadata. The only way to effectively pull it off is to just...

Actually, a followup question for this -

Upon thinking about this a bit more, I realised that in order to get what I'm trying to do to work, I'll need to...

Nope, no way you can do it without re-mapping. If you tell SSIS that you want to use the first row as column headers, and then you change the column...

Why not just have the "column headings" be the first row of the data you're inserting into the excel spreadsheet?

EG, do something like:

SELECT

Val1,

Val2,

Val3,

2 AS RowOrder

FROM [Table]

UNION

SELECT

'Val1 Column Header',

'Val2 Column Header',

'Val3...

i *think* he wants [2010] to be the dynamic column header decided by that select query

Could you use dynamic SQL?

If so, you can do this:

DECLARE @SQLCommand NVARCHAR(MAX)

SET @SQLCommand = 'SELECT ' + CHAR(10)

SET @SQLCommand = @SQLCommand + 'TestVal AS ''' + CAST((SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE()))) AS...

nm - misread your post

Yeah - though not available for just the data source, need to set it at the Data Flow Task level

The problem with the validations is that the expressions are only evaluated at run-time. So, as far as the engine is concerned, when it is trying to validate the package,...

For the error, try using the Advanced Editor instead of the basic one. In there, specify the connection string as a static one, and then the SQL Command from Variable....

Why do you need to know the column names? If you deselect the option "Column Names in First Row" for the excel output, then you're just left with "F1", "F2",...

Without going too much into your problem, if you're already able to use your dynamic connection manager settings to work with the Execute SQL task, you could just use another...

Ahh right, thanks Joe. Had read about that in Jeff's article on the "quirky" update, and didn't realise it applied here too.