H I´ve had some problems importing text files in different servers that really confused me I´ve installed SQL SERVER and built a database (using a script) to make it identical to the one I had in production server. The weird thing is that certain text files used at production server could not ...more >>

I had some great help from Allan Mitchell earlier this week. I am very much a rookie but have managed to complete a project with Allan's help. I have got the DTS 'bug' because of this small success. Now what I woud like to do is use the Looping and Archiving example on Allan's site but crea...more >>

I'm trying to copy tables and stored procedures from a hosting provider. It seems to work for the most part, but I receive this error: There is no such user or group 'name' Does anyone know what this error means? It seemed to work for all objects except on table....more >>

Two almost related items: 1. I have found how to set the values for these dynamically using VBscript, but how can I use these in a Execute SQL Task. For example (from a previous post) I want to collect the sizes of particular drives folders and files for capacity management. I use VBscript Fil...more >>

Hi, I would like to transfer all my DTS packages from one Server to another. I thought I would have to save all the DTS packages as structured storage files and ship across individually. However, I think I remeber (on a course) I was told that the DTS packages are stored in one of the system d...more >>

I want to use a bulk insert dts task to pull in a text file with 57,million records I am using about 30 records as test data until I get it to work It is the entire national Do not call list It is a list of phone numbers formated like thi 201,555999 201,555888 201,555777 I do not know what ...more >>

I have a VB.NET 2003 application that uses the DTS COM package from SQL Server 2000. The application installs and executes correctly for a period of time. At some point, the user tries to run the application again and it generates the error: "COM object with CLSID {10020202-EB1C-11CF-AE6E-00AA004A...more >>

We have Microsoft Access database (x.mdb) with "updated" column like 03/16/2004 having datatype "text" When I tried to import this database from SQL server, it poped up an error message...if data source Microsoft Access was selected, then it would not continue. If data source Microsoft Ac...more >>

I have a package that imports an unbalanced flat file, makes a few changes and exports the flat file. An unbalanced file is one in which each row can have a variable number of columns/fields with no column headers. The row terminator/delimeter is ~ and the field terminator/delimeter is *. Any c...more >>

Hi experts, I develeoped a activex by visual c# ,but when i register it as a COM object,it seem that i cannot call it in dts package. How can i deal with? Thanks in advance! victor ...more >>

Hi, I have an issue where I need to get data from all manner of different locations. Some from text files, some from ODBC, i'm trying to get as many different possibilities open as I can. My application gathers data from all different sources and centralises them in to a SQL Server database...more >>

I'm a new SQL server user. I'm attempting to copy the prod tables (prod server) to the test server with DTS Import/Export Wizard. The copy fails with "Failed to copy objects from the Microsoft SQL Server to Microsoft SQL Server" after approx 1.5 hours. I'm using the defaults on the wizard to "Copy ...more >>

Hi all I am using the following code to execute a DTS Pakage. It loads the package but it doesnt execute and it doesnt give any exception. If anybody have any idea on how to fix this behaviour please let me know asap. tr DTS.Package2 oPkg = new DTS.Package2Class() object pVa...more >>

Hello All I have one file arriving from a legacy system which as different types of records I want to extrapolate and load these records into different tables. The situation is as follows: Flag col1, col2, col3, col4, col5, col6, col7, col8 =================================================...more >>

Hi I want to copy some tables from an oracle database into a sql server database. I'm a total rookie on Oracle but as far as iI know I usually log in with a "service name", a user and a pw Now, when I use a DTS package with a Oracle OLE DB provider as my connection, the connection options are "s...more >>

Has anyone worked out the minimum set of permissions required to allow SQL users to create and/or edit DTS packages, without giving them admin access to SQLServer ...more >>

I'm getting an error when transferring an entire database (all objects, all data, drop all objects before creating, etc.) from one server to another. the process runs for 99% and then in the final stage 'Creating extended properties' I get an error. Double-Clicking brings up the following dialo...more >>

Hi everybody, When i try to open a Dts package from enterprise manager i get the foolowing error "Class Not Registered". The package opens normally....but at the left of the designer all Task and Connection objects have disappeared!! When i save and run the package from a visual basic file i...more >>

Has anyone else got the 18 column limit on tranformation to a text file? I have SP3 and if i have a select with > 18 columns the next screen that asks to derive destination columns from source goes BLANK afetrward (execute button). I can change to 18 columns and everything works fine. i ve...more >>

As DTS seems so much quicker than any SQL I write Is it possible to have a generic DTS task to pump any table from SQL server to SQL server from transact SQL? The annoying thing with Insert/Select is the ANSI_NULLS/ANSI WARNINGS for heterogenious query. Even though the servers are identical SQ...more >>

We'd like to be able to provide a simple batch file which will transfer an online database to another online location. Obviously, this is simple to do with a DTS package. Can a DTS package be called from the command line? (Failing this, I guess I could write my own program to launch the DTS pack...more >>

Is there a way to get more detail information - when a scheduled DTS job fails. By itself the job runs ok - but only fails when i scheduled and it runs - I have other DTS Scheduled Jobs that run just find on the same server. Thanks Dave ...more >>

I have 3 separate DTS to transfer tables. They transfer tables from 3 different Access databases into one SQL server database. But I would like to make them into one dTS so that I can just check on error log file. How can I do this? Thanks...more >>

I want to configure a single email task if any task in package fails ? Also I want to send in email which step failed. Any ideas or pointers ? Regards, AK ...more >>

Hi All; I've created a DTS Package on SQL Server 2000 and it is working fine in Enterprise manager. I saved the package as VBScript because I wanted to use it and run it from within a VB App that I am working on. When I call the script it creates the table correctly, but it then dies when it ...more >>

Is there a way to specify a 'Select Top N * From <Table>' query in DTS where N can be set as a parameter (or global variable) during runtime? I tried Global variables but it seems to take those only in the Where clause. All inputs are appreciated. Thanks!! Chumma Dede ...more >>

I have a DTS package. It has an ActiveX Script and a Data Pump with workflow to execute the ActiveX task first and the Pump after a success. The package works great running from DTS Designer. We are launching this package through a Delphi program much like using VB's "DTS.Package" component. It ...more >>

OK done a very quick test Excel table looks like Col1 Col2 Col3 I am Here Yes you Are SQL Server table looks like CREATE TABLE Exceltester( col1 varchar(20), col2 varchar(20), col2Again varchar(20), col3 varchar(...more >>

I have installed the SQL 2000 standart client tools on a few desktops, so they can make updates as necassary. Everything works well, however they can not run any DTS package using this method of execution. They get an error refferring to invalid access. Is their a way to do this or a wa...more >>

Ok, I beginning to get a clue after much reading and newsgroup help, thanks. I have another question to compliment my understanding. In an ActiveX task that uses this logic: Function Main() DTSDestination("field1") = DTSSource("field1") DTSDestination("field2") = DTSSource("field2...more >>

I have a folder made up of 100 (or so) .dbf files. The number of files can change and I was wondering if it's possible to use a DTS package to import all the files in the given directory into tables in a SQL database? Thanks....more >>

Hi Could someone help me to find a way to get the error number and message of a failed step of my package Merc ../Ména...more >>

I am trying to create a DTS package that will transfer raw data from one server to another. I need the package to be able to run on various servers at different times. For example on Monday the package will transfer data from server A to server B, on Tuesday I want the same package to do the sam...more >>

Looping, Importing and Archiving found at : http://www.sqldts.com/?246 This fails after importing 4500 of 6000 files. It never completes the task. Is this just the typical way that most Microsoft software works or is there a way to make it run to completion?...more >>

Hello, I have an SQL Server 2000 aplication with DTS import jobs scheduled each night, to import basic text files. These jobs first call a DTS import work (copying data in a temporary table, and making "simple" transformations) and then execute a stored proc to import and manage the data in t...more >>

Requirements for an upcoming data load process that will be used over and over again. The company will pass a "Header" and a "Trailer" to indicate how many rows should be received and the trailer to indicate that the file is complete. Any ideas how to read such a thing to read just those two r...more >>

hi i am trying to use "Generate SQL Script" to script out ONLY the indexes and keys defaults and contraints, i do NOT want to generate anything else as these already exist in the destination database that im going to run the script on. what options will i need to tick on the "general" tab in orde...more >>

I used DTSRun utility in the stored procedure. I put some extra spaces after the package name, such as: DTSRun /S "server" /U "sa" /P "psd" /N "PackageName" /A "global_variable" It works fine in all the msde2000 servers I have except one, which complains about can't fin...more >>

I have the following vbscript that is generating a RUNTIME error on the last MyFile.Write(xml.transformNode(xsl)) Set xml = CreateObject("MSXML2.DOMDocument.4.0") Set xsl = CreateObject("MSXML2.DOMDocument.4.0") xml.async = false xsl.async = false xml.validateOnParse=False xml.lo...more >>

I have a DTS job that runs every hour. it truncates destination table and copies from the source table. it has failed with the following error in view job history "error-214721900(80040E14);provider error 14262.(37B6)" error source Microsoft ole db provider error pls help in this how...more >>

A DTS package runs manually fine, but I get an error when it is scheduled as a job through the SQL Server Agent Job Error= -2147220472(80040408) Error String: The Data Pump Task requires Transformations to be specified. Error Source DTS package. Help file: sqldts80.hl Help context:4700 Error De...more >>

I'm importing an Excel file into SQL using a DTS package. There's a column in the Excel file called 'Description.' There are two fields in my SQL table called 'DetailDesc' and 'ShortDesc.' Both of these fields point to the same 'Description' column in my Excel file. The only exception is that...more >>

I have a sql script that can be put in a dts or it can be put in a sp. I can schedule both as jobs. What would give better performance? Thank You...more >>

Is it possible to use DTS to import from sql to Oracle? Text to CLOB? Image to BLOB? If so, how?...more >>

Hi, I'm trying to use a DTS transform task to output the results of a query that has the For XML syntax. I define the source and make a text file destination and everything seems to work fine but when I open the output ..XML file it is just a bunch of Hex data. Is there some type of pre or pos...more >>

I have a program at work that keeps crashing. It first creates a DTS package and imports files into our server. If the program does not create the DTS package everything works fine ( I believe ). However after the DTS package executes correctly I receive an " The process cannot access file "C...more >>

We recieve electronic reports ( txt files ) which i need to import into a table. the report can be n number of pages. Sometimes the report will contain records which i also need to apply to another table It is possible to import the entire report into a single table field ( unchanged ), but also s...more >>

hi i have recently migrated from sql 7.0 standard edition to sql 2000 standard edition, this went ok and i ended up with 4 user databases and the usual system databases. I then encountered problems with the system and user databases due to them being different collations, so i used the procedure ...more >>

Hi We are using SQLSERVER 7.0 can I extract data from xml file using DTS? if yes, can we schedule it. Secondly, if you more than one xml files in a folder to be extracted, is there a way you loop all of them extract the data one by one from those xml files and insert into the database? T...more >>

Trying to import a file from old SQL7/WIN2K server to the new SQL2K/WIN2K3 server. Import a big delimited text file into the new SQL2K, got 'not enough memory' error. Has never had any problem like this in the SQL7 before, and this table gets repopulated by importing the text file every midnight....more >>

When I syncronize my tables, I get this error: Cannot insert duplicate key row in object 'POHFIL' with unique index 'IPOHFIL0'. (Source: CMTSQL2 (Data source); Error number: 2601) What do I do? the help was not much. I am trying to replicate tables between two SQL servers. Thanks for an...more >>

when i try to run a sql server package from visual basic, i get the following message: Execution Started: 3/16/2004 11:31:54 AM Error at Destination for Row number 26276. Errors encountered so far in this task: 1. Error Source: Microsoft OLE DB Provider for SQL Server Error Description:The s...more >>

Just encountered a strange "bug" and still trying to understand. In a nutshell: Both machines are on Windows 2000 Server. Both have SP4 installed. I pushed a DTS package from one server, to the other. The other would report that it ran, with success. However, all the steps did NOT fire of...more >>

Looping, Importing and Archiving found at : http://www.sqldts.com/?246 How can the package above be re-written to append data from multiple files to one sql table, instead of importing the entire file again? Thanks...more >>

Hi there, I have a DTS package which copies on a daily bases 5 db's from 1 server to another. Every now & then the job fails on 1 of the tasks with the message: [SQL-DMO]CreateFile error on 'servername.dbname.DP2'. The system cannot find the file specified. I have to edit the DTS package: ...more >>

I have been struggling with a Data Driven Query Task. The Source is a text file I been working through it slowly and am getting an execption log which so far only relates to transformation task Transformatiion task 8 is and active x task '*****************************************************...more >>

Hello Experts, I expand from Enterprise Manager the Data Transformation Services and on the right side of EM (which other DTS Pckgs Listed), I did a right click -> New Package, a message appear that says "Specified filename does not exist", when I pressed "Ok" the DTS Environment Loaded ...more >>

Hello all, I think this is a simple question, should I be able to see the views lited in my dropdown destination list of tables when importing data. If so is there any simple reasons why I may not be able to do so? thanks alison...more >>

How can I force a package to fail? I want to transfer data from DB1 to DB2. If the table in DB1 is empty then fail, else continue. DB1 and DB2 are on different servers and because of our environment we can't create linked servers. A similar approach, how can create a transaction betwee...more >>

I have an SQL server 2000 on a Win2K server ... and I set up a job to generate a report every minute, after a while I get the following message (when I get it) ... 512 RAM and plenty of free hard drive space Microsoft SQL-DMO (ODBC SQLState: 42000) Error 22022: Unable to post notificati...more >>

I have a DTS package which utilizes an "Execute Process Task". This task calls a batch file on the server. I keep getting this message whenever I try to run the package: "CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified Succ...more >>