INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Use Temp Table as staging table?

Use Temp Table as staging table?

(OP)
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.

RE: Use Temp Table as staging table?

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: How to get the best answers before you ask a question

RE: Use Temp Table as staging table?

(OP)

Quote (Geoff)


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...

CODE

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.

RE: Use Temp Table as staging table?

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: How to get the best answers before you ask a question

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close