DTS Rounding problem

I have a problem...
Data transformation rounds or truncate decimals!
I have an ODBC source in witch is a table with float numbers (ODBC Driver
publish it as float).
If I build a query form access or from excel with Query Analizer, I see all
decimal places, but when I try to insert data using DTS, float numbers will
be converted to its integer values.
A "select * from table", with table ODBC table, gives integer value when
runned from DTS to transform data from ODBC to MS-SQL Server table, and
gives float values from Access or other tools.
Where I can fix my problem?
Thanks.
Giorgioput convert(float,field) around your fields in the insert
example
declare @.i int
select @.i =1
select convert(float,@.i),@.i

http://sqlservercode.blogspot.com/

dts retrieves 0 rows when scheduled

Hi all
After scheduling a dts, which retrieves data from a priority-tabula server
(using the tabula propriatary odbc driver) , no rows are transfared.
There is no error massage i can trace sql server or in any of its logs, the
job just passes gracefully by the dts data transfare tasks, which all return
0 rows..
If I execute the dts manually all is well...
Both the sql server and sqlserver agent user accounts are the domain
administrator.
Any idea will be greatly appriciated!
I've been fighting this phantom phenomena for a very l o n g time and still
have no clue.
Thank you
Rea
what is the result if you schedule your package in a Windows scheduled tasks
instead-of SQL Agent?
have you try to change the DTS properties to make sur the thread run in the
same process? (sometimes if this option is not checked, there is some
problems to pass the identity to the components)
"Rea Peleg" <rea_p@.afek.co.il> a crit dans le message de
news:eWbHCLWTEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Hi all
> After scheduling a dts, which retrieves data from a priority-tabula
server
> (using the tabula propriatary odbc driver) , no rows are transfared.
> There is no error massage i can trace sql server or in any of its logs,
the
> job just passes gracefully by the dts data transfare tasks, which all
return
> 0 rows..
> If I execute the dts manually all is well...
> Both the sql server and sqlserver agent user accounts are the domain
> administrator.
> Any idea will be greatly appriciated!
> I've been fighting this phantom phenomena for a very l o n g time and
still
> have no clue.
> Thank you
> Rea
>

dts retrieves 0 rows when scheduled

Hi all
After scheduling a dts, which retrieves data from a priority-tabula server
(using the tabula propriatary odbc driver) , no rows are transfared.
There is no error massage i can trace sql server or in any of its logs, the
job just passes gracefully by the dts data transfare tasks, which all return
0 rows..
If I execute the dts manually all is well...
Both the sql server and sqlserver agent user accounts are the domain
administrator.
Any idea will be greatly appriciated!
I've been fighting this phantom phenomena for a very l o n g time and still
have no clue.
Thank you
Reawhat is the result if you schedule your package in a Windows scheduled tasks
instead-of SQL Agent?
have you try to change the DTS properties to make sur the thread run in the
same process? (sometimes if this option is not checked, there is some
problems to pass the identity to the components)
"Rea Peleg" <rea_p@.afek.co.il> a crit dans le message de
news:eWbHCLWTEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Hi all
> After scheduling a dts, which retrieves data from a priority-tabula
server
> (using the tabula propriatary odbc driver) , no rows are transfared.
> There is no error massage i can trace sql server or in any of its logs,
the
> job just passes gracefully by the dts data transfare tasks, which all
return
> 0 rows..
> If I execute the dts manually all is well...
> Both the sql server and sqlserver agent user accounts are the domain
> administrator.
> Any idea will be greatly appriciated!
> I've been fighting this phantom phenomena for a very l o n g time and
still
> have no clue.
> Thank you
> Rea
>

dts question, data pump task hangs when i try to edit it!

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Quote:

Originally Posted by catstevens

I'm trying to build a DTS package that will copy data from one server to another, and I'm using the latest teradata ODBC driver to connect to teradata. The problem is when i setup the dts package - DTS just sits there and hangs when i try to make a new data pump task.

Any clues why this would occur? Also, in case you were curious, I am on sql server 2000 with service pack 4

Have you connected to your data source before doing this?|||hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks|||

Quote:

Originally Posted by catstevens

hi,

i kind of figured out what was wrong so in case anyone googles this - dts attempts to look at every table in the data source for every database. because the source connection i was trying to pump from had so many tables it hung for a good 3 minutes before the properties window opened.

it sped up a little when I performed the editing on the server itself (as opposed to a workstation using enterprise manager).

thanks

Hey dude,

What do you mean by editing on the server. I am a newbie to the data world and am trying to do the same ..extracting the data from TeraData to SQL Server. Its hanging and I was never able to get this done.

Can you please help me here...

THanks,
Rahul

DTS Package for AS/400 to SQL Server

Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

ThanksYes, we had this exact same issue. There are several steps you will need to take to resolve it:

1. Check the version of IBM Client Access that you are running on your SQL server. We installed V5R1 with the latest service pack. The latest service pack is critical because the original version of V5R1 has a bug that limits the size of the connection library to 9 characters (it's supposed to be 10).

2. Make sure that your SQL Agent is running as a user account (preferably a Domain User). Make sure it is NOT running as the local system account. Verify this by right-clicking on the SQL Server Agent icon under the management folder of your SQL Server (in EM) and then clicking on Properties. The Service Startup Account information should be filled in.

3. Set up an account on the AS/400 with the EXACT same name as the service account for SQL Agent. The password must also be EXACTLY the same (nb, at least on our AS/400, passwords are limited to 10 characters; the password for your SQL Agent account must conform to the same limitation).

4. When you set up the ODBC DSN connection to the AS/400 on your SQL Server, be sure to specify to use Windows Authentication (no prompting).

Doing all of this will ensure:

1. That your server will connect to the AS/400 without the password prompt
2. That rebooting your server will not impact the state of the connection.

Bear in mind that if you change the password for the SQL Agent account, then you will need to change it on the AS/400 as well.

I hope that this helps.

Hugh Scott

Originally posted by lossman
Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

Thanks

DTS job fails when scheduled from SQL Agent

Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..

Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' 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 OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.

How come it loses the path to the file when I dont run it directly?
Cheers
MickDTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.

Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.

I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.

Regards,

hmscott|||Thanks for that,
Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
Cheers
ML|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.

HTH|||Thanks folks,
I used UNC text file sources instead of odbc text connections. Worked great.
Cheers
Mick

DTS in SQL 2005

Not sure why Microsoft would remove from SQL 2005 DTS import data
sources all the default drivers available to ODBC but they did. In SQL
2000 the drivers available to ODBC were by default available to DTS
import. How does one go about getting DTS to recognize these drivers
so simple data import can be accomplished.
To import from a DBF file you now have to create an ODBC data source
Every time you want to add a new file and then to add insult to injury
they make you key in all the ODBC information for the import source
instead of giving you a simple drop down.
Seems like a huge step backwards as far as user interface goes.Hi
If I understood you correctly , you can run DTS (SS2000) from SS2005. There
is a folder called Legacy under Management folder.
<jmirsky@.hotmail.com> wrote in message
news:1139956308.785445.51930@.g43g2000cwa.googlegroups.com...
> Not sure why Microsoft would remove from SQL 2005 DTS import data
> sources all the default drivers available to ODBC but they did. In SQL
> 2000 the drivers available to ODBC were by default available to DTS
> import. How does one go about getting DTS to recognize these drivers
> so simple data import can be accomplished.
> To import from a DBF file you now have to create an ODBC data source
> Every time you want to add a new file and then to add insult to injury
> they make you key in all the ODBC information for the import source
> instead of giving you a simple drop down.
> Seems like a huge step backwards as far as user interface goes.
>

DTS in SQL 2005

Not sure why Microsoft would remove from SQL 2005 DTS import data
sources all the default drivers available to ODBC but they did. In SQL
2000 the drivers available to ODBC were by default available to DTS
import. How does one go about getting DTS to recognize these drivers
so simple data import can be accomplished.
To import from a DBF file you now have to create an ODBC data source
Every time you want to add a new file and then to add insult to injury
they make you key in all the ODBC information for the import source
instead of giving you a simple drop down.
Seems like a huge step backwards as far as user interface goes.
Hi
If I understood you correctly , you can run DTS (SS2000) from SS2005. There
is a folder called Legacy under Management folder.
<jmirsky@.hotmail.com> wrote in message
news:1139956308.785445.51930@.g43g2000cwa.googlegro ups.com...
> Not sure why Microsoft would remove from SQL 2005 DTS import data
> sources all the default drivers available to ODBC but they did. In SQL
> 2000 the drivers available to ODBC were by default available to DTS
> import. How does one go about getting DTS to recognize these drivers
> so simple data import can be accomplished.
> To import from a DBF file you now have to create an ODBC data source
> Every time you want to add a new file and then to add insult to injury
> they make you key in all the ODBC information for the import source
> instead of giving you a simple drop down.
> Seems like a huge step backwards as far as user interface goes.
>

DTS in SQL 2005

Not sure why Microsoft would remove from SQL 2005 DTS import data
sources all the default drivers available to ODBC but they did. In SQL
2000 the drivers available to ODBC were by default available to DTS
import. How does one go about getting DTS to recognize these drivers
so simple data import can be accomplished.
To import from a DBF file you now have to create an ODBC data source
Every time you want to add a new file and then to add insult to injury
they make you key in all the ODBC information for the import source
instead of giving you a simple drop down.
Seems like a huge step backwards as far as user interface goes.Hi
If I understood you correctly , you can run DTS (SS2000) from SS2005. There
is a folder called Legacy under Management folder.
<jmirsky@.hotmail.com> wrote in message
news:1139956308.785445.51930@.g43g2000cwa.googlegroups.com...
> Not sure why Microsoft would remove from SQL 2005 DTS import data
> sources all the default drivers available to ODBC but they did. In SQL
> 2000 the drivers available to ODBC were by default available to DTS
> import. How does one go about getting DTS to recognize these drivers
> so simple data import can be accomplished.
> To import from a DBF file you now have to create an ODBC data source
> Every time you want to add a new file and then to add insult to injury
> they make you key in all the ODBC information for the import source
> instead of giving you a simple drop down.
> Seems like a huge step backwards as far as user interface goes.
>

DTS Import Slow from As/400

I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jmWhat's the data in on the AS/400?|||JDE.. But the file isn't vanilla JDE.. Actually, I just create a File using query first, then I use DTS to import/translate..|||Can't you dump the data on the 400 then just copy and load it?|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm

COULD BE YOUR NETWORK SERVICES, CHECK OUT HOW FAST IS WORKING YOUR NETWORK, IF YOU ARE WORKING ON WINDOWS, CHECK OUT TERMINAL SERVICES|||are there any tranformations?
how is your package transforming the data?
are you using activex operations to perform the x-foms?

activex is single threaded in dts that could slow down operations

what is happpening on the SQL Side once you import the data
are those tables indexed and do they have enabled constraints on them?

constraint checking can slow down an import
so can an index load.
Disable your constraints and indexes before you perfom a bulk load|||Thanks for all the suggestions..
I will look into each of them..
To answer one of the questions, yes I'm using terminal services..
thx
jm|||Originally posted by jmayo
I'm using DTS to import data into SQL. Client Access ODBC is the driver I'm using. Any reason why DTS would move so slow pulling data down.
I can run that same import from a local access database and it will
run three times faster.
thx
jm

We pull data out of the AS/400 here and there are two areas which will be the cause of performance problems.

1)The speed of the network connection.

2)the size of the tables you are transforming.

Not much you can do apart from increase your network bandwith and the cards on the SQL Server and the AS/400 (I think we have Gigabit Ethernet cards on each end)

For every thousand rows is takes approx three seconds - so if you are around this then you're doing OK.

DTS import problem with text datatype from SQL Server to MySQL table

Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17?
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".Can you not export the data into a text file and import it into mysql ?|||You also can export the data into a file with .csv and import it into mysql.|||Thanks rnealejr and gyuan

Even with export to text file and then import that text file to MySQL gives the similar error -

Error Source: Microsoft OLE DB provider for ODBC drivers
Error Description: The number of failing rows exceeds the maximum specified.Query-based Insertion or updating of BLOB values is not supported.

I have also tried MySQL connection with mysql-oledb-3.0.0 but it seems it has some problems as it swaps the database name and server name parameters each time I open for edit or test connections.|||Doesn't sound like a sql server problem...

try here

http://www.dbforums.com/f5/|||Originally posted by Brett Kaiser
Doesn't sound like a sql server problem...

try here

http://www.dbforums.com/f5/

Thanks
I have checked http://www.dbforums.com/f5 and found a similar posting but no useful replies.

How to import a SQL Server table with text column to a similar table in MySQL database?|||What type of cursor are you using ?|||All DTS defaults. I am using DTS package tasks and connection objects with their defaults.|||Has anybody transfered the data from SQL Server table having text column to MySQL database?|||I understand this could be a ODBC problem of handling the text data but
When I use "MySQL.OLEDB Provider" instead of "MySQL ODBC 3.1 Driver" to make a connection with MySQL in DTS package and setup a Transform Data Task between SQL Server(source connection) and MySQL(destination connection) I see all the MySQL tables appended with "INFORMATION_SCHEMA." in the dropdown list of destination table names in Transform Data Task Properties whereas there is no user or database named INFORMATION_SCHEMA in MySQL database.