Dynamic SQL Server Destination?

Hi,

Is there anyway to dynamically change the Sql Server Destination? Let's imagine i want the data to go to a table based on a specific parameter like year for example...

Imagine tables like table1_2005, table1_2006 and i want to dinamically change the SQL Server Destination so it would insert 2005 or 2006 and so on....

I can't seem to find that option... It was really a good idea for microsoft to implement Expressions in such components... Dynamics it's all about it :)

Best Regards,

Luis,
From my experience the words dynamic and data flow task don't seem to go hand in hand....and SSIS leaves a lot to be desired in this area. However there are some workarounds that I have used. I've mainly done dynamic stuff with datasources, using the datareader and expressions for the SqlCommand property. Granted the table can change, but the metadata, field names, and datatypes need to be identical(as far as I'm aware at least). I don't believe any kind of expressions are possible with destinations within a dataflow though. However you could try out the "table name or view name variable" selection in the data access mode piece of the destination, and then use package variables to set the table name. You would have to use the OLE DB Destination to do this instead of SQL Server Destination, since it's not implemented there(at least in Sept CTP, I haven't moved over yet to release). Jamie has a good article on using variables in a OLE DB source that might give you some ideas:
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Thanks,
Adrian
|||

Yes i had already seen that option in the OLE DB but if i use that i would up getting hundred of diferent variables and that's not too good.. an Expression would be much more accessible and i cant understand why it doesn't exists...

Lets wait and see if it comes in by microsoft!

|||Perhaps you should use "Table or view name variable" as the data access mode of your destination, then set the variable to EvaulateAsExpression=True. Then you can put an expression in the Expression property and the destination will receive the evaluation result as the table name.
|||That's correct. Sql Server Destination does not support variable access mode, so it is not possible to change the table name dynamically at execution time. You should use OLEDB destination, and use the variable access mode. For the variable, make the EvaluateAsExpression = True, and use an expression to determine the variable value.