I am using DTS to transfer data from an ODBC database to SQL server on a daily basis - all my transfers are working OK except for one. The problem with this one is that one of the input fields is a text field containing a number and the SQL server field is an int field. Among other thing I hav...more >>

SQL Server 2000 Have created a batch file on my local Win2k workstation that calls another batch file from SQL Server. WORKSTATION BATCH FILE: REM Calls HR03 batch file on SQL Server pushd \\erpimgdev1\DTS_Info CD batch CALL Start_HR03_ZW1_Job.bat This successfully calls the second...more >>

I'm a casual user so forgive me if this is a stupid question. I am moving from one server to another (SBS2000->SBS2003) and have 3 SQL databases that we need to migrate plus related DTS procedures. I (think) I have found out how to move the databases but how do I move the Local Packages and Meta...more >>

How can I import certain database objects(tables, stored procedure, etc.) based on the date they have been created?...more >>

We are having problems with scheduling a DTS package job only when it uses a system DSN for an ODBC connection and this only seems to happen when we reboot the SQL Server. (Any non-ODBC type packages schedule run just fine.) We log into the SQL Server as administrator and schedule the DTS packag...more >>

I'm fairly new to SQL Server 2000 admin and DTS packages, and would appreciate any help with the following problem. We developed a DTS package to carry out various tasks, which ran fine from the developer's PC. When the package was scheduled we got the error about non-sysadmins being unable t...more >>

I have created a DTS package and scheduled to run. When I execute the DTS package manually it works great. It deletes all the rows in a SQL server Table and then imports query specific data from a Visual Foxprot Free table and appends to the SQL server Database Table. The same task does not imp...more >>

I am trying to import five test records from an Excel 2000 spreadsheet. One of the fields has a data type of 'number'. All of the fields for all of the records import just fine, except for one record in this particular field. Has anybody had a similar problem? Why would the DTS engine not p...more >>

Hi, When i run a stored procedure i am getting the following error in sql server 2000. Server: Msg 7391, Level 16, State 1, Line 2 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned messag...more >>

I am using an ExecuteProcess task to brodcast an error message to network users. I have to pass two paramaters to the task. I want to build the error message in an activex task and pass it to the paramaters section in the createprocesstask...more >>

I am new to SQL server and DTS.. Other errors have more specific explanation but not this one. The only information it gives is that this is a "step" error from a DTS step. We thought at first that it occurs due to large load of jobs on the DB server it was running at the same time. However, e...more >>

To automate DTS-searching process associated with a certain stored procedure or tabl in case of failure to get data for tables or other errors I wanted to make ASP to display all the information of DB, tables stored procedures associated with each table, and DTS steps excuting each stored procedu...more >>

A scheduled dts package which contains odbc links to other data sources fails I have use unc paths this fails. I have read the securtiy papers but this still does not run. I have other dts packages that run fine but, this package is the source of all the other data that is required....more >>

What the best object to use when need is to check if any rows exist on a staging table and manipulate result? For example if rows exist I need to perform a various of task otherwise do nothing. Thanks in advance....more >>

I'm trying to import an Excel file into SQL2000. I've done it many times. However, now I get a "DTS Import/Export Wizard Error: CoCreateInstance" with the comment "Class not registered." I removed SQL2000 and re-installed but I get the same error. The "source" possibilities all look st...more >>

Can anybody tell me how to specify the DTS connection properties if I need to connect to an LDAP database/server? Thanks for any help. Mary ...more >>

Hi All, I am trying to write a dts package that will allow me to export multiple text files from sql server based on teh results from a table. So i have a table called people that contains all the records i need to export into multiple files. I have another table called province that contai...more >>

I have written a pretty large DTS and have created a few connection objects. I had one that I no longer use, I know it is there because I see it in the dropdown when creating a SQL task or transformation, however the icon is missing from my designer. I want to delete this rogue connection as I...more >>

We're working on a VB COM component that will dynamically build a DTS = package using user inputs and configuration files. We have it working = in a single-server test environment, but just realized that in = production this component would be running on a machine that does not = have a SQL S...more >>

Alan thanks for the previous help, im still trying to avoid having to go that routr. In an ExecuteSqlTask is there a way to assign the @@rowcount to a global variable? ...more >>

How do I kill a DTS package? I have went into the SQL server agent/process info and tried to kill it there, but it did nothing. I have tried to end Enterprise Manager by using the task manager, but when I go back in, the DTS package is still running according to SQL Server Agent. Thanks for an...more >>

In my "Data Task" Under Workflow properties, under options I have the name "DTSStep_DTSDataPumpTask_1" Off of the connection that it transfers the data to I have "on success" workflow to an active x. In the active x I have the below function: Function Main() Dim sMsg Dim oTask Dim oDataP...more >>

Hi, I need the hotfix described in: Microsoft Knowledge Base Article - 830912 FIX: Key Names Read from an .Ini File for a Dynamic Properties Task May Be Truncated Could anyone mail this to me? Gr., Willem...more >>

I guess I probably don't understand the full concept behind the Meta Data Services, but why would somebody decide to store a DTS package there? What's the benefits? What's the drawbacks? David Sumlin --- Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community...more >>

There seems to be a problem running the sql ftp task object in a dts package on the sql server itself. The same process runs fine from a remote PC workstation, however, when run on the server the ftp task indicates completion but does not actually create the file. There is one caveat. ...more >>

I just learnt how to run SQL DTS from Access. I now need to check programatically, if the DTS runn with out an error. Is there are a property in access to do this? If so let me know how. thank you...more >>

Hello! I tried running Insrepim.exe to update the Information Model. It didn't work. I Upgraded SQL from 7 to 2k and I cud able to see all the Repository packages as metadata packages in 2k. I cud not modify and save any. I get an error saying that " The Information Model of Metatdata ...more >>

I have a DTS that does a Data transform, copy columns from text to table, and I would like to keeps track of the number of records that failed and store it in a global variable. I then call the global variable from another dts packags. I want to keep track of num of records that are succesful ...more >>

I am trying to move some BCP commands to DTS and the problem is that I don't know how to create a format file in DTS similar to the one in BCP. The user of the data still likes to see the field format in the text file. Any ideas? Thanks in advance, Kim...more >>

Hello NG, how come that the format function does not work in Active-X-Script??? Format(date(),"ww") What can I use instead of the format function??? Thanks for helping. Daniela ...more >>

How to migrate multiple SQL DTS packages (sa many as 100) to new SQL 2000 server in a new domain? Thanks for suggestion. -Tom...more >>

Hi I am using bulk insert to laod teh data from deliemted file.I am accessing sql server using windwos terminal service client The particular file is there in the same machine where teh sql server is installed.But I am getting an erro meassge Please find the exact error message It will be gre...more >>

I have an ExecuteSQLTask whis i dynamically set the SQL statement. The statement is an update statment, like Update Table1 Set Field1 = 'VAL1' Where Field2 = 'SOMEVAL' this works great, but i need to know how meny rows were affected so i can error trap. ?? any suggestions ...more >>

Hi, Can anyone help me on how to transfer a linked server from one sql server (remote server) to another sql server (local server) ? Regards, Anand...more >>

I have a DTS that is importing text files as a scheduled task. I would like to be able to generate a word doc and mail it to a list of clients. I know its possible under Visual Basic, but has anybody managed to get a DTS activex task to call a Word Document and print it.. ????...more >>

Can someone please help me out here? I have been trying relentlessly to solve DTS transformation issue. I'm trying to import a pip "|" delimeted text file that contains 1.5 - 2.0 million records. Around record 65,000 I get the famous error "Too many columns found in the current row; non-white...more >>

What is the best way to execute an Oracle store procedure with a DTS package?...more >>

I want to copy a table out to an Excel file at the end of every month. The Excel filename needs to be unique each month like: Dec03.xls Jan04.xls and so on.... any easy way to do this with a Pump Task ? ...more >>

Hi All, I am creating a DTS package to export a table to a text file. After creating and saving the package, I noticed that I selected the wrong table as the source. So I change the source table and go to the Destination tab. Selecting "Define Columns" and "populate from Source" clea...more >>

I'm creating a DTS package to export the contents of a table to a text file. Everything works fine until I try and change the source of the Data Transform task from Table/View to SQL query. When I click Build Query, I receive the following error: --------------------------- Package Erro...more >>

I just learnt how to run SQL DTS from Access. I now need to check programatically, if the DTS runn with out an error. Is there are a property in access to do this? If so let me know how....more >>

Hi all, Have a table, say tab1(any schema, few rows) and I want to execute this query through DTS select * into tab2 from tab1 select * from tab2 and store the result in a text file. I am using following object. 1. An ExecuteSQL task against the SQL Server connection for your SELECT ...more >>

I have scheduled a job (that calls the encrypted dtsrun string) to run on the sql server. when the job runs at a scheduled time... the job fails. the following error message is produced: DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error strin...more >>

Hi all. I am trying to use as DTS package to import data across from an old SQL 6.5 database to a new 2000 one. I have already used the upgrade wizard to set-up all the tables and fixed the few minor problems that occurred. I just want to import the data and overwrite the data that is presen...more >>

Hi, I am looking for exmaples or pointers to do single-to-multiple records transformations in DTS. This is the scenario: Source Table: ID Text - value1<separator>value2<separator>value3<separator>.... Example of 1 record: 1 A<break>B<break> The Destination should have the following ...more >>

I am new to DTS. I have an excel sheet with 4 columns, 101 rows, corresponding to a table in sql server database. column1 is pk in table. when I use DTS Import/Export wizard to import data from spread sheet, I always get an error saying that Error during Transformation 'DirectCopyXform' for...more >>

Hi, I need to read in a global variable to name my output files. Then I need to create a new file for each product in the table, and name the file by the product name and the value of the parameter. For example, if the parameter is 12/31/2003, I would need to create a file called Pr...more >>

I have a problem with a DTS package of Sql Server 2000 (Standard Edition). The package has to import some record from one table to another one and flag them with a X through a lookup query. The problem is that sometime DataPump seems to skip some rows, that are not exported but however flagge...more >>

I can run DTS package successfully if I run them in Enterprise Manager manually. When I try to run it from Query Analyzer or Stored Procedure I getting to errors. The following is execute command for DTS: Exec master..xp_CmdShell 'DTSRUN /S(CLAIMPRO) /U (claimpro) /P(claimpro1) /N(DTS_Mas...more >>

Hi, We are trying to use a Data Transform Task for inserting records into the database from an Excel file. In the Data Transformation task - Source Tab - Preview - Only some of the records are getting displayed. If the first record contains number for a column in the Excel file then al...more >>

Hello, I can't figure out how to set up Transformation scripts for the DataPumpTransformation in ActiveX. The vb code Set oTransProps = oTransformation.TransformServerProperties oTransProps("Text") = txtSourceCode Always generates object does not support this method. Is it possible...more >>

Hi all, Have a table, say tab1(any schema, few rows) and I want to execute this query through DTS select * into tab2 from tab1 select * from tab2 and store the result in a text file. I am using following object. Microsoft oledb provider for SQL server Transform data task text...more >>

Is there a way I can document what is in a DTS package? Ideally I would like to generate a script which lists the tasks, connections, transformations, etc. It seems quite laborious and prone to error to do this by hand. Thanks for any suggestions. ...more >>

Hi All, Just a quick question, I am using a DTS pack to transfer data out of a table into a text file, is there a way that I can include the field headings from the table, just at the top of the text file. Thanks in advance. Phil...more >>

Hi, Sorry if this is a simplistic question but I'm having trouble using FOR XML AUTO in a DTS package created using the DTS Export wizard in SQL Server 2000. My SQL is : Select rtrim(col1) as c1, rtrim(col2) as c2, rtrim(col3) as c3 from table1 for XML AUTO and I'm outputting to a fil...more >>

DTS package (MS SQL server 2000) reads data from a text file, then populates a table, then from that table populates five tables, depending of the values in each record. The package executes OK on developers PC from SQL Enterprise Manager, as well as from ASP page running on local host. ...more >>

Hi, Can someone tell me how to move jobs to another SQL server? Thanks....more >>

Every once in a while (say 3-4 weeks apart), one or more of my scheduled packages will "hang" and never complete. I don't get an error, and the job will just run until I check the SqlAgent myself, see it has been "running" for 87 hours, stop the job manually and restart it. Of course, nothing...more >>

I'm getting a syntax error when trying to execute the following command in the Execute SQL Task: Execute SP_SchoolAttendanceLoad ? ...more >>

I'm currently using a DTSDataPumpTask to copy data from one database to another. The SourceSQLStatement is a simple SELECT. So far so good. Now I'd like to replace the SELECT with a stored proc taking 1 input parameter and 1 OUTPUT parameter, like this: GetAccount (@startDate datetime, @en...more >>

For all the rows in this recordset Select emailAddress, customerNum from customer I would like to call this stored procedure EXEC sp_SMTPemail 'from@address.com', emailAddress, 'Subjecttext','Messagetext - Your CustomerNumber is' + customerNu Can I do this in DTS? Or can this been done in ...more >>

I am trying to write a DTS package to export all the tables in a database to CSV files and I hope to do this with the DTS paclage picking up all the table names from the information_schema view I know how to extract all table names into a recordset and store in a global variable I know how to ite...more >>

I am embarking on a project where it may require 10-15 packages to load and scrub a data set. As we add more platform sources that we load from, a new set of packages will be required, so the total number of packages will tend to grow arithmetically. We anticipate the need to be constantly evolv...more >>

Hi. I have a flat file that has many cols. the file has to be instered into many tables(each group of cols. for one table). one group has 6 cols. each of them has to be insterted into the same table with a corresponding FK value (some ID at the beginning of the line). there is a way (putting ...more >>

I want my MS Analysis Services 2000 OLAP cubes to be reprocessed once per week after new data is loaded. I created a DTS package using an Analysis Services Processing Task to process the whole database and scheduled it to run at the appropriate time. However, when the job runs it fails with the ...more >>

Hello: I haven't had a whole lot of exposure on the DTS front, and was wondering if after creating a package, would it be possible to generate its script so that could be applied later on either the same machine and/or on another machine? Thanks....more >>

In our data warehousing system, every weekend a DTS package executes that copies data out of several SQL Server 2000 'OLTP' databases into one SQL Server 2000 'warehouse' database. The package uses several Transform Data tasks to copy the data. The problem is that every time the package execut...more >>

Hi, I'm trying to use VBScript in DTS to convert a time string, with tenths of a second, to a sql 2k datetime value. The standard ISO format of "yyyy/mm/dd hh:mm:ss AM/PM" doesn't seem to support fractions of a second. Is there a way in DTS Active-X script to assign a string value like ...more >>

Hi all, i am trying to use an execute process task to connect to an ftp site and upload some info. but am getting the following error "CreatedProcessTask 'DTSTask_DTSCreatedProcessTask_3': Process returned code2, which does not match the specified SuccessReturnCode of 0." i am using an ....more >>

I have created a DTS the works fine when I execute it. I have schedule this same DTS to run 3 times a week. I can view the job fine under Management>>SQL Server Agent>>Jobs, but the job keeps on failing at its scheduled time. It even fails when I manually kick off the job. When I view the jo...more >>

New to DTS, and finding need for programmatic control of package execution. I found the DTS objects, and see the Execute on the Package object. BOL does not make clear if the package actually runs from my workstation, or from the SQL SERVER computer. I would like to understand the context of the...more >>

Hi all From Enterprise manager I right clicked 'schedule package' and added this to my job management however when I view the command in the properties menu there is a line of gobbledegook such as DTSRun /~Z0x5D4D6BCCCFC43B2332D8722DB632C5AD4CB79D05218F7A595FBA8D0841F41A33CE56D02C22825A2F9...more >>

Im a little confused as to what i can and carnt do. I am using crystal reports. If i wanted top get the SQl 2000 server to autogenerate a CR from a scheduled DTS would thios be possible. In Vb i just add the libery and i can access the CR dll. Is this possible under SQL 2000 Server and i...more >>

Hi I need to execute a sql task inside an Activex script which essentially executes a SP inside a loop. The task takes global variables as input parameters. How to execute a task inside an Activex script Thanks Aji ...more >>

Dear Sir, Would you please advise me to execute DTS package from inside store procedure? I plan to deploy a VB application for end-user. There are some criterions in my appl to transfer data to other SQL server. Thanks, Chaba ...more >>