unpivot transformation and expressions

I am using the unpivot transformation, but I can not figure out how to use an expression in the Pivot Key Value.

The denormalized table I want to unpivot has columns like Sunday_Qty, Monday_Qty, Tuesday_Qty, etc. Just before the unpivot component, I inserted a derived column component that adds fields like DateSun, DateMon, DateTue, etc. that resolves to values like 01/07/2007, 01/08/2007, etc.

So for the various rows in the Unpivot Transformation Editor, I entered DateSun, DateMon, DateTue, etc. for the Pivot Key Value, and "EntryDate" for the pivot key value column name.

The data pipeline gets unpivoted correctly, but the rows have the literal values "DateSun", "DateMon", etc. in the EntryDate column.

How do I tell SSIS to use the DateSun column instead of the string "DateSun"

-Larry

I think that is a reasonable approach, thanks.

BTW, I was actually doing the unpivot in my SQL using a UNION ALL after I created a temp table. But I get an error when I paste my SQL into the SSIS OLE DB Source when it has a temp table:

Error at Release Hours [OLE DB Source [3100]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Invalid object name '#timesheet'.".

Why aren't temp tables allowed for this

Thank you!

Still no luck on using temp tables. Here's a simple example using an OLE DB Source on my Data Flow Task:

CREATE TABLE #report (
report_id INT
)

insert into #report
select 1

select * from #report

drop table #report

I had to enter the SQL via the SqlCommand property b/c the component editor tries to validate the existence of the #report table, and throws an error since it doesn't exist. I also set ValidateExternalMetadata to false, set the task's DelayValidation property to false, and set the connection manager component's RetainSameConnection to true (though I don't think that would be necessary since the temp table does not need to persist outside of the OLE DB Source component).

When I execute my task, I get this error:

[MyTest 1 [1]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Invalid object name '#report'.".

I get the same results if I use a global temp table (##report).

Any ideas

-Larry

It's working now after creating the temp table in the Control Flow. I did create the table out of SSIS first for the purpose of metadata.

Thanks for the help.

-Larry