DTS permissions

I would like to import data from Source database into Target using DTS, what are the minimum SQL Server 2K privileges/server roles required for regular user to achieve that on both Source and Target instances? This import will replace existing objects on Target.

Thanks

As a client side tool, the minimum needed would be the same as if you were executing the SQL statements from any other tool. You'd need permissions to connect to the source, access the database, read from the table/view or execute whatever stored procedure. On the target, you'd need to be able to drop and create whatever objects are involved, insert into the table, etc.

-Sue

|||

There are also mulitple ways of doing this - if you're talking about transferring data between SQL Server databases, then Sue is correct. You can also export the data from the source server and store it as a file to be imported into the target. In that case, you have a lot of options, from file permissions to FTP security.

|||I was trying to use DTS in order to copy database objects from source to target, using drop target objects before copy option. In both databases user was db_owner however when I tried using DTS it was giving error saying that I need to have sysadmin privileges and sure enough as soon as I granted that to a user it worked. I also think that is overkill because I don't want developers working with DTS packages getting almighty privs.

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

DTS package

Hi all,
I am new to this forum. I have a question on DTS packages.
I am trying to create a DTS package which loads the data into target table(ORACLE).
Is that possible to create DTS package which do full load when i run the package initially and delta loads whenever i run the package later? I am not trying to create any stage table. I am trying load directly into Warehouse table.

Can ayone help me in this?

Thanks

DTS questions should be asked in the DTS newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg