OLE DB Destination - table name or view name variable - fast load

Question

Hi

I have read through a few posts on here on how to create a dynamic table name using the  table name or view name variable - fast load property in the OLE DB destination component in SSIS 2008.

Please can someone outline the steps of how I can create a table name that includes the datetime on the fly using the OLE Db destination?

Thanks

Answer 1

1. Create a package scope variable "TableVariable", change its type to "string", then set the default value for it to the name of the table in your database
   
2. Open the editor of OLE DB Destination, choose Table name or view name variable-fast load as the data access mode.
   
3. Go to columns tab to map columns.
   
4. Then we can change the table name or the owner name dynamically.

Answer 2

you can store the date time coming from sql in a variable. Then append that value inside the SQL task/ script task to create the whole statement including creation of the table with that variable. In the execute "Execute SQL task", use that variable as the sql statement.

regards

joon

Answer 3

1. Create a package scope variable "TableVariable", change its type to "string", then set the default value for it to the name of the table in your database
   
2. Open the editor of OLE DB Destination, choose Table name or view name variable-fast load as the data access mode.
   
3. Go to columns tab to map columns.
   
4. Then we can change the table name or the owner name dynamically.

Hi irsul

Thanks for this. I have been doing this previously maybe I am still missing something in my knowledge here. After I have completed the 4 steps you have outlined. I imagine I can return to the variable "TableVariable" and change the value to anything I like because I would want to create a new table with a name that I have defined here that does not exisit in the database. I tried changing the variable value to "testtable" to see if my assumption is correct but I received the error below -

Just to reiterate I am hoping to using the OLE Db destination to create a new table name on the fly which contains the date and time in the table name. I understand the meta data will remain the same though which is how I want my data flow to behave.

Thanks

c

Answer 4

you can store the date time coming from sql in a variable. Then append that value inside the SQL task/ script task to create the whole statement including creation of the table with that variable. In the execute "Execute SQL task", use that variable as the sql statement.

regards

joon

Hi joon

Thanks for this, I was aware of this and I'm confident it will work for my needs.

I was posting to see if the OLE DB destination can be used to do exactly the same thing as I have read on some blogs and forums on the webt that indicates it can.

Thank you

Answer 5

set a default value (a valid table name) for your package variable

OR

set DelayValidation property to true for Data Flow task and ValidateMetadata property of component to false.

---

http://www.rad.pasfu.com