Hi, We are trying to schedule a DTS Pasckage.... When we gave DTSRUN /S (our server name) /N (our package name) , we are getting the error as " Incorrect syntax near '/' ". We are using SQL server 2000 and windows authentication. What is the command do we have to use for this case ? Thanks...more >>

First of all, credit goes to http://www.sqldts.com/default.aspx?246 for helping me with this idea. Basically I have several files I wish to import - files are in different format so I can really use a datapump, so I was going to use a Bulk Insert Task and change the DataFile and DestinationTableNam...more >>

I'm trying to create a DTS package dynamically with T-SQL. The package loads an ASCII file (DTSFlatfile) into SQL. I can get the package created, but when I execute it, the package fails. The following is in the error log for all columns : Microsoft Data Transformation Services (DTS) Data Pu...more >>

I am trying to export data from a SQL Server 2000 DB to an Excel worksheet. I am using DTS with SP3a and am having a problem with NULL values. To be specific, I have a 4-column table. Several rows in this table have <NULL> as the value in the third column, with pertinent data in the 4th column. ...more >>

How to import/export stored procedures in SQL Server?? I used DTS, but it only transfers all tables, excluding views, stored procedures, etc... please help. thanks! ...more >>

I am trying to schedule a monthly dts job with the following parameters on the job scheduler: "monthly", day "1" of every "1" month down below : i clicked : occurs only once at: "03:00:00 am". however, the job does not start at the time defined. (the job is enabled, s...more >>

Hi All If I run the DTS package from the server console it runs ok. A job calls a SP which is really just a wrapper to run the DTS package. When that runs it says "Fatal Error: Failure to load DTS package '<packagename>'" Any ideas? Many thanks J...more >>

How do I restore a DTS pacakge using the MSDB?...more >>

i have to export customer sales data into a text file. the file name has to be the customer ID ==> 123456.txt the first line has to be the customer ID followed by the sales data. 123456 415.25 666.90 -25.32 996.21 and so on... can i use one of the DTS tools - not the wizard - it has ...more >>

I just had to upgrade my version of EM because my hosting company moved my SQL 7 db to a SQL 2000 server. Now none of my scheduled jobs work, neither local or the ones that connect to the remote db. The local db is still SQL 7, nothing has been altered there - tell me if that is a problem. D...more >>

I have a dts that basically performs a transformation from Access tables to SQL Tables. Daily there are mulitple Access tapbles with between 2k - 5k records in each. What is happening is that the .log file is contstantly growing. Is there a way to disable the logging of these transactions. Is ...more >>

Hi, Do you know if it's possible to execute a sql query like this in DTS : Insert Into Connection1.Table1 _ Select * FROM Connection2.Table1 WHERE _ Connection1.Table1.Field1 Not In _ (Select Connection2.Table1.Field1 FROM _ Connection2.Table1) So i have same tables in different databas...more >>

I am trying to run DTS on Oracle. My OLTP source database is on Oracle 8.1.7 and the destination is also Oracle 8.1.7. I am using Microsoft ODBC driver for Oracle. On an small table (144 rows) the package runs smoothly, takes only 5-6 seconds from command line. I tried to do some volume testing ...more >>

As a small non-profit we have been developing a web based application to be hosted by a 3rd party hosting company. We have been using MSDE for development, but do not have a license for SQL server ourselves since our hosting package includes access to a per-server licensed SQL server. How ...more >>

Thankyou for quick answer about my last question. More about 7.0 and 2000 sql servers: Can I move a 7.0 database from a server which has this different sort order to sql 2000 server which has this different sort order and do it with "Copy database Wizard" ? 2000 Installation white pap...more >>

Can I have two servers (one 7.0 and one 2000) with different sort orders and the same time having DTS running which transforms informations between these two servers / different databases ?...more >>

If I have a Transform Data Task importing a text file to a table. The table has 1 more field (Trancount)which is a Integer field, than the text file. Is it possible to use the activex part of the Transform Data Task and inset a number into the Trancount field for each record inserted an...more >>

I have a DTS that is reading a txt file. Part of the txt file is being imported into 1 table using a datapump task. That part is fine The second part of the file i read into global varibles and use a EXECUTESQL Task to inset them to another table. I wont go into the reasons for this. Thi...more >>

Hi I have a customer who is running a a Job that executes a DTS package. This had be running fine until 2 days ago when we were told they were having problems. Every time the job is run it Fails, When I try to execute it manually nothing happens and then when I try to re-create the DTS ...more >>

Hi, Ours is a product across multiple platforms and databases - Oracle 8.1.7 , 9i, SQL server on Solaris, AIX windows etc. I have to make sure that the DTS packages are maintainable. i.e I should not be maintaining multiple DTS packages - one for each environment. The strategy I have used in DTS...more >>

Hi, I've been having this problem. I'm trying to export a series of date and time from ms access to sql server but can only manage to export the date but not the time as the date and time are two separate columns. The error that I receive after execution in sql server is that there is a...more >>

It seems most DTS package manipulations are available programmatically, but I am not finding this one. Thanks for any clues (or confirmation it just isn't there!) Best regards - Lee Gillie - Spokane, WA ...more >>

We have a DTS package which we use to pull changed transactions from Oracle into a SQL Server data warehouse. The package steps pull the data from Oracle into a temporary table, then inserts the NEW records into the target, then updates the CHANGED records into the target. The CHANGED reco...more >>

Hello: I have created a DTS package that copies the objects of 3 SQL 2000 databases to identical databases on another server. The first 2 databases have no problem. The last database throws up the following error after the step runs for awhile: Microsoft SQL-DMO (ODBC SQL State 21501...more >>

I receive this error message when I try and run my DTS package: "Error opening datafile: The filename, directory name, or volume labe syntax is incorrect." I set the file name property of the Text File (Source) to a valid file, but when I run the package and it returns the above error ...more >>

Dears, I have a table with SQL Commands in it, and I want to create a result set with the values powered by these commands. I'm intending to create a stored procedure with a temporary table with that values and use it as a result set. Is it possible to make it using EXECUTE (@SQLCOMMAND...more >>

Hello. I have Execute SQL Task with SELECT * FROM ORDERS WHERE OrderID=10248 and I created two variables as f=10248 (int) and r=10249(int) Then I wrote on Active Script Task MsgBox "The order ID is " & DTSGlobalVariables("f").value Main = DTSTaskExecResult_Success Now I have couple quest...more >>

Hello ! I have a DTS on my SQL 7 Server, which import data from an Access database (mdb) into the local server.. I can run this DTS manuall on the server and it works fine.. I set it up as a Scheduled task to run every 10 minutes, but it always fails and returns this msg.. .... ...more >>

Hi, I'm trying to use dts to download data to an ascii file. The query I'm using is below: ----------------- SELECT generic_id, xml_transfer_site_id, enable_invoice_id, create_date, external_invoice_id, invoice_num, convert (varchar(20),invoice_date,120) invoice_date, enable_fro...more >>

Hello, From my client, through Windows Explorer, I'm executing a DTS on our network server(Win2000), but I get this error: "C:\WINDOWS>"\\acs4 \Binn\dtsrun" /Sacs4 /Ndts_Denso_CO_Item_Upload /E DTSRun: Cannot create COM Server to load and execute DTS Package. Error -21472 21164 (8004015...more >>

The above occurs when restoring data from HP Dataprotector 5.0 sql integration. Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) Pls advice....more >>

Is it possible to run SQL statements like drop proc and create proc through DTS? ...more >>

Dear All I have a problem to export a table to .dbf file. The table contain 6 Millions records. The output file is used for foxpro (DOS version). I tried using DTS export wizard but the process failed when reached 225000. The table consist of 32 column and most of the use varchar data type with two...more >>

How do i conver from varchar to float...more >>

Hi all- I'm trying to run a BULK INSERT through an Execute SQL task (I need to call it from Exec SQL to enable triggers). The problem I'm running into is that the import filename is variable. I'm calling the DTS package through a DOS batch file which cycles through a directory and calls DTSRU...more >>

I have a DTS package which transforms Excel spreadsheet data into table data. The problem I'm having is that DTS fails to properly parse Zip+4 entries. I specified the transformation as "Column Copy" thinking a straight copy of that data would suffice. For 5-digit zips it work correctly. Howe...more >>

Hello All, I'd like to run a sybase sp in a DTS package and insert the results in a table in SQL Server 7. I was able to create the connection successfully but do not know how to accomplish the insert. Thanks, -Melounjan...more >>

I was running a test of the DTS Import/Export Wizard. At the "save, schedule and replicate package" dialog box, I selected "Save DTS Package". For some reason, the "SQL Server Meta Data Services" radio button is disabled (i.e. cannot be selected). Can anyone help me get this button enabled? M...more >>

I am new to vb scripting and was looking for a way to look up a file and identify all the charachters between two delimiters and insert them into a field of a table.The file is having data limited by hex(07) delimiters and i am able to identify the first delimiter as followws,How can i identify ...more >>

We have some data source configured with ODBC connection. Is there anyway I can use like cursor in DTS package to access this ODBC data source? Or any way in stored procedure to access this ODBC data source and copy them over to local SQL server. Thanks, HG ...more >>

Im running an SQl server ( SQL 2000 ). I also have SQL 2000 on my laptop. I would like to be able to copy ' sync ' the data on both systems for a couple of tables, which enables me to make changes on my laptop or on the SQL Server and both will be updated with the same records. One thing...more >>

I have a table that I empty then re-import with a scheduled DTS package. What is the best way to designate a column as a primary key within the package details. Would it be possible to create a new column and make it a PK within the package? Help appreciated. Thanks. Frank *** Sent via De...more >>

I am working on large datasets, and would like to set commit statements after regular intervals, instead on one huge commit at the end. This commit should depend on the number of rows. For example if my total table size is about 500000 rows, I should be able to set my commit at 10000 rows. Is there...more >>

I have a dts package that copys information into staging tables. When the table are empty the package runs correctly. When i try to run it a second time with a select * from table where field+field+field not in (select field+field+field from table) I get a syntax error converting chartacter to ...more >>

After 24hours, I "killed" a DTS task that never finished, probably due to a bad index. By killed, I hit cancel on the DTS status window and after another two hours of waiting I opened Task Manager and Ended the process. Now I can't even get into any databases on that server! I tryed sto...more >>

Hi All Happy New Year. Inside a DTS package I have to implement a functionality that will enable me create and entry in a table and then collect the identity column generated. I have the following statements in the SQL Task. DECLARE @newID EXEC @newID = ps_AddDemoDTS03Control ?, ? SEL...more >>

Hi, We have a network in an ISP with 2 computer that their gateway is a cisco 1601 router. all IP adds of network are valid IP. after I install SQL server 2000 the router is not accessible and when ping it reqest time out is the result. what is the problem reason??? i'm really confused. Best re...more >>

I have stored procedure where i am passing two variable 1. @sMonth char(2), 2. @sYear char(4)) I have created a DTS package where i am passing the name of my stored procedure in execute sql task properties as exec test_dts ?,? in parameter i have define two parameter Name Type...more >>

Hi All I copied the data from a stored procedure to the excel sheet using import/export wizard by checking the option run immediately. It worked fine. As I want to Save this Package in Visual Basic and run the package whenever it is required. But When I run through an VB applicat...more >>

I have a situation where I need to allow unlimited errors and can't figure out how to set this beyond 9999. When a row doesn't meet certain criteria I send it to the Exception File which counts as an error. I have over 1 million rows I am trying to run through this Transform and approximately ...more >>

I have the following SP PROCEDURE sp_Write_Off_Select (@unique_id [char](15)) AS DECLARE @var1 nvarchar(1) Set @var1 = 'N' SELECT @var1 as include, Table1.* FROM Table1 WHERE Table1.[unique_id] = @unique_id This works fine within Query Analyzer and within an Execute SQL Task but I n...more >>

Hi All, How can I send(FTP) a file from a DTS Package, File name changes daily. eg 2004_01_02_Batch.txt Thanks for your efforts and guidance Vijay I Shall overcome...more >>

Hello NG Users! I have a MS SQL Server2000 SP3 in Office and 50 Notebook-users with MSDE 2000 SP3. I must copy role of server on Notebooks. I make by DTS Wizard, but copying process breaks off with error, although the role is koriert. On Notebook role is indicated, but all object with permissi...more >>

How can we make sure that DTS works across databases. For example in one of the Transform Data tasks I have a query which has to make use of outer joins. The strategy we has used is to keep our SQLs to ANSI standards. But I have still not found a solution to make outer joins which works across Oracl...more >>

Hello, I am on SQL Server 2000/sp3a. Not , a very elegant way to do, but, I have to develop a mechanism to get input data from user and then generate an export file based on that criteria (in WHERE clause). Ultimately, the package will be called externally from a program but for now, I need to p...more >>

hi, Sql Server 7 we have various jobs running. one of which copies data from one table on one server to another. recently i found out the job had failed when i went and looked at the history it says the job failed. the job was invoked by schedule 35. DTs run:loading.. DTSRun:Executing DT...more >>