Can I use a SQL Server #Temp table in another transformation

sbass1 · Post by **sbass1** » Wed Mar 04, 2009 6:49 pm

Job Design:

Seq File --> xfm1 --> SQL Server #Temp table --> select distinct --> xmf2 --> SQL Server final table

I need to do a bunch of lookups in xfm1, where the lookups will map source data to different values (example effective date to month surrogate key). I then need to write the distinct rows to the final table.

A great case for a transient, temporary SQL Server table. Alas, since DS does not persist the connection to SQL Server, the temp table is gone when xfm2 kicks in.

Is there any way to get DS to not drop the initial SQL Server connection so I can use temporary tables?

Thanks,
Scott

ray.wurlod · Post by **ray.wurlod** » Wed Mar 04, 2009 7:01 pm

You can more easily use UniVerse table in this scenario.

sbass1 · Post by **sbass1** » Wed Mar 04, 2009 9:16 pm

Can I do select distinct <composite key> on the Universe table?

ray.wurlod · Post by **ray.wurlod** » Wed Mar 04, 2009 10:17 pm

Yes.

In fact, if you want the entire composite key, it's easier and faster. The system variable @ID accesses the entire composite key. However, this is necessarily unique anyway - it's the primary key.