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 Error ? No query specification returned by transform status

I'm getting this error in a simple DTS package.
No query specification returned by transform status.
Any ideas what's causing this ?
DTS is simply running a query (which returns data via preview)
and insert results into a table.
Thanks
MarkTry to redefine the package and run.
Ensure the account used is having required privilege to insert the data.sql

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was WIndows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?
Mitch,
[vbcol=seagreen]
In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was WIndows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?
|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Mitch,
> In the context of the error message, I think it refers to SQL2000 licensing
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
sql

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was Windows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?Mitch,

In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx
.gbl...[vbcol=seagreen]
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was Windows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2
MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Mitch,
>
> In the context of the error message, I think it refers to SQL2000 licensin
g
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx
.gbl...

DTS Error

In trying to run a step in a DTS package, I get this error
on SQL 2000 running on Windows 2000.
"The DTS cannot copy or transform data from a desktop or
MSDE Server to a standard, Enterprise of small business
version of SQL SERVER unless your destination server is
per user licensing mode."
I thought the error was WIndows 2000 licensing error. The
licensing is set to per seat. Anyone know how I could
fixe this?Mitch,
>> I thought the error was WIndows 2000 licensing error.
In the context of the error message, I think it refers to SQL2000 licensing
in the destination server.Can you post the output of the below commands on
both source and destination servers ?
SELECT SERVERPROPERTY('PRODUCTLEVEL')
SELECT SERVERPROPERTY('LICENSETYPE')
SELECT SERVERPROPERTY('EDITION')
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> In trying to run a step in a DTS package, I get this error
> on SQL 2000 running on Windows 2000.
> "The DTS cannot copy or transform data from a desktop or
> MSDE Server to a standard, Enterprise of small business
> version of SQL SERVER unless your destination server is
> per user licensing mode."
> I thought the error was WIndows 2000 licensing error. The
> licensing is set to per seat. Anyone know how I could
> fixe this?|||I work with Mitch Orndorff. Here is the results of the select
statements:
SELECT SERVERPROPERTY('PRODUCTLEVEL') SP3
SELECT SERVERPROPERTY('LICENSETYPE') DISABLED
SELECT SERVERPROPERTY('EDITION') Personal Edition
I checked the same on the server we are replacing, and the LICENSETYPE
is set to PER_SEAT. So, I understand we need to change the new server
to PER_SEAT licensing. The question is, how? (Please don't tell me a
reinstall). Thank you in advance for any help you can provide.
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message news:<#EK9vSCVEHA.3788@.TK2MSFTNGP12.phx.gbl>...
> Mitch,
> >> I thought the error was WIndows 2000 licensing error.
> In the context of the error message, I think it refers to SQL2000 licensing
> in the destination server.Can you post the output of the below commands on
> both source and destination servers ?
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> SELECT SERVERPROPERTY('LICENSETYPE')
> SELECT SERVERPROPERTY('EDITION')
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Mitch Orndorff" <orndorffm@.hotmail.com> wrote in message
> news:1845c01c44a77$e7328d30$a301280a@.phx.gbl...
> > In trying to run a step in a DTS package, I get this error
> > on SQL 2000 running on Windows 2000.
> >
> > "The DTS cannot copy or transform data from a desktop or
> > MSDE Server to a standard, Enterprise of small business
> > version of SQL SERVER unless your destination server is
> > per user licensing mode."
> >
> > I thought the error was WIndows 2000 licensing error. The
> > licensing is set to per seat. Anyone know how I could
> > fixe this?

DTS connection

Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?

Thanks in advanceHi Eisa,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ashsql

DTS Active X transform script error

I can't for the life of me figure out why I am getting an error with this simple script. The error I get is "expected end" on line "22" which is the first "elseif" in the nested if statement. This makes no sense as obviously I don't want an end if statement there. Any ideas where my syntax is wrong would be greatly appreciated.

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

dim txncode
dim cashin

txncode = Trim(DTSSource("Col004"))
Cashin = Trim(DTSSource("Col005"))

if txncode = "" or IsNull(txncode) then
Main = DTSTransformStat_SkipRow
exit Function
end if

select case txncode
case "210"
if (Cashin > "0" and Cashin < "1000") then txncode = "210-1000"
'msgbox txncode
elseif ("1000" < Cashin and Cashin < "5000") then txncode = "210-1000-5000"
elseif (Cashin > "5000") then txncode = "210-5000"
else txncode = "210"
end if
msgbox txncode
Main = DTSTransformStat_OK
exit function

case else
Main = DTSTransformStat_OK
exit function

end select

DTSDestination("TransactionCode") = txncode
Main = DTSTransformStat_OK
End Functiongtheo, I hope someone is able to answer your question, but most of the heavy posters on this forum long ago stopped putting logic into DTS. Use it to load staging tables and the put your transform logic into a sproc.|||Problem Solved - it was twofold.

1) inline if-then made it expect it to have an end, if using multiple else you need then on next line.
2) quotes around numbers made them evaluate as text.

sproc solution untenable as that would entail schema changes, a no-no in a big bad corporate world development environment where I am on the client side, not dev!|||Glad you found the solution, but...

huh?

Why would a sproc solution imply schema changes? Are you talking about the staging tables? If necessary, those can be maintained in a separate database on the same server. As a consultant, I dip into a dozen or more big bad corporate worlds every year and there is always a way to implement this.|||The problem is an immature product that means a fat client (including full SQL Client) and no separation between an "application service user" and the end user. So the end user has to execute the DTS - meaning they have to create any temp tables, etc that are used; also I'd be discouraged from creating a separate database if not forbidden by the bank clients. They don't want anything that is not an "offical release". So, adding objects is not advised, and a separate database would be thrown back at me. So the easiest thing to do is have a structured storage file DTS Package that sits on the client and executes the logic inside itself. This is not viewed as custom code per se. Makes no sense to me but it's the way I get around them.|||Foolish people. Make sure then have your contact information so they can easily get in touch with you to rewrite the whole thing when they upgrade to 2005.

DTS "general error" when trying to export/transform to txt file

Hi there. I'm using the DTS Import/Export wizard to attempt to export data to a text file. I am using the visual basic transformations (or whatever they're called) to change column names at the destination, but that's about the most unusual or complex thing I am doing.

When I finish up, I try to save the export for later use, in the source server's Meta Data Services. It starts to save and then craps out with the following very useless error:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: General error -2147217355 (80041035)

Google turns up nothing on those number strings... anyone have any ideas, or failing that, a pointer to a tutorial page on how to create a data export script that includes the flexibility to change column names? Maybe I'm doing something wrong and don't realize it.www.sqldts.com is a good site to take a look. There might be something there.

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks.
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tell
s
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this.
I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DT_NTEXT pass through columns in fuzzy lookup transformation

The documentation on the fuzzy lookup transform mentions that only columns of type DT_WSTR and DT_STR can be used in fuzzy matching. I interpreted this as meaning that you could not create a mapping between an input column of type DT_NTEXT and a column from the reference table. I assumed that you could still have a DT_NTEXT column as part of the input and mark this as a pass through column so that it's value could be inserted in the destination, together with the result of the lookup operation. Apparently this is not the case. Validation fails with the following message: 'The data type of column 'fieldname' is not supported.' First, I'd like to confirm that this is really the case and that I have not misinterpreted this limitation.

Finally, given the following situation

- A data source with input columns

Field_A DT_STR
Field_B DT_NTEXT

- A fuzzy lookup is used to match Field_A to a row in the reference table and obtain Field_C.

- Finally, Field_B and Field_C must be inserted into the destination.

Can anyone suggest how this could be achieved?

Fernando Tubio

One possible workaround is using a multicast transform to route the input columns around the lookup transform. A merge join transform can then be used to join the outputs from the multicast and the fuzzy lookup to include the DT_NTEXT field back into the data flow.

I've tried this solution and it works but I wonder if it is really necessary to resort to all these contortions.

|||

It looks like your workaround is the best approach. The Fuzzy lookup does not support DT_NTEXT, DT_TEXT, or DT_IMAGE columns as copy columns OR pass-through columns. I am not sure why that is, but I will try to find out.

Mark

|||

I guess one of the reasons was performance, and that these columns require special handling. You might want to put in a request for this feature for a future release.

Thanks
Mark

|||

Thank you Mark.

Considering my limited knowledge about the inner workings of the data flow pipeline I am very likely wrong, but I would have guessed that a pass-through operation merely involved copying some pointers around. In any case, the package creator can control which columns to pass-through and if he is concerned with performance, then he is in a better position to decide whether to include these columns in the output. So I guess it would be nice to have this choice in a future release.

Fernando Tubio