Use Temp Table as staging table?

[lameid]

First I am totally green in the SSIS world and I seem to be trying something difficult for the uninitated for my first package...

I have a flat file that I want to take and update columns in a target table. From what I gather I need to use a staging table and I am thinking that table should be a temp table and from what I read, a global temp table.

The control flow would then be a SQL task to create the global temp table, data flow task and lastly a SQL task to update the table and drop the temp table. The Ole DB connection to SQL has it's RetainSameConnection property set to true.

The data flow is where I am stalled. I have my Flat File source connected to an OLE DB Destination, presumably the temp table... The blog I was following suggested to push new and use the same temptable script from the SQL task after having set the ValidateExternalMetadata to false... I receive an error...
"The Table Name Could not be retrieved from the script provided. Select a table or view from the drop-down list box."

Am I doing a step incorrectly or is this a bad approach? Anyway, please put me on track. SQL 2008... I think R2 without double checking.

 

[xlbo]

Unless you are constrained by space I would not suggest loading to a temp table. If complex transformations need to occur, you are far better off having a physical table to stage data into and then transform out of. If simple transformations are required then no need for a staging table - simply use data flow components to take the data out of the flat file and directly into the final table

The issue with tempt tables is that you have to put workarounds in place as SSIS can't know the table structure in advance so it is difficult to set up the mapping from source to destination - SSIS all works on underlying metadata and if that is not available it can cause a lot of difficulties.

If you really want to set up this load to a staging temp table, you would be best off creating a physical table in the 1st place to set up the mappings. Then set you delayvalidation settings and validateexternalmetadata settings. then save the package and delete the table - seems like a lot of effort to go to for very little (if any) gain. I always like to have my staging tables available in the db so I can refer back to them if there are any data quality issues. Either set up a separate staging database or simply a staging schema within the current database if you are concerned about the complexity of the db

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lameid]

simply use data flow components to take the data out of the flat file and directly into the final table

My impression is that data flow components insert / append and I want to update....

So import "TableA" from Flat file...

Then something like...

Update Table1
Set Table1.Column1 = TableA.Column1,
    Table1.Column2 = TableA.Column2,
    Table1.ModifiedDate = GetDate()

From Table1 Inner Join TableA ON Table1.ID = TableA.ID

So Am I missing a dataflow option or do I need a physical table? - Or is there a less elegant but more linear to design option... I need to do this for two different tables / layouts. I suspet one will be about 1000 records and the other 300 records so looping the records would not be that bad of an option but I have not dug into everyting yet to know which flows I should be using. A nudge to the right one(s) would be appreciated.

 

[xlbo]

for update you would use the oledb command component and choose the update statement

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question