Re: DTS on Sql 2005

Hello Travis,

OK so let's take DTS/SSIS out of the picture. What happens in SSMS? Same results?

What is the difference in the query plans between the two statements?

Is this DB an upgraded DB from 2K?


--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

Hi Allan ,

I manually open the DTS package and run on Sql 2005 after import.
When transform. Below is the query I pass before load.

But really strange , when I take out the "cast" , then the result
return in 1 seconds. If put in the "cast" , then the result never
return.

SELECT DISTINCT
CAST(DT_ID AS VARCHAR) + AGN_ID,
DT_ID,
AGN_ID,
AGN_CUR_ID,
AGN_SCH_BCYC_ID,
AGN_STS_ID,
AGN_CR_BAL_AMT,
AGN_CR_AMT,
AGN_CR_USED_AMT,
AGN_DIR_COMM,
AGN_GDS_COMM,
AGN_ONL_COMM,
AGN_ADD_ON_COMM,
AGN_NOTIFY_AMT
FROM F_AGN_TMP
WHERE CAST(DT_ID AS VARCHAR) + AGN_ID Not IN
(SELECT CAST(DT_ID AS VARCHAR) + AGN_ID FROM AAWH.dbo.F_AGN)
AND NOT (
(AGN_ID LIKE ('%HUALINTH%')
OR AGN_ID LIKE ('%NANTATH%'))
AND AGN_CR_BAL_AMT = 0
AND AGN_CR_AMT = 0
AND AGN_CR_USED_AMT = 0
AND AGN_GDS_COMM = 0
AND AGN_ONL_COMM = 0
AND AGN_ADD_ON_COMM = 0
AND AGN_NOTIFY_AMT = 0
)
"Allan Mitchell" wrote:

Hello Travis,

How are you executing the 2K package exactly?

What parts are slow? Is it the extract, is it the transform or the
load?

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com
|
http://www.konesans.com
Hi ,

I had import the DTS Sql 2000 to Sql 2005. When I run the package
and test the result , surprisingly the performance is very slow
compare to Sql 2000. What is wrong in there ?

The new server on Sql 2005 had 4 CPU , 8 GB memory and running on
windows 2003 enterprise. Already enable AWE and lock page memory and
set the max memory to 5 GB.

How can I ensure the new server is utilize the 5GB I set ? Can I
monitor the AWE in performance monitor and how can I do that ?

Many thanks



.

Relevant Pages

  • Re: SQL 2005, DTS, problems, problems, problems, etc..
    ... You must take the package in and out of BIDS to make changes but this could be as simple as deploying from the previous project. ... Like in SQL ... list of packages under the File System or MSDB, ... "Allan Mitchell" wrote: ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL 2005, DTS, problems, problems, problems, etc..
    ... "Allan Mitchell" wrote: ... calling my SQL 2000 packages via the "Execute 2000 package task" in ... a 2005 package. ... Backward compatibility/2000 DTS Components installed? ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS on Sql 2005
    ... Just wonder is there any CAST issue between 2000 and 2005 or not. ... "Allan Mitchell" wrote: ... I manually open the DTS package and run on Sql 2005 after import. ...
    (microsoft.public.sqlserver.dts)
  • Re: How to open sql 2000 DTS package in sql 7?
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > Our partner created a DTS package in sql 2000. ...
    (microsoft.public.sqlserver.dts)
  • Re: more detail on issue
    ... then have SQL and SQL Agent running as an ... When you installed the package to your SQL ... Server, did it prompt you for the password you packaged with? ... strings not being visible to the SQL Agent user. ...
    (microsoft.public.sqlserver.dts)