Dynamic File location for DTS transfer

Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||

netflash99 wrote:

Never heard of SQLDMO, what is that?

SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.

http://www.sqldts.com

Dynamic feed of table name to Transfer SQL Server Object Task

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

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

|||

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

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

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

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

|||thanks|||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?|||

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

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

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

|||

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!

|||

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

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

Dynamic feed of table name to Transfer SQL Server Object Task

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

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

|||

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

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

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

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

|||thanks|||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?|||

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

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

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

|||

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!

|||

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

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

Dynamic Data Source in DTS

I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
LixinIn short, you could use global variables to specify the data source. When
you invoke the package using DTSRUN, you could specify values for these
global variables. See SQLDTS.com for examples.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
Lixin|||Vyas,
Thanks a lot.
I went to SQLDTS.com. However, I didn't find the example I
really need.
What I want to kow is how to set up the server name in
source server of DTS as global viriable so that I can pass
in the server name as parameter through DTSRUN? Can you
give more instructions about how to do it in EM?
Lixin
>--Original Message--
>In short, you could use global variables to specify the
data source. When
>you invoke the package using DTSRUN, you could specify
values for these
>global variables. See SQLDTS.com for examples.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>What hardware is your SQL Server running on?
>http://vyaskn.tripod.com/poll.htm
>
>"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
>news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
>I want to build a DTS package to transfer data between two
>SQL 2000 servers. I like to set up the data source as a
>variable. In another word, I want to pass in the data
>source (source SQL server 2000) as a parameter to the DTS
>package so that I can reuse this package somewhere else.
>Is it possible? How?
>Thanks in advance,
>Lixin
>
>.
>|||OK
Let's say I have a connection to SQL Server and I call it MySQLServer. The
Global Variable that holds the name of the server is "MyConnVar". In an
Active Script task I would do.
dim PKG
dim Conn
set PKG = DTSGlobalVariables.Parent
set Conn = PKG.Connections("MySQLServer")
Conn.DataSource = DTSGlobalVariables("MyConnVar").Value
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:082501c38921$6977f0a0$a401280a@.phx.gbl...
> Vyas,
> Thanks a lot.
> I went to SQLDTS.com. However, I didn't find the example I
> really need.
> What I want to kow is how to set up the server name in
> source server of DTS as global viriable so that I can pass
> in the server name as parameter through DTSRUN? Can you
> give more instructions about how to do it in EM?
> Lixin
> >--Original Message--
> >In short, you could use global variables to specify the
> data source. When
> >you invoke the package using DTSRUN, you could specify
> values for these
> >global variables. See SQLDTS.com for examples.
> >
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >What hardware is your SQL Server running on?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
> >news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
> >I want to build a DTS package to transfer data between two
> >SQL 2000 servers. I like to set up the data source as a
> >variable. In another word, I want to pass in the data
> >source (source SQL server 2000) as a parameter to the DTS
> >package so that I can reuse this package somewhere else.
> >Is it possible? How?
> >
> >Thanks in advance,
> >Lixin
> >
> >
> >.
> >