I am writing some ActiveX scripts using DTS in SQL server 2000. I notice there doesn't seem to be a Find command so one can find some code as I would if I were using VB or Access VBA. I assume others have experienced this. What solutions do you suggest? ...more >>

I have a simple DTS package that executes a VB program that I have created. If i manually launch the DTS package, it works with no problems. After I schedule the job, it does not run - it looks like it starts, but it stops before the exe is completed. It does the same thing if i click on t...more >>

I am trying to use DTS load Excel data to SQL Serve The Excel Spreadsheet has 100 columns in each row. Let say I have 3 rows in Excel sheet Each row has 100 columns as follows column1, column2, ...column10 Now I want to load it to a table which has only one column The first record will be t...more >>

Firstly i must give big thanks to Allan for all your help. I have now hit a brick wall and am getting the same results no matter what i try I have an import Text file that contains 10 rows of header data ( Lenghth 15 bytes ) and N number of transaction rows containing a single string of d...more >>

Hi all, I know this is a common problem but through all of my searches on the web; none of the examples work. The problem is I have a directory on a drive somewhere that contain a number of CSV/EXCEL Files. The names and number of these files change on a daily basis. I need a method with...more >>

Hi all, I m trying to use BCP utility to upload a huge text file. As per microsoft documentation, -R is used to specify the regional settings. My computer (client) and the sql server (running in a network machine) has dd/mm/yyyy as the regional settings. The incomming text file also has d...more >>

I have a simple query Select * from Table1 where Licence = "123456" I am exporting the result to Global Veraibles which works fine. Hope can i set the Licence = ?? at runtime Any help is most appricated...more >>

hi there, is there a way to do a search on dts? is there way to find dependency of the DTS? Thanks Regards, Terry...more >>

I am trying to connect to a database using the function i have included below. Trouble is i need to find out if the connection was made before proceeding. Can anyone suggest how i can find out if the user connected sucessfully and return True / False as the case may be Function OpenDa...more >>

hi, can DTS extract data from XML file. Can DTS populated data to XML file. Warm Regards, Kitty...more >>

I have a data driven query task that utilises the multiphase pump functions to insert and delete rows from a table. Can anyone tell me if it is possible to get the row count for the action queries during the "row transform function". The query executed during the row transform function phase is base...more >>

I'm scheduling a DTS package but want incorporte a function deleting a file from a folder. Does anyone pointing me how to do it in DTS, or VB ActiveX task? ...more >>

Hi, does DTS supports pipeline parallelism? Supports data partitioning?...more >>

Hi, Is DTS able to handle aggregation functions and specify memory cache sizes as well as directories within the GUI (no coding)?...more >>

how can i insert the parameter date() in a criteria of a fiel date? thanks ...more >>

I like to log processing messages to a file, and modified the server properties in Analysis services to point to a file. This works fine when I run processing jobs through the Analysis Manager. However, when I run the same action through a DTS job on SqlServer it does not write to the log. A...more >>

Hi, i have several hundred text files that i need to import into a SQL driven Database in thier own tables. Each table name is taken from their filename. Is this possible through DTS? if so how and can you point me to some links? Many thanks Simon ...more >>

I am trying to run a DTS package from a Stored Procedure that uses the FTP Task. When I try to run the Stored Procedure, my DTS package returns the following error: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_D...more >>

Hi, I would like to create a dynamic column mapping in DTS, how can I do it? I will receive a file and use dts to load into the table. The table has all the columns which include all the situations. But, the column from the file I receive is dynamic and it means some columns is missi...more >>

Hi, I have DTS packages that I saved as "structured storage files". I will need to create the same packages at another server, is there a way to create them as a batch job, instead of creating them one by one using the enterprise manager? Thanks Biray ...more >>

Hallo NG, I generated an ActiveX-Script Task. The Problem, I can't update the recordset anymore. Following error occurs: "Identity cannot be determinded for newly inserted rows". -------------------------------------------------------------------- Setting connection... Opening recordsets.....more >>

I am currently experiencing a weird phenomenom When trying to open a dts package, I am getting a blank dts designer window. No task bar on the left, no menu on the top, no steps in the window I know there is information in them, I've tried opening them all on all of the different servers we hav...more >>

Good Morning I have a question. I have a dts that export a view of sql server 2000 in a file xls. On the server SQL there is not Office(I think is not important). If I manually start direct the dts all steps go well. If I schedule the dts and then start the job ,fail. Why? The destinatio...more >>

Hi all, When I try to import an excel file that has a column with numeric AND text data in it, I get lots of NULLs imported. Mu column contains values like "101, 102, 105, 343, 101-34, 304-44, Rosemary, Other text" sometimes, the numeric values are null and at other times the text valu...more >>

I have a <file>.vbs file that builds a .js file that I am using for a website. Can I use DTS to run the <file>.vbs file? If so. how?...more >>

I have a process that runs on a remote machine, that rebuilds many tables that will get imported into SQL Server 2000 using a DTS package. Both the rebuild process on the remote system, and the execution of the DTS run on schedules that are corrdinated with each other. Occassionally, the...more >>

I am trying to bcp a table to a flat file, and the output does not contain some french characters that are in my data. It replaces them with some invalid characters The command I am using is bcp tablename out c:\data\tableName.txt -c - The -c and -6 are necessary due to some restrictions on the ...more >>

Is there any published information on the features of DTS in Yukon? I get the impression that there are some significant enhancements. Dick Campbell ...more >>

I'm migrated a set of DTS packages from a Windows 2000 server running SQL server 2000 onto a Windows 2003 server running SQL server. I have a DTS package that exports to a text file whose target is a mapped network driver. The W2K server works fine both running the DTS packages and when it...more >>

Hi I have a DTS package which imports data from a text file into SQL server. I am using Data driven query task for the same. I dont want to execute this task if the source text file is not available. What is the best way of doing this? I think multiphase data pump can be used here but not sure as...more >>

Hi I'm setting up a dts job that will import the data from a heterogenous table to a the same table in SQL I want to know if it makes a difference if I use the sa account to setup the connection on the SQL end or should I setup another SQL account with minimal priviledges to make the connection? ...more >>

While trying to export data from an Access table to flat file, DTS will crash Enterprise Manager MMC and return a memory error when there are a large number of columns in the table or select statement. The GPF occurs when you try to define the destination. Is this a bug or am I doing somethi...more >>

We use an Access2000 database as a front end to combine data from several different datasources (Sybase - SQL Server - Access - etc.) We have a DTS job that processes some of the SQL data that we use. The only problem is that someone (usually me) has to execute it when things have changed an...more >>

Hi there! My DTS task is to import several files and compare the data within a loop. I found this very good example 246 on sqldts.com (thanks guys!) but there is one problem I still have to solve. I don't want to store the differences between the original and the new data in a table but i...more >>

Hi, i have to import several hundred files into seperate tables, each text file name is the name of the table that i wish to create. is this possible? if so how? and where can i find info on this? many thanks Simon ...more >>

We have some packages that were created by a former employee. We use the packages daily, so I can't delete them, but I would like to be able to change the owner of the pacakges so that we can totally remove login. How can I change the owner of a DTS package without having to re- create th...more >>

Hi I am using an FTP Task to extract large files- some are more than 8,500KB. Is there a restriction on the size that the task can handle? While the task it executing it does begin to put a file on my local directory, but when the execution completes the file "disappears." For the brief time that...more >>

When importing multiple text files into a SQL server table by DTS Transform Data Task, can DTS package detect the damaged text files (such as no charaters, or wrong format) automatically? How to do that? Thanks in advance. -Don ...more >>

Hi does anyone know how I can automatically assign column mappings at run time. At the moment I have a DTS scheduled package to run and the maximum number of columns that can be transformed is 18 but sometimes it can be as little as 9 but when there is not 18 columns the package fails. I am tr...more >>

Hi all, Is there a way to capture Field Mappings between Source and the Target into a text from the DTS Transformation? It's possible to view the transformations but if there is an option to print, would be great! Can anyone give a suggestion. Thanks Kris....more >>

Hi, Can anyone tell me how to upgrade DTS Repository Packages from SQL Server ver.7 to 2000? MS Installation guide says that Packages don't get upgraded automatically in the process of upgrading databases from 7 to 2000! Correct me if I am wrong! If it is true! What would be the best way ...more >>

I have a DTS that I am trying to schedule for 07:00:00. However after I set the schedule (using enterprise manager), if I go back in and look, the time is reset to 00:00:00 ?? I tried setting it and not going back in and checking whether it had stuck, hoping it was some display glitch, but th...more >>

Hi all, I have created a DTS package to transfer data from a some tables in a Access database to SQL server. I use a transform data task and specifiy a source and destination table and use a activeX script perform the source/destination mapping. Because the data source is a union of some tables...more >>

I am atempting to write a DTS to pick up text files from a derectory to be imported into two tables Although all the files will be in the same format, they will all be have differnet names and extensions. The DTS will be running as a task every few minutes, Is there anyway to 1. build ...more >>

Hi, I am trying to bcp out one table from one server to another server: bcp "dbname.dbo.tablename" out "\\remoteservername\remoteserver_SHAREdrivename\bcp process\tablename.txt" -Sremoteservername -N -T >>BCPOUT_tablename.LOG I ran the code in command promp. this table only have ...more >>

Hi, I want use a DTS job to append to a text file at a given interval. I can create the job OK but have not found a way to sucessfully append the file, only drop and recreate the file with the new data. Any Help? Mark...more >>

Hello.. I am able to create dts packages... but i am unable to schedule the dts packages. I know you have to be an sa to schedule the jobs.. but why does it allow you to create a dts package with your login being the owner.. but does not allow you to schedule them \ view them. Are the...more >>

I have SQL Server 2000 on Windows NT server machine. In one of my DTS I have to write an active X script to get some data from the database. How do we connect to database and read a table in active X script? Thanks JY ...more >>

I am trying to use the Copy Database Wizard to copy all the databases from one SQL Server to another, but I am getting the following error after I select the destination server and clicked next. Error code:5510 Error category:5500 Source: Microsoft SQL-DMO Description: [SQL-DMO]The name 'd...more >>

Can I do this and if so what do I use to get at the logs? jc ...more >>

I have imported a flat file to a sql table using a DTS. I now want to go thru that table and either insert (if new) or update (if they exist) the records into another table within the same DTS. I can do this easily using a VBScript task, but was wondering if there was a more efficient/easy way...more >>

Can I execute a SP (Stored Procedure) while transfering data from a .CSV file to a table ? To explain this in detail: 1. Say there is a .CSV file which contains the following Fields, Bank_Code Address1 Address2 City Country 2. In a table named "Bank_Master", I am h...more >>

I am trying to call a DTS package from an ASP page. I didn't want to go through the permissions nightmare so I created a DLL and installed it under COM+. I am calling it from an ASP page and getting the following message: Package FHLSubs failed Error in Object Execution Number: -2147417...more >>

All, i have a folder which i need to import each files seperatley and then run some administrative tasks / reports on each table. my question is how can i use DTS import each file into thier own table? any links or advise would be greatful. Simon ...more >>

Hi Ng, is there a possibility to create a SQL-Script to Create a DTS Package (Like Creating Tables, Views, Procedures)? Thanks in advance. Daniela ...more >>

Hello, I have a DTS package that contains an ActiveX script. This script compacts an Access database. If I have the package open in the designer and run it everything works fine. The Access database is compacted. However, if I run the package as a job it fails. I see the following e...more >>

I am imported a CSV text file (comma) delimeter, produced from a third party piece of software. All text fields as surrounded by double quotes E.G. "SMITH","JOHN",55.12,8.9 I have millions of records and wanted to use BULK INSERT excectuted as a SQL statement, as this is the fastest. ...more >>

Hi, Has anybody had experience using dts with oracle on windows 2000 ? We have sql 2000 on windows 2000 and oracle 9i on windows 2000. Using dts to send data to oracle is extremely slow, coming from oracle to sql is twice as fast, but still relatively slow compared between transferr...more >>

Does anyone know if it is possible to use DTS to insert data into a Lotus Notes database? The Parse/Show Parameters button on the Query Tab does not work when a Lotes Notes database is specified as the destination. I'm useing the NoteSQL 3.02c ODBC driver from Lotus. Please advise....more >>

How do I backup and restore a DTS package? ...more >>

Hi, I have a package that contains an FTP task on SQL Server 2000 SP3. I am trying to load the package from a console application in C#. Following is the code. ############Code starts here############################### Package2 p = new Package2Class (); object o = null; p.LoadFromS...more >>

Hi, I have a very strange problem using bcp. I'm using bcp to export data to text file. I have a .fmt format file which specifies the layout. The problem is that on one server, when it exports the data to a text file using a delimiter, the data when opened with Notepad looks like garbage lik...more >>

I have a DTS package which creates a connection to a SQL Server database table. It then writes the contents of the a table in the database to an ASCII text file in a folder on the same machine using a Transform Data task. My NT domain account is the owner of the package and my account ha...more >>

I am trying to save a DTS package to Meta Data Services. The problem is that in DTS designer, when I 'save as', the location dropdown does not even list Meta data Services. I do see Meta Data Services in the Enterprise Manager tree. What gives? Any info is appreciated. thanks, -marty n...more >>

Hello all. We have a dts package that has 2 global variables, which will be used in the WHERE clause of a SELECT statement in a data pump. However, we don't know how to differentiate between the two, because both are referenced using the "?" question mark. Here's our WHERE clause, with PARAMETER1...more >>

Hi, Can any body help me out how to execute a single task in dts package through programetically. I tried with the method object.Execute pPackage, pPackageEvents, pPackageLog, pTaskResult but iam unable to do it. Thanks in advance Sreedhar. ...more >>

I'm using a fixed length fields in a DTS package to import a text file. Each line in the text file ends with a CR\LF delimiter. The lines do not all have the same length. Blank fields after the end of the last field with data are not filled with any characters - the CR\LF delimiter simply occ...more >>

Hi, There is supposed to be a Mail icon under Control Panel to create mail profiles. On my standalone Windows 2000 Server there is none! I tried re-installing it but still the icon does not appear in control panel. I need it to send maiL from SQL Server. Some other info: W2K Servic...more >>

hi, all experts: I have a sql server 2k running on win2k server. I have one user database running on this server which support our help desk application. I have a sql server 6.5 running on winNT4.0 server. Since sql server 6.5 is no longer in support by microsoft, we decide to migrate this...more >>

Hi all, I have an SQL Server machine, if I have a specific table, or tables, can I create some type of DTS script that replicates or appends all data to JDE at a certain hour during the day? Our JDE runs on AS/400. Thanks...more >>

After applying SP4 to SQL SERVER 7, we see and odd problem relating scheduled SQL Agent jobs. The jobs start at the time they are expected to, and the steps are completed ok, according to the DTS log. The symptoms are: the jobs which were started seems at jobs manager to be running, althoug...more >>

Windows 2K and SQL Server 2K When I run the following code I get an error: "An exception of type 'Microsoft (sic) VBScript runtime error: Object required: 'WScript" (sic) was not handled." I have downloaded the most recent wsh script update (5.6). '**************************************...more >>

Hi All I've written a package that gets all packages on a server, then runs through each of the packages, then enumerates the connections in each package, and attempts to translate certain properties to facilitate moving packages between servers my code seems to happliy replace server names, use...more >>

...more >>

Hi All, I have exported a DTS Package to a VB Module that contains an Analysis Server Process Cube Task. When I try to execute it in VB I get an object not defined error for "DTSOlapProcess.Task". I know I am missing a reference in VB but I cannot find the Library I am missing. Any help would ...more >>

How can I write a delete file if there is one within ActiveX scrpt? I got an error at createobject line. Function Main() dim fs set fs = createobject("scripting.filesystemobject") fs.deletefile "\\mypc\shared\newfile.xls" set fs = nothing Main = DTSTaskExecResult_Success End Funct...more >>

Hi there, I'm currently trying to import data from a product system using an ODBC driver called AcuODBC. My problem is that the performance is just killing me. It is currently taking 35min to import 95,000 rows of data (there are 700 columns). It equates to approx 1.8M cells of data per min...more >>

Hello, all. I'm trying to import a file into a SQL 2K table. The record size is 4095 characters. For some reason, when setting up my column positions (it's a fixed length file), DTS won't let me go beyond the 4095th column. My record size is 4221 characters. Any help someone can offer is ...more >>

Hi , I have a file presumably backup of a User database from a SQL Server(2k) database. I want to load/restore that in a local SQL Server to be able to see the table , views , etc present in the database. How do I do that ? Thanks, Arijit *** Sent via Developersdex http://www.deve...more >>

When loading a package from within an ActiveX script using the following code: 'Connect to DTS Package Set oPackage = CreateObject("DTS.Package2") oPackage.LoadFromSQLServer ServerName, "********", "******", 0, , , , "DW_Copy_Table" I get the following error message: Step Error ...more >>

Some time ago, I changed the machine name of my laptop running WinXP and SQL Server 2000 (SP3). Now when using the Query designer in DTS, I get an OLEDB error: "Server <old-name> is not configured for DATA ACCESS". After clearing the error, the Query designer opens. <old-name> is the on...more >>

How can I copy IDENTITY property of source column to destination column in DTS? "ENable identity insert" property does not affect the transfer TIA, Sandra...more >>