DTS from MS SQL to Excel Spreadsheet Issue

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. Im using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named Results. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. Ive Googled this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jimright click on the spreadsheet and go to properties. what do you see?

the application dev team spent a week tossing something similar to this with Access for one their internal processes. I pointed it out a couple minutes after they asked me. I spent an hour laughing at my team of geniuses.

I am a joy to work with.|||Thanks for the idea. I thought of the read only flag as soon as I saw the read only error in the error log. Unfortunately that wasnt it. The issue was within the DTS package. If you open up the connection properties and click the top option to New Connection in an attempt to re-name the object, you actually create a new object with a new name leaving the older one in tacked but hidden in the background. This can only be seen if you go into Disconnected Edit under connections. There was an object names Connection1 and Connection2. These old connection objects where pointing to the development environment where the functional ID that was used to run the DTS did not have access to. Oops

Again thanks for the idea.

Jimsql

DTS from Excel to SQL?

I tried to use DTS for copying a sheet from Excel to SQL. For some
reason, the last column shows up error saying source's column 5 buffer
is too big.. and failed it.. is there anything that I need to watch
out?ebug@.hotmail.com (Kelvin) wrote in message news:<191e0546.0404261422.4bad806@.posting.google.com>...
> I tried to use DTS for copying a sheet from Excel to SQL. For some
> reason, the last column shows up error saying source's column 5 buffer
> is too big.. and failed it.. is there anything that I need to watch
> out?

Does this apply to your case?

http://support.microsoft.com/defaul...kb;EN-US;281517

Simon

DTS from Excel problems

I am trying to import an Excel spreadsheet into SQL Server. Why can I impor
t
an empty Excel column only if it is a text field? If the field is a number
or date field, I get a conversion error.
i.e.
create table labresults
(
analyte varchar(50),
sampledate datetime,
result numeric(19,6)
)
The excel worksheet has 3 columns:
analyte (formatted as text)
sampledate (formatted as date)
result (formatted as number)
If the first 7 rows in the analyte column are empty, the file imports fine,
but if the first 7 rows of sampledate or result are empty, I get a
"Conversions invalid for data types" error.
Anybody have any good suggestions on how to fix this issue?
Thanks.
ArcherHi
Sounds like it is related to:
http://www.sqldts.com/default.aspx?254
John
"bagman3rd" wrote:

> I am trying to import an Excel spreadsheet into SQL Server. Why can I imp
ort
> an empty Excel column only if it is a text field? If the field is a numbe
r
> or date field, I get a conversion error.
> i.e.
> create table labresults
> (
> analyte varchar(50),
> sampledate datetime,
> result numeric(19,6)
> )
> The excel worksheet has 3 columns:
> analyte (formatted as text)
> sampledate (formatted as date)
> result (formatted as number)
> If the first 7 rows in the analyte column are empty, the file imports fine
,
> but if the first 7 rows of sampledate or result are empty, I get a
> "Conversions invalid for data types" error.
> Anybody have any good suggestions on how to fix this issue?
> Thanks.
> Archer
>sql

DTS from excel file (excel filename is different everyday)

Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:

http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this

1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D

Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function

There's a syntax error. I will debug this later.

3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.

Please help :o|||Hi upalsen,

I got it already!
I followed your instructions. Many thanks to you. :)

Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:

100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX

wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.

Here's how my ActiveX Script looks like:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************

Option Explicit

Function Main()
Dim vDay, vMonth, vYear, vDate

vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear

DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"

Main = DTSTaskExecResult_Success
End Function

Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code

Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if

Main = DTSTaskExecResult_Success
End Function

in your starting ActiveX script consider vCounter and write code to get branch code for each value

if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....

.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,

Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.

And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF

I saw it in the Dynamic Property Task under Steps. Am I correct?

Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.

if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....

DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"

yes, u r right. step names r listed in dynamic prop under "steps" heading.

DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS for Excel problem

Hello

I have a problem and need some assistance.
I have a SS that I am loading into a SQL2000 database
I am using DTS for this.
Previously uses used an ADP file to load the spreadsheet.

My problem is that the ADP file uses the ACTIVESHEET for its import.
And the in the DTS you must select the SS you wish to insert.
And the SS name is not standard.

I am trying to use the ADP file and rename the worksheet
any idea how to do this or any idea howelse I can accomplish my goal.

TIA :confused:I will try to help but what does SS and ADP mean? With this explained I can try to help you. :rolleyes:

DePrins
:)|||SS = Excel Spreadsheet within a workbook
ADP = MIcrosoft Access file

I have a spreadsheet with a non specific name
I transfer that file to a shared drive and need to rename the ActiveSheet there. Using access or SQL Server|||Thanks, i will look into it.
:)

DTS Export to Excel Too many Recs?

Hi All,

I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

I am able to successfully export around +- 65,000 rows, but the operation fails after that.

I need to be able to get all the rows out, so using TOP obviously doesn't work.

Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

Thanks!You need to have a key field(s) that you can sort on. So lets say you have a unique id like excel_id numbered from 1 to 120000:

select top 65000 ... from table order by excel_id
select top 55000 ... from table order by excel_id desc|||If you want an inner subset like 65000-90000 then you would use something like:

select top 25000 ... from (select top 55000 from table order by field desc) order by field|||If you do have a key then you can use KEY BETWEEN 65001 and 90000, etc. ...and if you were using MySQL we wouldn't have even bothered to look at the question...In short, - DON'T USE IT!|||Are you using a standard DTS data pump to export the data? Or are you using something else?

If you are using a data pump then why not take care of the problem programaticly? Select all the records and keep an internal counter so you know when you reach 65000 and when you do change your target to a new spreadsheet zero the counter and continue on...

Just a thought,...|||There are plenty of other ways to do this as well, it just depends on the requirements for your export...|||With a programmatic solution, your performance will suffer. Allow the database to do its job.|||Thanks for all the replies/suggestions!

There is no key field.
I couldn't find a solution, so I just dumped the data, opened it in textpad, cut out three chunks (~35,000 rows) and imported them into 3 diff .xls files.

Interesting enough for those in the anti MySQL crowd ... chances are, this very forum almost certainly uses MySQL. I say this for two reasons. 1. PHP server side scripting. 2. This is a vBulletin forum.
Not to mention the growing interest in open source products vs proprietary!

Cheers!

DTS Export to Excel (How to Format Results in Excel)

Hi All

I've been googling this for a while now and can't seem to find any elegant answers.

I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.

Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?

His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).

In an ideal world an individual would send an email to the Server with two formated parameters (@.FromDate & @.ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.

Thanks

GWTurn your logic inside out. Create the macro, and use the macro to retrieve the data into Excel then format it.

-PatP|||Thanks for the suggestion Pat

I eventually got MSQuery installed & got some simple External Data into a sheet in Excel - Formatted another sheet and the past linked the Data from source sheet to the new sheet which seemed to work really good.

Then I tried altering the External Data Call to include the @.FromDate & @.ToDate.

O worlds of pain - got as far as this err :-

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

There's no cast/convert in the SP so I assume it's the dreaded ODBC Driver again.

O why O Why did you guys insist on formatting your dates funny m/d/y instead of the correct way which is British English d/m/y (Flame Flame - tehe) which I assume maybe the problem.

I'll battle on - any suggestions gratefuly received.

GW

DTS Export to Excel

I'm looking for the best way to export the results of a parameterized stored procedure (SQL 2000) to excel. I can do this with DTS using global variables for the parameters, but each time I execute the package it appends the data below where the previous data was, leaving a bunch of blank rows. I need the data to always be appended to the 2nd row (replacing the old data) because I have a chart based on a dynamic named range in Excel. Is there an easy way to do this in DTS, or should I approach this another way (ADO, ActiveX Scripts, .NET, etc.)? Thanks,

Dave

Try putting the query directly into the spreadsheet. Use Data -> Import External Data -> Query. You can set up the query to take parameters that you are either prompted for or are taken from a cell. You can also configure the query to always replace the previous data.|||Thanks. I was thinking that using Microsoft Query as you described might be the way to go. I actually came up with something that works using DTS with ActiveX scripts but it's alot "clunkier" than using Microsoft Query from Excel would be.sql

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I am using a DTS created by the Export Wizard to send data to an Excel
> > spreadsheet. The problem is that each time the DTS is run, the data gets
> > appended to the spreadsheet, rather than replaced. I used the Drop and
> > Create Destination Table option on the Transformation window.
> >
> > I found an earlier post regarding this same problem. The suggestion was to
> > use the Delete option in the Transformation window of the wizard. I rebuilt
> > the DTS based on that idea, but get an error message about "Deleting data in
> > a linked table is not supported by this ISAM".
> >
> > Can someone offer some ideas on how to get the data replaced in the Excel
> > spreadsheet?
> >
> > Thanks.
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I cannot rename the spreadsheet because it is tied into other processes. I
> > need to replace the data that already exists in the spreadsheet.
> >
> > If it helps, I did some research since my original posting and here is what
> > I found:
> > 1) When DTS creates the range name in the spreadsheet, it is only the
> > headings of the data. The data itself is not included in the range name.
> > Somewhere, the last line of data is being tracked versus the last line in the
> > range name. Subsequent runs of the DTS appear to be using the last line of
> > data, not the last line in the range.
> >
> > 2) If I manually expand the range name to include the last line of data,
> > then rerun the DTS, the new data is still appended to the bottom of the old
> > data. The old data is cleared leaving blank rows, but the new data is still
> > appended to the bottom; again based on the last line of data. The area
> > covered by the range name returns to being just the headings.
> >
> > Could the fact that Excel is not installed on the machine running the DTS
> > have any bearing?
> >
> > Thanks.
> >
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

ThanksPost the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||Here goes the Stored Procedure

CREATE PROCEDURE sp_ProductivityReport_ByMonth

@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

DECLARE @.@.total1 decimal(20,2)
DECLARE @.@.total2 decimal(20,2)
set @.@.total1 = 0
set @.@.total2 = 0

select B.T_FName as "First_Name", B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"

into #CustomTable1
from dbo.BillingInfo A (nolock), dbo.TInfo B (nolock)
where
A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY
A.T_ID, B.T_FName, B.T_LName

DECLARE Total_Cursor CURSOR Local
FOR SELECT Lines_Changed FROM #CustomTable1
OPEN Total_Cursor
FETCH next From Total_Cursor
INTO @.@.total2
while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor
INTO @.@.total2
END
CLOSE Total_Cursor
DEALLOCATE Total_Cursor

/**** Calculating Total*******/
DECLARE @.Reports_Total int
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1

DECLARE @.T_M_Total decimal(15,2)
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1

select "xxx - Total" as First_Name, ' ' as Last_Name, @.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed, @.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2

set nocount on

SELECT First_Name, Last_Name,Reports_Changed,
Minutes_Changed, Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.@.total1) AS decimal(5,2))) + '%'
as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT * FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

Post the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||You really need to learn more about TSQL, and principles of database application design before attempting something like this. Why? Because you are taking the wrong approach to solving a problem that, really, you should not be trying to solve with SQL in the first place.

Lets begin...

Dump the cursor and learn how to write set-based logic. This entire sectionDECLARE @.@.total2 decimal(20,2)
set @.@.total2 = 0
.
.
.
DECLARE Total_Cursor CURSOR Local FOR
SELECT Lines_Changed
FROM #CustomTable1

OPEN Total_Cursor
FETCH next From Total_Cursor INTO @.@.total2

while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor INTO @.@.total2
END

CLOSE Total_Cursor
DEALLOCATE Total_Cursor...can be replace by one line:SET @.@.totall = sum(Lines_Changed) from #CustomTable1

Next, all of these lines and more...SET @.@.totall = sum(Line_Changed) from #CustomTable1
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1
.
.
.
select "xxx - Total" as First_Name,
' ' as Last_Name,
@.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed,
@.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
...can be run as a single statementselect "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

Then, I have to ask, what is with the use of the double ampersands?

Lastly, it poor programming practice to be calculating subtotals to a report within SQL, as you are doing with CustomTable2. This is best handled by whatever reporting application you are using. Not because of a limitation within SQL, but because you are essentially mixing record types (raw and total) within a single dataset. Bad form.

So, re-read the Books Online sections on SELECT statements and aggregate queries. And if you find yourself using cursors again, be confident you are doing something wrong because you probably are.

But for the heck of it, go ahead and try this shortened code:CREATE PROCEDURE sp_ProductivityReport_ByMonth
@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

select B.T_FName as "First_Name",
B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"
into #CustomTable1
from dbo.BillingInfo A (nolock),
dbo.TInfo B (nolock)
where A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY A.T_ID,
B.T_FName,
B.T_LName

/**** Calculating Total*******/
select "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

SELECT First_Name,
Last_Name,
Reports_Changed,
Minutes_Changed,
Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.total1) AS decimal(5,2))) + '%' as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT *
FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS- excel option missing

I am a senior DBA using SQL Server 2000.

I just did a clean install of SQL 2000 Personal edition. I am trying to DTS an excel file in but the excel icon is not there!!!!! There are the other excel ODBC sources but they each require me to setup a DSN (not normal!!!)

Anyone had this problem before? I am uninstalling and reinstalling.

-KevinI don't understand...DTS in or out?

Are you using the wizard?

When open the menu option connection, what do you see?|||I am trying to import with DTS.
When I choose the "source" drop down list I am expecting to see a little excel icon with Excel 97-2000 next to it.

I see a bunch of data direct closed icons and the excel treiber , and the WINSQL excel workbook. All of those options require me to define a DSN.

The normal excel option just lets you choose the excel file.

?!?!?!?!?|||So you're in EM and you right click on DTS and SELECT

>>ALL Tasks >>IMPORT DATA...

Then you use the wizard and change the data source drop down...

and you don't see the teal "X" for Excel?

It's half way down the list...

What if you try to build one from scratch?|||No luck.

I am going to try to do it with Informatica.
I applied mdac 2.8 and SP3a.|||Do you have excel installed on the local machine?|||Yes. Thanks for your help. I found another work-around using Erwin instead of SQL Server.
Thanks anyway.

-Kevinsql

DTS Excel Import, Transform - how do I use "OR" clause in SQL Query

Howdy. I'm trying to build a query that will take an Excel file and
pull a few rows of data from a particular sheet. I'm having a problem
with my WHERE clause - I can can tell it to import WHERE a field
matches a value, or WHERE the field matches another value, but not
both.
I've tried bunches of different variations, but can't get it to work.
Is there any way to do this?

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body'))

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body' OR F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where (F1 = 'Body' OR F1 = 'Cash')

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body') OR (F1 = 'Cash'))Did you try:

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body'
union all
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

or

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 in ( 'Body', 'Cash')

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0409010509.1f10c665@.posting.google.c om...
> Howdy. I'm trying to build a query that will take an Excel file and
> pull a few rows of data from a particular sheet. I'm having a problem
> with my WHERE clause - I can can tell it to import WHERE a field
> matches a value, or WHERE the field matches another value, but not
> both.
> I've tried bunches of different variations, but can't get it to work.
> Is there any way to do this?
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body'))
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Body' OR F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where (F1 = 'Body' OR F1 = 'Cash')
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body') OR (F1 = 'Cash'))

DTS excel file

Hi,

I have to DTS a excel file to sql server 2000. The DTS package works fine. I have issue with one column that has value

450

800

900

45TH

23SI

800

390

100

30SI

If given the cell format general/text only that have alphanumeric characters are upload and not others. What should be done to upload all the values for this column.

thanks in advance.

Are you using DTS or SSIS?

There is a forum specific to DTS issues: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

DTS Excel Database

Ladies and gentlemen, I posted a question that I export data from SQL to .xls, where is that data of .xls stored?ummmmmmm..where you tell it...

You also have to remeber this...if you run the package manually, it will run under the contex of your login (meaning your mappings)

If it's scheduled, it will run under then context of the system account (or how the server see's itself..)

So if you map the excel to a c:\temp, and you run it, the file will be on your c drive..

If you schedule a job (or start a job) it wil;l place it on the servers c drive...

DTS Error: Importing from Excel file to SQL Server 2000

Data for Source Column 15 'Notes' is too large for the specified buffer size.

How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype totext

I have never seen this error when importing before. What do I do?

An error I've gotten way too much. Some things you can try:

- The Excel provider (JET) often makes assumptions based on the first 8 lines of the file, and sometimes that is the problem).
- Or you can save to comma separated or tab separated file format, and import that way.

Oftentimes, I cannot figure out the problem as well. I've tried posting before, but no answer. The only thing I come up with is to save to tab-delimited, and import that way.

Sorry I can't be more help; hopefully someone has the answer.

DTS Error involving Access

Hello,

I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:

Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.

I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.

I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.

BrianHow exactly are you running the DTS package? Are you triggering a SQL Agent job, or some other way? The security context is different depending on how you are executing the package.

You might find this link to be helpful:INF: How to Run a DTS Package as a Scheduled Job.

Terri|||Through VB.NET code, using the DTS library. Also note that, when I kick it off manually, it works fine. I also can open up the access database with no problems.

Brian

DTS Error

I need to export some table data to Excel which I normally do with Query Analyzer as a .CSV file. However, one of the fields has a ton of comma's which screws up the formatting so I'm using the DTS Import/Export Wizard. I choose the .xls file and paste my query. When it runs I get the following error: "Failed to copy 1 table(s). Double click the error..." When I click on the error it gives me an "Invalid Pointer" message. I'm not sure where to go from here so any help would be greatly appreciated.
Thanks,
Jim
I figured it out...I had "USE (Name of Database)" in my query. Once I took that line out everything worked quite well.
Jim
|||Please address future DTS (SSIS in SQL Server 2005) to the SSIS forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80