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 Execute Process Task question

In the properties sheet for a DTS Execute Process Task, there is a input box for 'Parameters' which allow you to add command-line options to the task exe specified. How can you use the DTS Global variables to be passed in this parameter field? Is there a special format to indicate that the parameters are in fact global variable tokens?I think the Global Variables are designed to be used for ActiveX Script Task only.

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 and Excel - missing fields

Hello, I'm having a problem with importing an Excel file
with DTS. When doing this I see 2 entries for each sheet
in the Excel file but one has a $. The one with the $ has
all the fields from Excel when I preview it but the one
without the $ doesn't have the last field (going to a bit
field in SQL). When I look at the transformations for the
Source on the last field it says "ignore" and the field
isn't in the list to choose from Excel. Can anybody tell
me what's going on?
Thanks,
VanFound the problem. Instead of "1"s and "0"s in Excel, I
needed to use "True"s and "False"s for bit fields.
>--Original Message--
>Hello, I'm having a problem with importing an Excel file
>with DTS. When doing this I see 2 entries for each sheet
>in the Excel file but one has a $. The one with the $
has
>all the fields from Excel when I preview it but the one
>without the $ doesn't have the last field (going to a bit
>field in SQL). When I look at the transformations for
the
>Source on the last field it says "ignore" and the field
>isn't in the list to choose from Excel. Can anybody tell
>me what's going on?
>Thanks,
>Van
>.
>

DTS and EXcel

I am currently creating a DTS that will carry out a select from a table and
insert the results into a spreadsheet.
Each sheet has a formula at the bottom that calculates the sum of column A.
In the DTS the first step that I do is to remove all data that was inserted
into the sheet last time the DTS was run: DROP TABLE [Test$]
However, this removes my formula at the bottom of the sheet (cell A65536).
Is there a way of removing all the data from the sheet without also deleting
my formula.
many thanksHey Warren,
I would remove the DROP TABLE statement and use an ActiveX Script such as
this one:
'***************************************
************
' Visual Basic ActiveX Script
'***************************************
************
Function Main()
Dim xlApp
Dim wkBook
Dim sheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set wkBook = xlApp.Workbooks.Open("C:\test.xls")
Set sheet = wkBook.Sheets("Sheet1")
sheet.Range("A1:D1000").ClearContents
wkBook.Save
wkBook.Close
xlApp.Quit
Set sheet = Nothing
Set wkBook = Nothing
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
Obviously, modify the Range to suit your needs. Hope this helps
Kevin Bowker
"Warren Hughes" wrote:

> I am currently creating a DTS that will carry out a select from a table an
d
> insert the results into a spreadsheet.
> Each sheet has a formula at the bottom that calculates the sum of column A
.
> In the DTS the first step that I do is to remove all data that was inserte
d
> into the sheet last time the DTS was run: DROP TABLE [Test$]
> However, this removes my formula at the bottom of the sheet (cell A65536).
> Is there a way of removing all the data from the sheet without also deleti
ng
> my formula.
> many thanks

DTS + .net code

Hi,
My web application take data from excel sheet and store in the
database using dts. it is working fine when i run directly dts package
on sql server 2000 but i call sp ( exec master..xp_cmdshell 'dtsrun /S
/N /E ' ) from dot net code then it gives error
"A severe error occurred on the current command. The results, if any,
should be discarded "
*** Sent via Developersdex http://www.codecomments.com ***Have you tried running the command using Query Analyzer? If that works, try
adding 'no_output' to the statement executed by the application:
exec master..xp_cmdshell 'dtsrun /S /N MyPackage /E', no_output
Hope this helps.
Dan Guzman
SQL Server MVP
"Pooja Sharma" <pooja.sharma@.siliconbiztech.com> wrote in message
news:%23zgue0RSHHA.1364@.TK2MSFTNGP06.phx.gbl...
> Hi,
> My web application take data from excel sheet and store in the
> database using dts. it is working fine when i run directly dts package
> on sql server 2000 but i call sp ( exec master..xp_cmdshell 'dtsrun /S
> /N /E ' ) from dot net code then it gives error
> "A severe error occurred on the current command. The results, if any,
> should be discarded "
>
> *** Sent via Developersdex http://www.codecomments.com ***

Dts

I need to run a DTS package everyday twice.
Everytime it should fetch some record and put in a excel sheet (in a definite place).Now my requirement is that this DTs should override the excel file everytime.As it is a daily requirment I need this.How can I get new fresh records everytime?Refer to this KBA [http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319951] for more information.

You can schedule the DTS package in order to execute the package twice or as per the requirement.