Kolf


Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @table_name


I would be grateful if you can give me any hint.
Thanks
S




Re: Dynamic feed of table name to Transfer SQL Server Object Task

Rafael Salas


The logic looks right to me...are you seeing any error

I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner.

I hope that hepls you







Re: Dynamic feed of table name to Transfer SQL Server Object Task

MohitGupta

Hi,

I have faced this issue where TablesList property of Transfer SQL Server Object task takes in list of tables to transfer. There is no way to dynamically set the property through SSIS variable because this property expects StringCollection object. If you declare an SSIS variable as Object and assign it to TablesList property of the task thru Expression, it will not work because expressions cannot evaluate Object data type

I did a workaround by creating a child package in ScripTask and adding a TransferSQL server object task programmatically and assigning the TableList property as StringCollection object in VB.NET

Thanks

Mohit







Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

Thanks Rafael,
I feed the list of tables (as object) to foreach Loop Container and the loop will put them in to a string variable.
I have got other tasks in the
foreach Loop Container as well as transfer SQL Server Object tasks and they do use the table name (the string variable with out the problem)
the problem starts when I try to feed this table name to Tablelist properties of
transfer SQL Server Object tasks which it complians saying that I it can not assign the string value to the tablelist property.
I have even tried to feed a dataset(object -list of tables ) to that property and even that didn't work.





Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

Thanks Mohit for your reply.
so in the child Script task you are populating the table collation the way it should be but how are you feeding it to TabeList Propery.
would you be able to attach the VB code please.
Many Thanks




Re: Dynamic feed of table name to Transfer SQL Server Object Task

MohitGupta

This is how to do it...

'create sql server object task to move tables

Dim MoveTable As Executable = Child.Executables.Add("STOCK:TransferSqlServerObjectsTask")

Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)

'set properties

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)

MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)

'create a stringcollection of tables

Dim Tables As StringCollection = New StringCollection()

Tables.Add('Table1')

Tables.Add('Table2')

Tables.Add('Table3')

'create string collection for tables to transfer

MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables))

'set the source and destination connections

......................

'execute package and dispose

Thanks

Mohit






Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

thanks



Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"


sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas




Re: Dynamic feed of table name to Transfer SQL Server Object Task

MohitGupta

Kolf wrote:
I manage to use this method and copy all the tables.
The problem is if I tables belong to a schema this method won't work.
I am using version variable as schema name.
sample
version ="VT.1"
Table_name="Sample"


sc.Add("[" + Version + "]." + Dts.Variables("Table_Name").Value.ToString)

so I am expecting to add [VT.1].sample to the TableList collection.
but when it is trying to populate tablelist property from the sc (collection) variable it complains that table does not exist at source!
which I know for the fact it does

Any ideas

Can you try with any table with dbo schema wether you are able to transfer. Also check whether you have permission on the schema to access the table.

Thanks

Mohit






Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution
thanks




Re: Dynamic feed of table name to Transfer SQL Server Object Task

MohitGupta

Kolf wrote:
it does work with dbo schema
and I do have permission on the schemas
it seems like the object copy task doesn't understand the collection of the table names with the schema.
any solution
thanks

It seems it does not understand schemas. Bcoz even creating the Transfer SQL server Object task in design time , I selected one table of schema1. I had same tablename for schema2. When i select one table from schema1 for table list property and close the task and edit again, I see both the tables of different schema selected automatically. Seems there is a problem. Suggest you to use tablename to maintain versions like tablename + "_" + VersionName.

Thanks

Mohit






Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!





Re: Dynamic feed of table name to Transfer SQL Server Object Task

MohitGupta

 Kolf wrote:

Thanks for the quick reply.

so it seems like this is a bug as the functionality is there but it doesn't work.

I have to be able to copy tables in a schema to the destination DB

I have even tried to create the schema manually at destination

 

let's say

I have a table

schema:[Dt.1]

table:test

then I've got [Dt.1].test  as my source table

I've created schema [Dt.1] at the destination database but still SQL object copy task complains that task can't see test table in the source database.

this is even the case if I hardcode the name of the tables in the task!

 

 

 

Just check in your code for this:

MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True) to copy schemas thru Task and

MoveTableTask.Properties("SchemaList").SetValue(MoveTableTask,sc) is assigned list of schemas to transfer .

If does not work and you want to stick to schemas you can try dataflow tasks

Thanks

Mohit






Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

Dataflow task can't be used as I have different tables with different table columns
would you be able to create a sample dtsx based on adventureworks please
thanks




Re: Dynamic feed of table name to Transfer SQL Server Object Task

Kolf

Thanks Mohit
the problem with dataflow tasks is that (my table names are dynamic and I have got a foreach Loop that feed the table names to a dataflow
and in dataflow I have got a
* OLE DB source ( which the data access methode has been set to Variable) and that's how I feed my table name (variable called User::source_table ) and an example value for it will be
[DT.1].TableA
which is pointing to [DT.1] and with in the OLEDB Source I can preview the data

* also I have got an OLE DB Destination which same as OLE DB source is reading the table name from a variable ( which I'm using the same variable name , as the table name and the schema on both servers are the same)

problem: in order for this to work I have to manually click on the column map section in OLEDB Destinaiton section and save the package(manually) as the tables and they columns are changing this method won't be possible.

I hope I 've explained it properly.
Many Thanks