April 29, 2009 at 4:20 pm
I am having problems withing with the Transfer SQL Server Objects task in SSIS. I would like to set the TablesList Property of the task to the value of an expression. At a high level, I need to transfer a table from one server to another but the table name is dynamic so I can't use a data flow task. Each iteration of the package execution will work off of one table so I need to set the TablesList property to the one table.
Since the property is a collection, I am getting errors when I attempt to just assign the TablesList property to the variable value in the expression builder. I get this error:
Error at Copy Table To Production: The result of the expression "@[User::TablesList]" on property "TablesList" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I also tried to open up the SSIS package in textpad and look at the formatting of the TablesList property and then set the variable to match what I saw in the XML......this gave me the same error.
Any suggestions on how to set a StringCollections property in SSIS?
Thanks in advance,
John
April 29, 2009 at 8:07 pm
What you are trying to do is not straightforward, as you have found.
One way is described here, under Self Modifying Packages in SSIS?:
http://www.sqljunkies.com/weblog/knight_reign/
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 30, 2009 at 7:59 am
Thanks Phil. I had read through that article as well, but I was hoping there was an easier way to do it through the expression builder.
I had also sent an e-mail off to Jamie Thomson from SSIS Junkies and the reply that he sent states that you cannot set a collection property through an expression.
So it appears that the only way to do this is to use a script task.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply