We have a DTS package that loads VFP data into a SQL Server on a separate machine on the same network. This process takes ~12 minutes and involves many of the records in many VFP tables (1.3GB). Our CIO has expressed an interest in moving our SQL server to a remote location and keeping our Vis...more >>

Hi, I have created a VB program to perform DTS tasks for data transfer from ACCESS database to SQL Server. The DTS are created by saving the actual DTS packages as VB file and used those bas files in VP app to run the DTS programmatically. The process works fine most of the time. But occa...more >>

Hi, Currently when I perform a export to excel, those fields with datetime will only show date. I understand that I can open that excel file and change to the correct format. But I would like to have it set automatically after the export is done. How should I do so that it can show out the...more >>

Hi, Could someone help me. When I execute my DTS batch with a script ActiveX in VBScript, I have the following error message : "ActiveX Scripting Transform (DTSTransformation__5) doesn't succeed to initialize script execution engine". Thank's a lot in advance. Christian ...more >>

When using DTS to copy Tables/Views from one SQL to another, some of the tables/views start to copy but error out. Is there anyway to fix/rebuild the existing tables/views before copying or worst, is there anyway to let as many copy before it fails. I understand this would not get me a c...more >>

I used DTS to transform data from ORACLE 8.1.7 to SQL Server 2000. an error message appears while moving data in new table DBTYPE_DBTIMESTAMP "Data overflow. Invalid character value for cast specification". field in oracle with Date type. the retrived information through oracle ODBC ...more >>

Hi, I have a DTS package that imports data into SQL 2000 from an ODBC source on another server. There are only 2 steps, empty the sql table and import new data. This package runs with no errors when it is run from EM on the SQL server. I need to be able to trigger this package from a ASP pag...more >>

I have a fixed length text file that I wish to import into SQL tables. The text file will contain 2 different types of records. Firstly, a header record, which is immediately followed by X number of detail records. Each record is 80 characters long, separated by a CRLF, and the type of reco...more >>

I have data in text file , delimited with Pipe and with help of DTS package pump data to SQL server. The issue is, the text file some rows can have 32 columns and some can be 33 columns and so on. Number of columns range from 32 - 40. How do i tell DTS package (vbscript) to recognize the c...more >>

I need some help with the way I make dts packages that bring in data from outside data sources. A typical DTS Package that I've made Deletes the records of a table then on success connects and pulls down a new set of records and then on success does and update to another table from the ne...more >>

I have a DTS package the does an FTP --> Text File Data Source -->>> Multiple Tasks -->>> SQL Server database. The input source file has one row that has12 fields. The multiple tasks basically read 3 columns from the source record and insert a new row in the destination table until all 12 ...more >>

I have a DTS that uses global variables to got to the network, get a file, unzip the file, import the files into sql and then move the file to an archive. It works great as a DTS. But when run it as a job it sits at Running Step 1...and I have the manually Stop Job. Then I get no errors...more >>

sorry in advance for the newbie question. i need to insert into a table using a global variable in a "where" clause. so something like insert into datasourceA.table1 (field1, field2) select field1, field2 from datasourceB.table1 where field1 > aGlobalVariable i can do the part without t...more >>

What is the best way to import different flat files that are in the same directory using one package? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

without having a manually controlled external value set to test, is there a global property or something that can be tested to let the scripts control flow, even just whether or not to issue MsgBox statements? doug ...more >>

I am trying to import some data from excel to SQL server table. In excel file there is a column which is mostly empty [around 99 % rows] so when I am importing the file. the DTS package is putting null in all rows even if there is a value in that row for that column. is there any bug in it? do I ...more >>

Hi there, In DTS, Can we use temporary or global temporary created table in DATA PUMP TASK for Exporting data from Server1.DB1 to Server2.DB1 ? Here is case : In DTS Package. Create table ##Global(xyz int) on Server2. Server1 ----- Data Pump Task----->Server2 In Data Pump Task ( My...more >>

Hi, I'm trying to import an ms access database into SQL server using DTS. I have read all the postings and tried all the suggestions and I keep getting errors. I'm getting: "not a valid account name or password" "error during initialization of provider" Here's how I tried importing this: ...more >>

H! I need to export Excel worksheet data into a SQL Server table. The Excel worksheet has 5 cells in it and I need to insert 5 cells data into a text field of a table. Is that possible? All I am looking for is ActiveX code. I used this article (see the link) to set up my script (just to make ...more >>

I have written several DTS tasks in EM that run nightly. I am moving my data from one SQL Server 2000 installation to another SQL Server 2000 installation. Is it possible to copy my DTS tasks from EM on one machine to EM on another machine? -- Regards, William...more >>

Is there a switch that can be used with bcp to include the column names as part of the resulting file from a queryout command ? Thanks in advance ...more >>

I've a minimal SQL Server installation in my desktop PC and a source text file containig my orders. From my desktop is it possible in DTS to channel data from my single source text file to a remote destination table in a remote SQL Server? In my network environment is forbidden to share folder...more >>

Enterprise 8.00.818 DTS package on an Active/Passive cluster with a transform data task that loads data from some Excel spreadsheets. The package works fine when one node of the cluster is active but consistently fails on the transformation task (see error below) if we failover onto the oth...more >>

Dear All we recently put in a DTS request to move 36 million records from a non SQL Server to ours. At 33 million it failed, which was a bit annoying as it had been running about 19 hours. Is there a way of stopping it from rollbacking the records ? Thanks J...more >>

I am using Transform Data Task to transform data from table to text file. I am using lookup query to get the value of a single column using VBScript in transform data task. But when I test the particular VBScript task it gives me the following error Error Code:0 Error Source: Microso...more >>

DTSRUN is said to allow values to be passed-in to the DTS package. Books Online has no complete example of this being done so I have to rely on the syntax alone. Fine, but . . . The syntax for DTSRUN shows /A global_variable_name:typeid=value. but I don't know what values "typeid" take...more >>

Is storing the package into the metadata services the only way to get the DTS package excution time? Thanks Ray ...more >>

Hello Everybody, I'm trying to use the DTS package to run an UPDATE query but it looks like the DTS query designer isn't accepting a properly formatted query. I'm looking at an example given on the Microsoft website that says I should use a query like this: * UPDATE Account SET Company...more >>

Hello: I am working with SQL Server 2000. I need to search thousands of DTS packages across multiple servers to locate those DTS Packages that reference a specific server. The server is being replaced and the server's name is being changed to accommodate corporate guidelines. Is the...more >>

Is there a way to stick something in the package's configured log from an ActiveX Script? I've got a script that uses an external component to help parse an XML document that's loaded from a text field in the source table. If the document is invalid, I want to log the record's key value and ...more >>

Anyone know of a way to put DTS packages in source safe and use keywords? ...more >>

I have 4 different flat file struture in on folder(_001.dat,_002.dat,_003.dat,_004.dat)each going to a seperate table. Right now the logic below grabs all files. I want to be able to filter on the file extension and send that to its own particular table. Function Main() ' Declare FS...more >>

hi I am using SQL Server2000. I want to use the "microsoft.xmlhttp" ActiveX Component in DTS Package (using VB Script) By that method “Set objXML = CreateObject("MICROSOFT.XMLHTTP") “ But if run that code then server was not responding and also don’t send any error...more >>

Hi all, i've an aplication called DTSBackup (Copyright © 1999 Darren Green) that i don´t know if you've eard about. But this app backup one or more DTS into a DIR and then u can restore them from there to another sqlserver instance. Nothing new! My problem is that after i restore the DTS...more >>

We are trying to run a job scheduled on SQL Agent that has only 1 step. The step is an Operating System Command of: DTSRun /s <Server> /n NEW Email Team Managers /e which attempts to run a DTS package. The DTS Package itself has only 1 step which is an ActiveX Script Task. The task connect...more >>

Hi In my DTS package i have "Truncate Table task" followed by "Package Success" and "Package Failure" tasks. "Truncate Table task" is connected to "Package Success" using "On Success" workflow. "Truncate Table task" is connected to "Package Success" using "On Failure" When there ...more >>

I set up a very simple Execute Process Task which runs a batch file on our server's local drive. The process works fine until I close and restart Enterprise Manager. Then when I attempt to execute the package (which contains only 1 task - the "batch file" task), execution fails. It will only ...more >>

Hello I'd like to thank everyone who has helped me in that past. You guys have been great. I am outputting various fields from a table to a tab delimited text file. (Thats the vendor requirement). One of those fields is a varchar field that appears to have a 'tab' buried in the data......more >>

Hello: what is the sql command to be able to see the packages in the database? Thanks ...more >>

Hello: I have a WEB.NET 2003 program service. This service lets me check if a file exists in the hard disk of the server, if this is true a need to execute some packages that read text file and bring the information to SQL tables. Currently, I have structured storage file created, b...more >>

Hi everyone, Can someone let me know if there's a way of calling Oracle's procedure within a SQL Server DTS package please. If there is a way, an example would be greatly appreciated. The reason for this is that I need to execute the Oracle procedure to extract data from Oracle tables, then ...more >>

I am transferring records from one table to another table using a Datapump task.each record looks like the following: Field1 Field2 Field3 Field4 While transferring the field1 to 3 ,I update Field4 (it's my flag) and when the control gose to the next record field4 has a value of either 1 o...more >>

Can anyone throw any light on why I'm getting the following error... Step Error Source: Microsoft OLE DB Provider for ODBC Drivers Step Error Description:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Step Error code: 80004005 here's the setup.....: ...more >>

What does letter 'N' means in this syntax? if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFileData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Thanks...more >>

We have been encountering this message when running three DTS packages between two database servers: Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:ActiveX Scripting encountered a Run Time Error during the execution of the script. Step Error c...more >>

I am trying to modify a DTS package connection to use a .udl file. When promoting the package I have to ensure that there is a .udl file on every environment using the same name in the same location. That is not a problem, however, I would like to know if there is a way to dynamically se...more >>

Dear All, I want to copy all the stored procedures from one sql server to another sql server by using Import, then I get "Fail to copy objects from Microsfot SQL Server to Microsfot SQL Server" error. I can't find any detail of the error. The source database info is Microsoft SQL Server ...more >>

When executed manually all is well, connection is made to both sides and data flows nicely. When I schedule this package to a job it runs but step is stucked. There are no logs or any other information. Thanks, Roman...more >>

I'd like to know if there is a way to identify all the local packages that refer to a specific table or specific stored procedure. I know that the master and msdb databases contain special tables that can be queried when examining database structures and executed jobs. But I don't see anyt...more >>

Hello, I've used your tips from http://www.sqldts.com/default.aspx?218,1 (multiple paths in workflow) and the above article on performing a multi-step in my DTS package. What I can't figure out is this: 1. If I have two multiple paths in workflows in my DTS package and one of the paths ...more >>

I have created DTS Local Package for Migration of data from Text file to Database Table. This is working fine. I change the file name often times when I execute the DTS Package, for this purpose I have to change this DTS with parameter so kindly guide me how can I change my this DTS Package wi...more >>

VB6 ( SP6 ) & SQL 2000 In my vb code i have a public connection to to the SQLServer by using the following code Set UserDbconnect = New ADODB.Connection UserDbconnect.ConnectionTimeout = 60 UserDbconnect.ConnectionString = "DSN=MyDSN;UID=MYUID;PWD=MYPWD;" UserDbconnect.Open This works...more >>

I have serveral dts packages saved on the server. they contain a very large amount of tables(hundreds) i have made a ini file to determine the source database and the destination(db and server) that works fine. however dts package stored from a wizzard contain full DestinationObjectName =...more >>