DTS problem

Hi,
I am trying to export data into EXCEL sheet using DTS, It is working fine
for single user, however it fails with error messages when tried by multiple
users at the same time...So is it limitation of DTS as it does not run
simultaneously ?Is the destination Excel Workbook the same for all users?
If it is, then that's why the DTS package fails. If that's not the case,
please post the actual error message.
ML|||Hi ML,
It is the same excel but user can change the path and each one is exporting
it in different folders, so I feel it should not be the case.
Errors is shown like :
"Microsoft Database Engine canot open the file ", It is already opened
exculisevely by another user, or you need permission to view data.
Is this because every user is using single user(user name + pwd, is provided
thru config file), with special permission, to fetch data from different
tables.
Thanks
"ML" wrote:

> Is the destination Excel Workbook the same for all users?
> If it is, then that's why the DTS package fails. If that's not the case,
> please post the actual error message.
>
> ML|||Judging by the error message all users are trying to access the same Excel
file.
You say they have the option of changing the output path - at what point in
time are they allowed to change it? Before or after executing the DTS packag
e?
ML|||It is through input box, which is displayed through VB script and that is th
e
part of DTS package. So It happens during the execution of DTS package.
"ML" wrote:

> Judging by the error message all users are trying to access the same Excel
> file.
> You say they have the option of changing the output path - at what point i
n
> time are they allowed to change it? Before or after executing the DTS pack
age?
>
> ML|||If the error occurs before this VB script is executed, then the connection t
o
the Excel file is made before the user selects the output file - which means
the connection is made to the same file for each user.
However, if the error occurs after the user has selected a different output
file, then it seems that the connection is not made to the file the user
selected.
Maybe you could create a new file for each user at the beginning of the
package, and establish a connection dynamically:
1) open a new file using VBS;
2) name the file by embedding the user's name and a timestamp (e.g.
"Output_John_200510301255.xls");
3) passing the filename as a global variable to the data connection.
ML

DTS PACKAGES SECURITY ISSUE

Hi,
I need to give access to a user to use enterprise manager to create DTS
packages within a single database. However I have a problem that he can still
see other DTS packages and open them. IS there any way of restricting the DTS
packages so that he can only see what he had created and not others?
Thanks
Anp
Hi
It is not a good idea to develop DTS packages against a live system. You may
want to create a safe environment on a different server for this person to
work with.
John
"Anup" wrote:

> Hi,
> I need to give access to a user to use enterprise manager to create DTS
> packages within a single database. However I have a problem that he can still
> see other DTS packages and open them. IS there any way of restricting the DTS
> packages so that he can only see what he had created and not others?
> Thanks
> Anp
|||Additionally, you can secure DTS packages with DTS package
passwords. You can find explanations of this in books online
under the topic: Handling Package Security in DTS
-Sue
On Wed, 17 Aug 2005 23:11:01 -0700, "Anup"
<Anup@.discussions.microsoft.com> wrote:

>Hi,
>I need to give access to a user to use enterprise manager to create DTS
>packages within a single database. However I have a problem that he can still
>see other DTS packages and open them. IS there any way of restricting the DTS
>packages so that he can only see what he had created and not others?
>Thanks
>Anp
sqlsql

DTS PACKAGES SECURITY ISSUE

Hi,
I need to give access to a user to use enterprise manager to create DTS
packages within a single database. However I have a problem that he can still
see other DTS packages and open them. IS there any way of restricting the DTS
packages so that he can only see what he had created and not others?
Thanks
AnpHi
It is not a good idea to develop DTS packages against a live system. You may
want to create a safe environment on a different server for this person to
work with.
John
"Anup" wrote:
> Hi,
> I need to give access to a user to use enterprise manager to create DTS
> packages within a single database. However I have a problem that he can still
> see other DTS packages and open them. IS there any way of restricting the DTS
> packages so that he can only see what he had created and not others?
> Thanks
> Anp|||Additionally, you can secure DTS packages with DTS package
passwords. You can find explanations of this in books online
under the topic: Handling Package Security in DTS
-Sue
On Wed, 17 Aug 2005 23:11:01 -0700, "Anup"
<Anup@.discussions.microsoft.com> wrote:
>Hi,
>I need to give access to a user to use enterprise manager to create DTS
>packages within a single database. However I have a problem that he can still
>see other DTS packages and open them. IS there any way of restricting the DTS
>packages so that he can only see what he had created and not others?
>Thanks
>Anp

DTS PACKAGES SECURITY ISSUE

Hi,
I need to give access to a user to use enterprise manager to create DTS
packages within a single database. However I have a problem that he can stil
l
see other DTS packages and open them. IS there any way of restricting the DT
S
packages so that he can only see what he had created and not others?
Thanks
AnpHi
It is not a good idea to develop DTS packages against a live system. You may
want to create a safe environment on a different server for this person to
work with.
John
"Anup" wrote:

> Hi,
> I need to give access to a user to use enterprise manager to create DTS
> packages within a single database. However I have a problem that he can st
ill
> see other DTS packages and open them. IS there any way of restricting the
DTS
> packages so that he can only see what he had created and not others?
> Thanks
> Anp|||Additionally, you can secure DTS packages with DTS package
passwords. You can find explanations of this in books online
under the topic: Handling Package Security in DTS
-Sue
On Wed, 17 Aug 2005 23:11:01 -0700, "Anup"
<Anup@.discussions.microsoft.com> wrote:

>Hi,
>I need to give access to a user to use enterprise manager to create DTS
>packages within a single database. However I have a problem that he can sti
ll
>see other DTS packages and open them. IS there any way of restricting the D
TS
>packages so that he can only see what he had created and not others?
>Thanks
>Anp

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
Loane
In the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
LoaneIn the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
LoaneIn the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
--
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>

DTS import

Hi Folks,
I want to import the contents of several text files into a single sql server
table, however I also want to add in an additonal field which will give some
sort of indication which source file the row came from.
So for example.
File1.csv
a, b, c
File2.csv
x, y, z
sqlTable
a, b, c, 1
x, y, z, 2
Any ideas how can I could do this, I know I could import to tempory tables
and set the indicator field with a default value and then copy this data
into the final table but this seems like I would be adding in unnecessary
steps.
Cheers in advance
Charlie.From the example given it would seem that you need
A. A loop
B. A Global Variable that you can set.
Basics are that on the setup of the package you map Source --> Destination
columns but you also (In an Active X transform) map a Global Variable to a
destination column.
At each iteration you can then set the GV to whatever value you want
(Perhaps name of file is good).
Here is a looping example.
Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)
--
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|||Thanks Allan,
It looks a bit complex, but I'll give it a go, I like a challenge!
Charlie.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> From the example given it would seem that you need
> A. A loop
> B. A Global Variable that you can set.
>
> Basics are that on the setup of the package you map Source --> Destination
> columns but you also (In an Active X transform) map a Global Variable to a
> destination column.
> At each iteration you can then set the GV to whatever value you want
> (Perhaps name of file is good).
> Here is a looping example.
> Looping, Importing and Archiving
> (http://www.sqldts.com/Default.aspx?246)
> --
>
> 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|||I've had a look at this and downloaded the sample file from the website, but
its a bit beyond me, I don't mind setting up seperate DTS packages for each
file import if this makes things simpler.
Charlie.
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:RTF3b.222$b82.110168@.newsfep1-win.server.ntli.net...
> Thanks Allan,
> It looks a bit complex, but I'll give it a go, I like a challenge!
> Charlie.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> > From the example given it would seem that you need
> >
> > A. A loop
> > B. A Global Variable that you can set.
> >
> >
> > Basics are that on the setup of the package you map Source -->
Destination
> > columns but you also (In an Active X transform) map a Global Variable to
a
> > destination column.
> > At each iteration you can then set the GV to whatever value you want
> > (Perhaps name of file is good).
> >
> > Here is a looping example.
> >
> > Looping, Importing and Archiving
> > (http://www.sqldts.com/Default.aspx?246)
> >
> > --
> >
> >
> > 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
>|||OK.
Setting up another package for every file will be OK if you have 2 or 3
files but will quickly become unmanageable if you have 5000.
We can go through it if you wish.
Are you in the UK (email suggests this)
I am doing a presentation at MS Reading next week and this is one of the
things I am covering.
See here
http://www.sqlserverfaq.com/?eid=32
--
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