DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]

DTS question - need your help!

I am trying to use DTS load Excel data to SQL Server

The Excel Spreadsheet has 100 columns in each row. Let say I have 3 rows in Excel sheet.
Each row has 100 columns as follows,

column1, column2, ...column100

Now I want to load it to a table which has only one column.

The first record will be the column1 of the first row from the excel spreadsheet
The second record will be the column2 of the first row from the excel spreadsheet
...
The 100th record will be the column100 of the first row from the excel spreadsheet

The 101th record will be the column1 of the second row from the excel spreadsheet
...

Can anyone give me a suggections? Thanks in advance,

PeterHmm, any way you can transpose the columns in Excel? I can't think of a simple way to create an SQL Statement to transpose the columns in DTS. It might be easier to manipulate Excel, rather than trying to do the transpose in DTS.|||I agree with the first post, but you could do it starting with DTS. Suck the excel sheet into a 'holding' table and then run a stored procedure that takes each cell and makes it a record by itself. You could use a cursor to step through the records and do 100 inserts to the final table for each row in the holding table.|||Is the order truyly that important?

You can get all the data in one column by using the following SQL statement instead of a table as your source:

SELECT Column1
FROM [Sheet1$]
UNION ALL
SELECT Column2
FROM [Sheet1$]
UNION ALL
SELECT Column3
FROM [Sheet1$]
...

You'll need a lot of cut and past (or a handy little script) to generate all the UNIONed selects.

If the order truly is important, then I'd add another column to your spreadsheet for the row number. Import the entire spreadsheet into the a temp table using something like this:

SELECT TheValue = Column1,
TheRow = RowNumber, -- your row number column
TheColumn = 1
FROM [Sheet1$]
UNION ALL
...

Increment the column number for each column. Again, a script will write this monster select for you. Then you can insert into your final table:

INSERT ...
SELECT TheValue
FROM MyTempImportTable
ORDER by TheRow ASC, TheColumn ASC

FYI - You may need to use UNION rather than UNION ALL. I don't recall the exact workings of EXCEL with respect to the ALL part. A little experimenting (or a syntax) error should clear it up pretty quickly.

DTS question

I have a dts package which I built which I will call Package1. Package1
imports data from an excel spreadsheet into SQL Server 2000. The only thing
that changes about Package1 is the excel input file name. How can I call th
e
package from VB .Net and feed the new excel file name into the call from VB
.Net?
Thanks.
ArcherSee if this helps, look for "Using Connections":
Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?292
AMB
"bagman3rd" wrote:

> I have a dts package which I built which I will call Package1. Package1
> imports data from an excel spreadsheet into SQL Server 2000. The only thi
ng
> that changes about Package1 is the excel input file name. How can I call
the
> package from VB .Net and feed the new excel file name into the call from V
B
> .Net?
> Thanks.
> Archer

DTS Question

I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >

DTS query with vb to excel

Hi,
does anyone have an example for doing a query in dts via vb an exporting it
to excel
ths and reg
bxandiUse the DTS Wizard to create a package then save the backage as a VB
file. You can then modify the VB code to your requirements.
An alternative option is to use Excel's QueryTables.Add method to
import the data with no DTS required.
David Portas
SQL Server MVP
--

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 take a long time to run

Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
Loane
You can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:

>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complete
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create each
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>
|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>

dts packages take a long time to run

Hi there
I'm running a DTS package that imports data from various Excel spreadsheets
into a SQL Server database. Running the package from the command prompt, I
get information on each data pump task including each 1,000 rows imported
successfully. When all rows have been imported from a particular worksheet
(65536), DTS takes quite a bit of time before it initiates the data pump
task for the next worksheet. I notice that the CPU falls idle during this
time, but DTSrun.exe's memory usage remains high for some time afterward.
This apparent idle time more than doubles the total amount taken to complete
this DTS package. Do you know why this is so? I thought it might be time
taken to build the indexes on each imported worksheet (I need to create each
table from scratch), or alternatively the time taken to establish
communication with Excel. Any other ideas, and a suggestion on how to pick
up the pace?!
Best regards
LoaneYou can use profiler to monitor what is going on - looking
at what the process is doing especially at the end of the
import and after the import, what the durations are for
different steps and processes in the package.
You could also monitor it more "manually" by querying
sysprocesses and watching the wait types, activities, etc.
If you are running SQL 2000, sp3 you can also use fn_get_sql
to see what exactly it's executing at different points.
-Sue
On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
<look_sharp_not@.hotmail.com> wrote:

>Hi there
>I'm running a DTS package that imports data from various Excel spreadsheets
>into a SQL Server database. Running the package from the command prompt, I
>get information on each data pump task including each 1,000 rows imported
>successfully. When all rows have been imported from a particular worksheet
>(65536), DTS takes quite a bit of time before it initiates the data pump
>task for the next worksheet. I notice that the CPU falls idle during this
>time, but DTSrun.exe's memory usage remains high for some time afterward.
>This apparent idle time more than doubles the total amount taken to complet
e
>this DTS package. Do you know why this is so? I thought it might be time
>taken to build the indexes on each imported worksheet (I need to create eac
h
>table from scratch), or alternatively the time taken to establish
>communication with Excel. Any other ideas, and a suggestion on how to pick
>up the pace?!
>Best regards
>Loane
>|||thanks, will try this one, it's been really niggling me
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3end71hhr8drpdqhs7kg8d4k6d99415lo3@.
4ax.com...
> You can use profiler to monitor what is going on - looking
> at what the process is doing especially at the end of the
> import and after the import, what the durations are for
> different steps and processes in the package.
> You could also monitor it more "manually" by querying
> sysprocesses and watching the wait types, activities, etc.
> If you are running SQL 2000, sp3 you can also use fn_get_sql
> to see what exactly it's executing at different points.
> -Sue
> On Mon, 2 May 2005 22:26:21 +0200, "Loane Sharp"
> <look_sharp_not@.hotmail.com> wrote:
>
>

DTS package to overwrite the existing Excel

Hello,
Is there are a way to set up a DTS package
to export data to an Excel format, overwriting or deleting the existing Excel file.
Thank you,
Ythere r a few tricks for this.
(1) add a ActiveX or SQL task before the export task to delete existing xls and copy a blank xls file in the same name
(2) export as CSV file and that can be opened with excel directly (can aslo change the extension to xls)
(3) write script to delete rows from existing excle file and then append data.

DTS package to check if table exists

Hi all,

I have created a DTS package in SQL Server that takes Excel file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

a lot of thanks in advance,

Dmitry

yeah, and by the way,

when i execute the DTS i can see how many records are taken from XLS and copied into the database, if i can just somehow check that number, retreive that varible... that would solve the problem.

thanks again,

Dmitry

|||

It is often a good idea to import data into 'holding tables' (duplicate schema to the production tables), do whatever data cleanup/manipulation is required, and then move the data to the final tables.

With that process, it is easy to determine rowcount, duplications, etc, before running other code.

|||

ok...sounds ok, i have never done them before though... can you link an example or something..

many thanks

DTS Package Problem

Created a DTS Package is SQL that drops a table then recreates it then
imports data from an excel spreadsheet. This works fine if you run it
from the Local Packages and click execute package.
But, if you run it using the xp_cmdshell it doesn't popluate all the
columns. It will fill the 29 columns then the rest are null and then it
will fill the last 4 columns.
I thought that maybe that if the 30th column is null in the first row
then the rest would be null. So I sorted by that column in the package
and I'm getting the same results.
Does a package run differently with the xp_cmdshell versus righ
clicking and executing?
Thanks
MikeIf you run a exectue sql task with xp_cmdshell in it by right clicking and
executing, this will run same as executing full package with other steps.
How are you running it exactly (from where, how) and what is the sql stmt
inside xp_cmdshell ?
"Mike" wrote:

> Created a DTS Package is SQL that drops a table then recreates it then
> imports data from an excel spreadsheet. This works fine if you run it
> from the Local Packages and click execute package.
> But, if you run it using the xp_cmdshell it doesn't popluate all the
> columns. It will fill the 29 columns then the rest are null and then it
> will fill the last 4 columns.
> I thought that maybe that if the 30th column is null in the first row
> then the rest would be null. So I sorted by that column in the package
> and I'm getting the same results.
> Does a package run differently with the xp_cmdshell versus righ
> clicking and executing?
> Thanks
> Mike
>|||EXEC master..xp_cmdshell 'dtsrun /Sservername /E /NExcelInsert'
running this statement leaves most columns NULL.
Going through Enterprise manager --> local packages and right clicking
and hitting execute runs correctly.
MattB wrote:
> If you run a exectue sql task with xp_cmdshell in it by right clicking and
> executing, this will run same as executing full package with other steps.
> How are you running it exactly (from where, how) and what is the sql stmt
> inside xp_cmdshell ?
>
> "Mike" wrote:
>

dts package help

I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.

I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:

Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).

I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.

Any help is appreciated.Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.|||This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.

I appreciate the help. Thank you.|||You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.

-PatP|||I double checked and I did specify an Excel file as the connection, not a text file.

It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?|||Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.

-PatP|||You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.

DTS Package Execution (From Job)

Hi,
We have DTS package which imports data from an Excel file into an SQL Server 2000 table. The DTS package runs fine when exectued from SQL Server Enterprise Manager, but when we run the Package from a Job it executes when the excel file is in the local drive. The execution of the package fails when the excel file resides on a different Computer shared drive.
We get the following error message.

\\CompName\SharedDrive\ExcelFile.xls is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

Note that we have access to the shared drive.
Please reply if have faced the same problem!Is it possible that the job owner doesn't have rights to remote computer and when package runs within its context it can't access a file?

It is only a guess.

Good Luck.|||Do u mean the "file access" ?
We have the file access! The file was created and modifed by us in the remote system. Do u think of any IIS problem? As of now there is no IIS installed in these systems.

Regards,
Srinidhi S. Rao

DTS Package Excel Connection - Please Advise

Hi,

I'm using MS SQL 2000 and I want to export data from MS SQL database to MS Excel file. So, I choose the DTS package tool to do this job.

I try to create the connection to the Excel file, which located in the shared location by using the UNC path, e.g. \\servername\foldername\filename.xls However, I cannot provide the username/password to connect to the file's location. So, the connection does not work.

Do you have any idea how to create the Excel connection along with shared username/password ?

Anyway, if it is not possible :-( any advices about exporting MS SQL data to MS Excel sheet via DTS packages are still welcome....

(The reason that I prefer DTS package bec. I can schedule the job to run it automatically)

Thank you in advance.

You need not mention the user and password to the excel file, until unless you want to secure the access to excel file with a password.

KBA http://support.microsoft.com/kb/319951 for your information to perform this action without any issues.

|||

Thanks, I have tested by sharing a folder on my machine and give access to "Everyone" account. It works fine .

However, with the real situation. The shared folder, which I have to place files in it, needs authentication. They don't allow shared folder to have a read/write access for "Everyone" account.

What should I do?

|||With a READ permission those users should be able to read the file on that shared folder, this has to be done using Windows explorer and SQL will not have any influence in this case. But if you are running this as a scheduled job then SQLAGent will do the rest if it has required privileges.

DTS Package Excel Connection - Please Advise

Hi,

I'm using MS SQL 2000 and I want to export data from MS SQL

database to MS Excel file. So, I choose the DTS package tool to do

this job.

I try to create the connection to the Excel file, which located in the

shared location by using the UNC path, e.g.

\\servername\foldername\filename.xls However, I cannot provide the

username/password to connect to the file's location. So, the connection

does not work.

Do you have any idea how to create the Excel connection along with

shared username/password ?

Anyway, if it is not possible :o any advices about exporting MS SQL

data to MS Excel sheet via DTS packages are still welcome...

(The reason that I prefer DTS package bec. I can schedule the job

to run it automatically)

Thank you in advance.I am betting the user context that the dts package is executing under dopes not have access to the UNC path.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks|||Is your windows login having appropriate rights on that folder & files..?

To check try this...

1. Click Start Menu -> Run
2. Write \\servername\foldername\filename.xls -> Press OK

If the file opens, ensure that you are having appropriate rights & need to check something else.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks

Thanks for all replies.

To clarify... A scheduled job that I created runs steps with TSQL command. I have a stored procedure to load and execute DTS package by database account. Since more and more securities are concerned, it is not possible for me to run the scheduled job with DTSRUN.exe via local windows account.

For more information: http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3

That's why I can't identify what the SQL-Agent login is because I didn't use it to run the jobs.|||OK
You mean you are using the following sp inside the job:
either
1. using integrated security: EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.IntSecurity=1
or
2. using the current connection via SUSER_NAME() EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.ServerPWD='xxxx', @.PkgPWD='xxxx'

But when a job is executed, specially for file access which is your case, SQL uses SQL-Agent NT login to access the file not the user login or sql login. Therfore you must know what the SQL-Agent login is and check if the SQL-Agent login has the access permission. We had lots of issues before accessing
Excel files in the network drive. Later on we found the issue came from the permission of SQL-Agent login.
So I suggest
1. You contact your SQL server administrator to inform you SQL-Agent NT login and request permission to access the file. - Best solution.
Or
2. You may create a macro in the Excel to import the data. - Hard to schedule. But I can give you hand for this.
Or
3. You may create vbscript to import the data into Excel. This, you may be able to schedule to run. Similar to #2.

DTS package

I have a DTS package which imports data from an Excel spreadsheet into an SQ
L
Server database. The package works fine, but I would like to take the
package to the next step in automation. How could I call the package(from V
B
.Net or QA) to be executed with only one change in the package: the name of
the excel spreadsheet and corresponding worksheet'
Thanks
ArcherSee
http://www.sqldts.com/default.aspx?292
for file manipulation.
You can call run the DTS Package from the command line with DTSrun.exe and
then pass a global variable to the package as a parameter with the /A
switch. (See dtsrun in Books Online) Alternatively, from VB or VB.Net, you
can instantiate a DTS Package object and run that. A DTSPackage has a
GlobalVariables collection that you can populate and an Execute method to
run it.(See "Executing DTS Packages with the DTS Object Model" in Books
Online for more details)
Jacco Schalkwijk
SQL Server MVP
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:FB2C9E14-2229-49CE-B464-16564F1FA557@.microsoft.com...
>I have a DTS package which imports data from an Excel spreadsheet into an
>SQL
> Server database. The package works fine, but I would like to take the
> package to the next step in automation. How could I call the package(from
> VB
> .Net or QA) to be executed with only one change in the package: the name
> of
> the excel spreadsheet and corresponding worksheet'
> Thanks
> Archer|||There's a couple ways you could do this...
Using parameters:
http://www.sqldts.com/default.aspx?234
DTS in VB.Net
http://www.sqldts.com/default.aspx?265
There's tons of DTS info on this site...
"bagman3rd" wrote:

> I have a DTS package which imports data from an Excel spreadsheet into an
SQL
> Server database. The package works fine, but I would like to take the
> package to the next step in automation. How could I call the package(from
VB
> .Net or QA) to be executed with only one change in the package: the name o
f
> the excel spreadsheet and corresponding worksheet'
> Thanks
> Archer|||here is some code from the microsoft site:
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer( _
ServerName:="MyServer", _
ServerUserName:="MyUserID", _
ServerPassword:="MyPassword", _
PackageName:="DTSDemo")
dtsp.Execute()
Christy Warner
www.autoaudit.com
"Alien2_51" wrote:
> There's a couple ways you could do this...
> Using parameters:
> http://www.sqldts.com/default.aspx?234
> DTS in VB.Net
> http://www.sqldts.com/default.aspx?265
> There's tons of DTS info on this site...
>
> "bagman3rd" wrote:
>

DTS Output Excel File - how to format?

Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?

Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)

Thanks!I will have a similar situation coming up so I am curious to see the answer. We are installing a SQL Server box in our department. It will be our own server where I will be able to create DTS jobs to export data every 15 minutes throughtout the course of the day. I plan on appending data to a table and having it do so for each and every job's output on SQL Server. The issue is I am only inhouse very early in the morning so I want to allow the client to see the reports as they run. They are tech challenged so I want to use either Excel or Access to create reports as they are run.

ddave|||I had vb developers modify a csv file while I wrotw the sql...

It was a whole store and forward app...scheduling, formatting ect...

sql just delivered the file to a location...|||>Also an easier question:
>What is the best (easiest) way to create a unique filename in Excel with a >datetimestamp in the file name (i.e. MyFile-20040608.xls)

DTS outputs it to any MyFile.xls and the next ActiveX step renames MyFile.xls file to MyFile-<date>.xls
-rohit

DTS jet oledb:engine type

What does jet oledb:engine type = 35 mean? I believe it means Excel file.
Where can I get a list of all the engine types?
Tnx in advance
JoeHave a look at this
http://support.microsoft.com/default.aspx?scid=KB;EN-US;230501
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:
We are close...look here:
http://www.thecoffeeplace.com/om/aaaaaagl.html
This is a list and it talks about values of 10,11,12...70
But not 35. The page you gave me only has 1-5 which the I listed above also
has 1-5, but not 35?!
Am I missing something in reading the page you gave me...?
tnx
Joe
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:VA.00000044.0158986e@.no-spam.sqldts.com...
> Have a look at this
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;230501
>
> 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
>

DTS issue

In Microsoft SQL DTS I'm trying to run a package that convert output to a excel spreadsheet. Following error appears:
Error source: Microsoft Jet Database Engine
Error Description: Failure Creating File. What has added to my frustrations is, once I go to the server and open up the DTS package and click on destination it allows me to create new table, but that is only temporary. If I run a schedule job at a later time it fails again with the above message.Well if you're doing a CREATE, have you added the DROP?|||Brett, thanks for your reponse. It's only creating a dynamic table through DTS. When I take a look at the tables list it does not exist.|||Take a look at this post -> should answer your question.

http://www.dbforums.com/showthread.php?threadid=981661

kbk