DTS Problem

Hi Folks,
I have a two node cluster and I have a DTS package, which transfers data from a text file to the database. The problem is like it runs fine when the cluster is in its base node(Node1) but when ever the cluster fails overs (to node2) the dts job fails saying that the path not found.

1. The drive is a dependent resource to sql server.

2. When I try to run the DTS package itself (from node2) its running fine.

any insights guys?

with smiles
santhoshHow is the file referenced in the DTS package (ie, is it UNC)?

Is there possibly a permissions issue (Log in to Node two as the SQL Service account and attempt to run the DTS package)?

I have a similar set up with a two-node cluster and a DTS package that references a cluster disk. No issues that I am aware of.

Please provide a bit more detail.

Regards,

hmscott

DTS Performance Issues?

I have roughly ~80 machines periodically performing DTS transfers to a central server. The central server apparently can't handle this load. When watching in the performance monitor, after about one hour of being online, disk use is steadily pegged at 100% and the server becomes unresponsive. How do I track this down further and further diagnose/resolve this performance issue.

I can look at the current activity window but I don't know how to interpret or use the results.

thanks!Roger,

I wonder would it be easier to pull the data rather than push from the 80+ clients ? At least the server would be able to schedule them sequentially.

How much data are we talking about here ? Improvements might be made via indexing etc on the server table side ?

Mark|||Just a thought, may be you can export the data to a media as files and have the server import the data from the media as file import one by one.

DTS packet size

I have a dts package that transfers data from a view into
a table structure on another server. Occassionly the
scheduled package will fail with the following error:

Error = -2147467259 (80004005) Error string:
[DBNETLIB][ConnectionRead (recv()).]General network error.
Check your network documentation. Error source:
Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider
Error: 11 (B) Error string: [DBNETLIB]
[ConnectionRead (recv()).]General network error. Check
your network documentation. Error source: Microsoft
OLE DB Provider for SQL Server Help file: Help
context: 0 DTSRun OnFinish: Copy Data from PS...
Process Exit Code 1. The step failed.

Once the package fails I will rerun it several times
without any modifications and eventually it will run to
completion successfully. One change that I have made while
troubleshooting is to change the default network package
size from 1 to 1024 or 2048. Although this has worked I'm
not confident that the package wouldn't have run without
that change.

Has anyone seen this error before or anything similar?Default network packet size is 4096. How did you manage to make it 1?|||Under the Data Link Connection tab the default packet size is 1. It gives you the option to Edit the value so I bumped it up to 2048.

DTS Package - Append Results to Text File

I have created a DTS package which transfers data from a SQL 2000
server table to a text file. Each time the package is executed the
data in the text file is replace with the new data from the current
execution of the package. Can DTS append new text to a text file and
not replace it each time the package executes. Is there an option or
seeting for this? I havent seen one. Can anyone help me out on this.
Thank you,

Brett"bdwgarth" <bwalke@.lbrspec.com> wrote in message
news:1105716432.160263.235590@.c13g2000cwb.googlegr oups.com...
>I have created a DTS package which transfers data from a SQL 2000
> server table to a text file. Each time the package is executed the
> data in the text file is replace with the new data from the current
> execution of the package. Can DTS append new text to a text file and
> not replace it each time the package executes. Is there an option or
> seeting for this? I havent seen one. Can anyone help me out on this.
> Thank you,
> Brett

http://mssql.meetholland.com/message/162162.aspx

Simon|||I doubt it will let you handle merging file text thru DTS. Anyone
correct me if I am wrong..!

DTS Oracle Select

Hi there,m

I have a DTS with an Oracle Connection. I transfers some data from oracle Db to SQL Server DB. I need convert my Oracle query into a parmerized query, i mean that the field [T1."DELV_SHIP_DATE_KEY"] can receive the dates like parameters.

I'd try to put:
T1."DELV_SHIP_DATE_KEY" between ? and ?

but it show me the next error when i try to set the parameter info:
Provider cannot deriver parameter information and
Setparameterinfo has not been called

Any idea??,
Maritzita

I use this select statement (for Oracle):
select T1."DELV_ID" "DELV_Delv Id",
T1."CUST_ID" "DELV_Cust Id",
T1."SHIP_CODE" "DELV_Ship Code",
T1."WHSE_ID" "DELV_Whse Id",
(T1."DELV_SHIP_TIME"*1.0e0)/100 "DELV_Delv Ship Time",
T1."DELV_SHIP_DATE_KEY" "DELV_Delv Ship Date Key",
T2."DELVI_ITEM_NO" "DELVI_Delvi Item No",
T2."ORD_ID" "DELVI_Ord Id",
T2."ORDI_ITEM_NO" "DELVI_Ordi Item No",
T2."DELVI_QTY" "DELVI_Delvi Qty",
T2."PROD_ID" "DELVI_Prod Id",
T2."DELVI_TOT_WGT" "DELVI_Delvi Tot Wgt",
T2."DELVI_TYPE_CODE" "DELVI_Delvi Type Code",
T3."ADR_CMPY_NAME1" "ADR_Adr Cmpy Name1",
T4."PROD_DESC_TEXT1" "PROD_Prod Desc Text1",
T4."PROD_ALT_CONV_FACTOR" "PROD_Prod Alt Conv Factor",
T5."ORDI_QTY" "ORDI_Ordi Qty",
T5."ORDI_ASM_GEN_ITEM_NO" "ORDI_Ordi Asm Gen Item No",
T5."ORDI_IS_UNIT_WGT" "ORDI_Ordi Is Unit Wgt",
T4."PROD_MKT_GROUP_CODE" "PROD_Prod Mkt Group Code"
from "VPEMGR"."DELV" T1,
"VPEMGR"."DELVI" T2,
"VPEMGR"."ADR" T3,
"VPEMGR"."ORDI" T5,
"VPEMGR"."PROD" T4
where T1."DELV_ID"=T2."DELV_ID" and
T1."ADR_ID"=T3."ADR_ID" and
T2."ORD_ID"=T5."ORD_ID" and
T2."ORDI_ITEM_NO"=T5."ORDI_ITEM_NO" and
T2."PROD_ID"=T4."PROD_ID" and
T2."DELVI_TYPE_CODE"='IQ' and
T1."DELV_SHIP_DATE_KEY" between '20030501' and '20031231' and
T1."DELV_SHIP_TIME"/100 between 0.00 and 24.00If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||I saw this solution on help from SQL, but i culdn't understand it so much. Can u tell me where i can find more information about it? and if i use this funcionality, how i have to do for pass this results of my query to my SQl database without using a data transformation task?

I have many quetions about it.

tks,
Maritzita

Originally posted by hmscott
If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||Look under OPENQUERY in SQL Books On Line. They give an example that specifically shows an Oracle Server. I think that the only thing you will have to do is build your Query String (with the substituted parameters) as a variable. I'm not positive, however, that this will work. Example:

DECLARE @.SQL Varchar(4000)

SELECT @.SQL =
'SELECT Columns
FROM MyTable
WHERE MyDate BETWEEN TO_DATE(' + '12/4/2003' + ') AND
TO_DATE(' + '12/5/2003' + ')'
GO

SELECT *
FROM OPENQUERY(OracleSvr, @.SQL)
GO