I'm considering using DTS packages for certain database tasks. I have a policy that most if not all database activity must use database code (stored procs, functions, etc). Currently, schema changes can be easily associated with code by searching Visual Source Safe files for anything that's ...more >>

Is it possible to running cursor query in Execute SQl Task? My Cursor query is simple, just disable all constraint in all tables at a database : declare @tablename varchar(30), @str varchar(80) declare c1 cursor for select name from sysobjects where type = 'U' open c1 fetch next ...more >>

Hi, I want to have your feedback on how to configure my drives to insure a good performance during loading process & transformation against a staging database. Image you have 4 drives available for the staging database & tempdb database... loosing these disk is not important, so no redu...more >>

Dear all, I am not sure if I am at the right place for my question. I have a SQL server table, I have to set the schedule (every morning at 8:00 a.m.) that the data from this table is automatically transferred to a specified Excel file. I know that there is an "Excel Connection" in DTS pac...more >>

Hi, I am still new to DTS and can't figure out how to do this. I have a two tables, Game and Game Detail joined by Game_ID, I need to set up a DTS package where I can export to different text files the Game Detail information for each game in the Game table. Each Text file will be the Game ...more >>

Hi I have a Platinum database which stores dates in integar form e.g the date is formatted as below: Column_name Type Length Precision ------------------------------ ------------------------------ from_date int ...more >>

Hello: I have the following table: CREATE TABLE LANGUAGE ( Language_ID_N smallint IDENTITY(1,1), Culture_Name_C varchar(20) NOT NULL, Culture_ID_C varchar(20) NULL, ISO_Language_CD_C varchar(3) NULL, Language_Country_Region_C varc...more >>

Hello, I've just discovered DTS and I'm very glad I did because I need to do some serious data transformation tricks! I realise that I will need to write some ActiveX scripts to convert my data, however, I am completely new to DTS and my VBScripting skills are limited. I've used PHP for ...more >>

Hello I need to define a user of SQL Server 2000 that could run jobs. At present time I succeeded only in the case that the user has rights of system administrator (in Enterprise Manager in Logins I click right on the user and in Properties/Server Roles I check box System administrator). B...more >>

I'm importing a text file into SQL table using DTS. The file(s) sometimes have formatting anomalies that are benign to me but DTS sees them and throws an error - and doesn't store anything into the DB. The file is comma-delimited and there are no errors in that area. The error appears when th...more >>

I would like to create a job from enterprise manager with 3 steps, each step execute a DTS package by order. Can I do that? Any help will be appreciated....more >>

I have DTS packages. I run them from a C#program. DTS packages run a query and exports results as txt files. I want to send export file name dynamically from my program. Is it possible?...more >>

Hi, I have a DTS package that will implemented on several network configurations and would like to use a 'path variable' to replace the string that is entered when defining the error text file. (depending on the implementation I am directed by the DBA or network admin to use specific locati...more >>

We use the DTS Transfer Database task to blast our live database to a standby server at regular intervals. The TD task is a monolithic task which transfers a complete database. Thus, as the database changes its structure, the TD task continues to work perfectly. It simply transfers the entire ...more >>

Hello there, I have a statement running in DTS. SELECT COALESCE( MAX(colID ), 0 ) AS LastID FROM TableName Output of this row is stored in Global variables; on successfully completion of this step; the next step a transformation task defined as select * From TableName where colId > ...more >>

Can anyone tell me how, I can execute a stored procedure in a DTS and depending on the output parameter execute the rest of the stored procedure or else write to a log file? ...more >>

I have to create a dts package which moves data for some tables from Server A to Server B and C. There is a flag in Server A in some lookup table which indicates whether Server B is available or not. Similarly there is a availability flag for Server C stored in Server A. Data should be moved fro...more >>

I am having problem with .DAT file. iwant to upload it to sql server. Do i need to choose Text type ? any help.. ...more >>

For SP3a and below, we have been installing the following files on a workstation to allow a user to run DTS packages without installing all the SQL tools: DTSRUN.exe dtsrun.rll axscphst.dll axscphst.rll custtask.dll custtask.rll dtsffile.dll dtsffile.rll dtspkg....more >>

What is the best way to save a DTS Package. I don't seem to be able to modify any of the different types, such as if you save it as a "SQL Server", how do you modify it? Then I saved it as a SQL file, a dts file, but now how do I open it so I can modify it? I would really like to save t...more >>

If I want to use DTS export table to Oracle from SQL Server, what all drivers I need. One is I believe MS ODBC Driver to connect to Oracle. Does any other Oracle client library required. ...more >>

I have a table with 2 columns that I am copying into 3, with the 3rd column being an identity column, and I have the Enable identity insert checked, with an "ignore" selected under the transformation columns. But I keep getting the error that it can't insert data because the ID field cannot b...more >>

I've created DTS packages to copy tables from SQL Anywhere version 2.5 (AKA Sybase) to SQL Server 2000 & am executing them from Visual BASIC .NET. I'd like to limit the data copied by the Transform Data Task by date like this: SELECT column1, column2 FROM tablename WHERE (column2 >= inputd...more >>

A problem occurred while attempting to logon as the Windows user 'SQLAgentCmdExec': The parameter is incorrect. The step failed. There are a few jobs that developers create and they run under there accounts, I do not want to give them sysadmin access, but they need permissions to CmdExec. I e...more >>

I have to write a job to bring in a text file into a sql database. We have a ftp server set up and a place to read the file from. I've been looking at this site: http://www.sqldts.com/default.aspx?292 trying to get some ideas as to how to do this piece of a project. What I need to do ...more >>

Is possible to run DTS with Transform Data Task, which have source and destination connections on the same server? This task copy 1:1 table from source database to destination database (without ActiveX script). When this databases are on a diffrent server than DTS run correctly. When enviro...more >>

I am executing a DTSRun statement from a batch file running on Windows XP and am checking for ErrorLevel 1 in order to perform error handling tasks. The DTSRun statement runs without error, yet returns ErrorLevel 1. I am using SQLServer2000 SP3: SQL Server 2000 Service Pack 3: select @...more >>

Hi, I have a DTS job which consists of around 34 Cubes. Scheduled to run daily at 1 am. Some times the job is getting failed. When I look at the error log, I can see this message "CONNECTION TO THE SERVER IS LOST". But if i open the DTS and open the particular cube, it is not showing the c...more >>

Hello, i would like to change the DTS-AtiveX template: If i create a new Data-Tranform with ActiveX, so i get ever this template: '********************************************************************** ' Visual Basic Transformation Script '****************************************************...more >>

Hi "select x from tbl_Y where myDate=GetDate" I am running a dts and need to filter out todays records. I have the dates in string format but can convert them to as date dd/mm/yy but can't filter on GetDate() as this returns the date and the time. I can't find a simple way to do this...more >>

I have many DTS packages. I modified them to accommodate new connections with different login information. They work fine when executed from DTS packages. All these packages used to be executed from SQL scheduler and they been fine until my changes. After my above described changes to the packag...more >>

Hi, I have a DTS package where I load 1.5 millions of rows in a staging table, and I perform multiple updates. 1 of these updates update the entire table by doing a simple lookup in a small table. this update takes 30 minutes ! my file is on a Raid 0 I have tried to update by batch (s...more >>

Hi -- I've created a DTS package on my computer and saved to a remote server. I can execute the package locally and it runs without problems. I can also open it within DTS Designer (where I created it) without problems. I've noticed that the package cannot be scheduled and would fail w...more >>

just installed sql service pack 4 and am having big problems with meta data services after installation if i expand data transformation services in enterprise manager and select Meta Data from the list two dialog boxes appear one labled internet explorer which says some software (activex...more >>

I need to create a DTS package, which will execute stored proc and export a result set into Excel without creating intermidiate table in SQL and export it to Excel...more >>

-- I made the following local procedure: create procedure proc_AccountTransfer @client varchar(50) = null ,@account varchar(50) = null ,@amount money = null as set nocount on set ansi_warnings on set xact_abort on begin distributed transaction exec proc_AccountFrom 'Bishoy','Saving',10...more >>

We have a table on a legacy DB that stores change records. This table gets populated in real time, everytime a change is made to any one of the hundred of tables. We use these change records to update our records in "real time" in SQLServer tables. I want to know if I can use DTS to 1) sele...more >>

I have an URL to a file that is comma separated on the web. I would like to use DTS to put the contents into a table. When I create an HTML file source and give the URL as the location I get the following error: Error Source : Microsoft JET Database Engine Error Description : Cannot update...more >>

Hi, there, I have one DTS runs every day to generate one text file from one table in a database of SQL Server 2000. The output file path I use is \\web2\export\report.txt. I schedule the DTS to run at 10:00pm every day, and each time it runs it will generate a text file "report.txt" on the ...more >>

I am moving a DTS package from one server to the other. In the DTS package, I am exporting data from some tables to text files. On the new server, I have to modify the text file destination and the database name. When I modify these two connection properties, some of them OK. but some of th...more >>

I have a DTS package I am kickig off from a C#.Net applcation that imports a tab delimited text file created by the C# app. I can successfully kick off the DTS package from my development machine, run the package, and everything works great. When I install the app on another machine, it ...more >>

Is it possible to use query in DTS to export to Text file. I tried it, gives error msg. DATA PUMP TASK REQUIRED TRANSFORMATION TO BE SPECIFIED. I want to trim varchar field before export to TEXT file. That is why i used query with ltrim and rtrim on that particular field. thanks ...more >>

I have a SQL server which I use to store Itemised phone bills. The bills come on a flat file on a CD which I then use a DTS package to bulk insert into a table.. I now want to give this task to a user to do, but I have no 'front end', and I don;t want to teach someone how to use DTS.. Ca...more >>

Hello, Inicialy i had created a job to transfer all objects between 2 servers (that failed) , then i created 2 new jobs one that transfer all stored procedures (that's work) and another to transfer all tables (that's fails) I get the following error : Executed as user: ARES\SYSTEM. .....more >>

Hi folks, I get a "out of memory" error when trying to use lookups in my data transformation task the two lookup querries are SELECT MAX(AddressID) AS Expr1 FROM tblAddresses and INSERT INTO tblData (AddressID, Name, Data) VALUES (?, ?, ?) ...more >>

I have an Excel 2003 spreadsheet with a column of alpha/numeric data. The first row is OPEN and the next 100 or so rows are fy's ranging from 1999 - 2005. When I preview the data (using SQL Server DTS import) it does not show OPEN but displays the fy's. emp termination_year salary 11 ...more >>

Hello, I have a dead instance with lots of DTS scripts that were not backed up. Is there a way to extract them from the msdbdata.mdf file? This is SQL Server 2000 SP3a. I do not have a backup of the msdb either. :( Thanks -- Jake...more >>

How can I encrypt the command prompt options to be executed by the DTS Run utility when is called by a job?...more >>

I am new to scripting and ActiveX Scripts in DTS I am getting a syntax error on line 11 and it looks like the example from MSDN 'Using ActiveX Scripts in DTS'. Can anyone tell me why? Here is my script: Function Main() Pass = DTSSource("PassID") Select Case Pass Case "6" DTSDestinat...more >>