hi all !!! I wrote a ETL package that allows many tasks to run in parallel. I set the maximum number of tasks to run in parallel to 5. however, only one single task runs at a time; all other tasks that could be running in parallel show Status=Started at the Execution Progress window but the...more >>

This is the second package I have had this trouble with. The first time I only resolved the problem by recreating the package from the beginning. This time, recreating the package does not help. Here is my problem... I change the catalog of a SQL connection object via script, retrieve some ...more >>

Hi! How do I display a status message in DTS? I'm cycling through hundreds of input files and I want to be able to show the user which file is currently being imported. I know I can call MSGBOX in VBScript, but the user will have to press "OK" for each file, which is not what I want. Thanks...more >>

Hi all, following situation: There are 2 sql servers (2000) in a LAN and there is my notebook with a local sql server connected via a vpn tunnel to this LAN. On my notebook i have registred all servers in enterprise manager. Now i have created a DTS Package to transfer a Database from one s...more >>

Hi We are in process of changing SQL Server authentications to Windows Authentications. We have only 1 issue... lots of DTS packages created by users. Is there any easy way I can change all authentication to Windows Authentication without opening DTs packages???? I mean, changing ...more >>

I have a Transform Data task where the source connection is SQL Server and the destination connection is a text file. On the 'Source' tab of the task I use a stored proc in the SqlQuery box like this: exec dbo.xxTestTableVar Here is the stored proc: CREATE PROC dbo.xxTestTableVar AS DECL...more >>

ERROR: The selected package cannot be opened. The DTS designer has been closed. I was able to open DTS packages before. But all of a sudden I am not able to open any of them. The designer closes with the above mentioned error. I am told that my SQLDTS.dll is corrupt. How can I fix it? Ple...more >>

We are having a very strange error since past few days. We have a batch file that executes 20 some DTS packages on nightly basis. But since past few days we are having the following error on (one specific) DTS package Error = -2147221499 (80040005) Error string: Need to run the object...more >>

I am using DTS for import data from Navision to MS SQL DB, but i am getting this error when trying to import a BCD (15,5) into the MS SQL. Error msg: Insert error... ('Pr',DBTYPE_NUMERIC), status 12: Invalid status for bound data. If i change the decimal(15,5) to varchar(30), i only get...more >>

How can I loop the rowset values one by one for data connection? I've only checked if there're values in the recordset and assign the connection values as follows. I would like to ask where I can assign the execution status (stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting) to loop back for ne...more >>

I've treid to assign connection variables by rowset. ie. Dim oCon Dim oR Set oRS = DTSGlobalVariables("RSTables").Value ' This is the output para. of "select source_server from table Set oConn = DTSGlobalVariables.Parent.Connections("Sybase ASE OLE DB Provider") ' This is the source connectio...more >>

Hi, while trying to create a package that exports data from a database table to a text flat file, there seems to be a problem whenever the amount of fields that need to be exported reaches a certain threshold (around 60, not sure now). To be more precise, the number of fields in the destinatio...more >>

I need to load data (either insert or update records) into a many horizontally partitioned tables. I have a DDQ task set up to load the data from a staging table. Is it possible to have the destination, of a DDQ task, to be a view instead of a table ? The designer does not seem to allow specify ...more >>

Hi there I am having a strange problem - I am trying to transfer data from Access to MSSQL 2K. It is a very simple field->field copying tranformation task, so there shouldn't be any funny stuff involved. There are no restrains/indexes or anything on the destination table - and all fields al...more >>

(for SQL server 2000)...more >>

Hello, If we write to a public queue using a message queue task this works....then reading from that public queue using a message queue task ...no problem... When the message is sent from another application (biztalk in our case) in another domain.... we get a timeout error message. What...more >>

I set up a transform task between a sql service connection and a text = file (destination). My sql server table has 48 columns and I want to = export them all to a comma delimited text file. When I am at the = 'Define Columns' dialog I see all 48 columns listed, but when I click = the Execute ...more >>

If you create a connection and then delete it.... No problem. But if you create a connection and want to rename it, you must go into the properties and select "New Connection" with a new name. The problem is that the old name stays behind in all the connection lists. How do you remove unwant...more >>

I saved my packages as dta files to allow easy transfer between SQL servers. The problem I have is that the server name I created the package on is stored inside the connection. My thought is to use dynamic properties to change this setting but I am having problems with that. Is there a way to...more >>

Code works in the DTS designer, but fails when run as a job. Set objFolder = myOlNameSpace.Folders("Public Folders") Thanks BJ ...more >>

When I run my package (just a simple pkg with the ActiveX task with the following functions and 2 connections and a transform data task for testing the acitiveX functions) I keep getting this error "invalid task result value". The script parses fine. So, I cannot figure it out why? Can you...more >>

I have a script (thanks to Darren) that is working to import an XML file into a database. Unfortunately, sometimes a node does not exist at all. I tried using IsObject .. and IsNull but I still get the same error. Any ideas would be greatly appreciated IF IsObject( objTitleNode.selectSingle...more >>

guys any idea how (if at all possible) to rename the task names in dts? i am talking about ones like "DTS_EXECUTESQL_TASK_1 thanks alex;...more >>

I have a problem where I use DTS to download a file from a server using DTS FTP process. It works fine the first time, but when I try to execute that same task again, it fails, any clues?...more >>

I want to insert a default value in a date field, if the value in the input text file is blank. I tried, <null>, null, 1 space, 00/00/00, 99/99/99, and none of them work (gets cast or datatype error). 1/1/1900 works but I wanted to insert a blank value. Please Help!!...more >>

I need to export data to a text file with a fixed record length. Positions 13-25 of the record contain price information and needs to contain leading zeros. My query is fine except that it produces leading spaces instead of zeros. How can I format the price to have leading zeros?...more >>

Hi there, Background - Win2000 server, SQL2000 with SP3 I have a table with 10 columns. Each column has either 0 or 1. Values are changed everyday by users. At night, A DTS kicks off and read the value, if 0 then do nothing, if 1 then kick off another DTS package to update some data. In A...more >>

This is a wierd problem and I am clueless on what caused it. Using SQL Server 2000 Enterprise on Windows 2000 my client is XP Pro with Developer Edition. I have a collection of backedup databases which I am combining into one massive database(only certain tables), as they come in. I was doin...more >>

How do you use the Message Queue Task to Receive Message Strings. I can do this using the Data file message but I don't want to have to use any interim files...more >>

What's the best way to restore or create a copy of production db (15 GB) on development server and also another copy on same sql server with different name. The copies need to be refreshed on a monthly basis. 1. For doing above things what's best and can some one post sctips or TSQL stateme...more >>

When opening a DTS package the error 'Class not Registered' error appears. When you ok the message the package opens but heaps of connection icons are missing from the left selection panel. I reinstalled SP3 but it still happens. I totally uninstalled SQLServer, checked and removed all regtistry ...more >>

This is a really strange problem. I'm forced to read in CSV data as fixed-length because the amount of data per row varies and they can't provide additional commas to make a set number of columns. This works fine as I'm just converting most of each row into a varchar column by replace commas wi...more >>

Hi, I have a DTS job with a send mail task that required a change to the attached file parameter. As the task isn't generic, we simply opened the send mail object and changed the relevant value. Once we resaved the package, MAPI logins failed. I checked the task, and it turns out that it i...more >>

Hi, I am new to DTS, and here is my situation. I create a DTS package that export SQL Server data to Excel. Unfortunately it appends to the old file. I would like to override to old file NOT append it. Please help, Thanks in Advance, Culam...more >>

Hi I apoligize about any cross-post, since I posted a similar message in the sql...odbc group too - I didn't see the dts group prior to posting - I've been tasked with setting up a datawarehouse using SQL that will pull down about 70 MAS 200 files (MAS 200 is written in Providex with an ODBC ...more >>

I have moved some DTS packages to a new server. I am calling a package from within an outer package. In the outer package (package 3)I am setting the server through a global variable and setting the GUID through a query select id from sysdtspackages where name = 'package 2' This appears t...more >>

When using WriteTaskRecord instead of WriteStringToLog - where in the DB does it write that? Also - once again it doesn't like my syntax: DTSPackageLog.WriteTaskRecord (DTSGlobalVariables("gLocalServer").Value) "Joe" <hortoristic@yahoo.com> wrote in message news:... > Thanks - I got it work...more >>

Hi Sorry to post in such quick succession. Is there any way i can save local DTS packages (like i can script files) to send to a colleague. So that he can then implement and save the DTS pakages on his local server? Regards -- G www.bradflack.com Please remove ANTI and SPAM from...more >>

Hi there, I am relatively new to SQL Server so do forgive me if i seem a tad nieve. I have recently upsized an Access 97 DB to SQL server 7, simply to implement an ASP application on the same data that is in the Access DB ( had to be in SQL, not direct on Acces db). The data is still input ...more >>

Can anyone tell me why a backup would fail when scheduled or run from query analyzer on the server, but will be successful when run from query analyzer on my local machine. Every other backup job run succesfully when scheduled in the same manner (i.e. user, backup options...etc). The event descr...more >>

Ok..I am trying to import a large XML file. The script below does an insert.. but it inserts all of the same nodes. I can't figure out what I am doing wrong?? The general structure of the XML file is: <products-announced> <product-announced> <title-info> <title-name>10</title-name> ...more >>

What's the easiest way to log task steps inside DTS? I need to create and examine some of the variables at certain points as the package steps through the logs. I need to do it as a Job - verses running it interactively. -- ___________________________ Joe Horton Database Developer / Softwa...more >>

Hello I try to transfer a data file into a table of my sqlserver 7 the size of my data file is greater than 1 Go. the bcp utility copies data , however, the latest data doesn't transfering into my table Could you help me thank you for your information ...more >>

H Does anybody know how to execute a DTS with Java? There are a lot examples for VB, C# and of course .NET but I couldn't find anything for Java Looking forward Regard Rene...more >>

Ok here is the scenario. I have a text file that contains all the data. I have a SQL Server db that has tables for that data. Currently I backup the old table, then truncate it, then insert the new data. I do this for about 5 tables. The text file always has the newest data. I do this...more >>

Hi I have a package with a number of Execute SQL Tasks and a Data Driven Query Task. All tasks are required to be part of a transaction to enable a rollback to happen. The Data Driven Query Task has the 'Join Transaction' selected but when the package fails on one of the Execute SQL Tasks, the rec...more >>

I've a task to make dynamic connections to transfer data from Sybase Server to SQL Server by DTS. The servers, databases and tables are dynamically used in INI file by dynamic properties task. For example, tables (T1,T2,T3) are used by each database(DB1,DB2,DB3,DB4) and each server(S1,S2). ...more >>

I running a datapump task to output from a table to a Text(source) output file(csv). I'm trying to mock an Excel spreadsheet layout. The table has 7 fields but I would like to added some extra columns to the output file. Table: Field1 Field2 Field3 Field4 Field5 Field6 Fi...more >>

I often receive data from trading partners in a tab delimited or fixed width text file. I often receive a schema document which I use to create my DTS package to automate the import of these files. My question is this: How can I generate a schema document like this when I am asked to provide...more >>

In a DTS I use a Dynamic Task Property to set the source and destination files of my Data Pump Task to the new table names as defined in my global variable. My problem is that by doing this the mapping of columns becomes "invalid / corrupt". Since each time I run this DTS I can update th...more >>

Hi, I created a DTS package with password. I would like to remove that password option. How do I make this change ? I don't want to drop and create this pkg since it's a lot of work for me !! TIA Mohan ...more >>

Hi, I used DTS to move data into text. The TSQL codes ran fine in QA. However when I put it in DTS package, it truncated some of the fields. My codes contains a lot of formating. Ex: Below query codes will truncate my other date fields CONVERT(CHAR(12), REPLICATE(' ', 4) + SUBSTR...more >>

Hello, I need to make a copy of a dts job to modify. I don't have the package existing, where i could go and create the job. I have the job though but there is no option to do save as or copy an save. how do i make a copy. Help!!...more >>

Anyone come up with good Environment controls inside DTS to migrate a DTS package from Dev-->Test-->Pre-Prod-->Prod? What I want is the package to be aware of what environment it lives on - and based on that it knows to use different DB's. If the package lives on DEV it knows to use DEV DB's...more >>

I have created a few DTS with about 20 steps each. I would like to get an email for the step that has failed, and only if it failed. Is there any way to create on step that will send an email with the information of all the DTSSteps if one of them failes. I am trying to create an activex and the...more >>

I set up a DTS to transform data from an AS/400 DB2 file to SQL server. I can run the package fine manually; however, if I try to schedule it, it hangs trying to connect to the AS/400 database. No errors, just sits there. We can get this to work on another server, so it seems to be ma...more >>

I want to create a DTS package that will use a Message Queue Task to read messages from a Queue and then insert these messages into a SQL Table. How can this be done? thanks...more >>

Hi, We are using SQL 2000 Server as we are mystified why the 'Data Source Drivers' when performing a DTS, for instance, contain German, Portugese and Spanish drivers and not French etc... This is quite odd and I want to know how these got there. Does SQL by default create a list of th...more >>

Hi all I would like to know what the easiest way is to set a SQL-parameter (prepar it?) in a DTS ExecuteSQLtask Normally when programming in Delphi I format the SQL-statement before I hoo it up to the Query as SQL-statement with the Format-function, but ca something like this be done for insta...more >>

Hi, I found a lot of resources on the internet to execute a DTS package from VB.net. I tested my code on a test DTS package to make sure it worked and it did. The vb code does work for a DTS package that does not prompt for a value. Now, the real DTS package I have to run, does not wor...more >>

Does anyone know how to import data from a workbook that has read and write passwords on it ? If I attempt to open it using DTS without specifying a password I get the message "Could not decrypt file." I know both of the passwords and can quite happily open the workbook with Excel. I ha...more >>

Hello I have a DTS that loads data from a CSV file to a SQL database. The CSV file has about 65k rows of data. While it is about 1/3 of the way (ie about 22k rows loaded), DTS stopped and said "Not enough storage is available to complete this operation". Our transaction log and data file are ...more >>

I have made å custom DTS job in visual basic. I started with the dts export wizard to save visual basic code for a simple export to excel spreadsheet. I then wanted to timestamp the filename so that the filename itself contains the date and time of the actual export, example: MyExportedExcelFile...more >>

I am trying to dump a table with > 32 columns to a text file. I am emphasizing "> 32 columns" because through some basic testing it appears that when you try and dump more than 32 columns, SQL Server takes a memory exception and MMC blows up. I was wondering if anyone is seeing this and if...more >>

My DTS excution failed because one of my ActiveX script got timed out. I run the same epackage again and it run just fine including this particular ActiveX script. How can I find out why this happen so I can prevent it? I've also checked the server events during the time of the problem and...more >>

For auditing purposes, I would like to keep a record of all the logins from all the SQL servers on one central administration server. This way if I need to know which servers a user has access to, I just need to execute one query on one server. My plan was to use the 'Transform Data Task'...more >>

I am pretty new to SQL database maintenance and am running into a problem, here's my scenario: 1) I have a "production" SQL server which holds the most current data retrieved from a website 2) Also have a "development" database which is fundametally the same as the "production", but may have ...more >>

Upon opening the DTS Designer, I get a missing file error and all the DTS task controls are gone. Apparently, this is not an uncommon problem, as I've seen several references to it in the newsgroup discussions. Have tried all the recommended fixes-- Installing the latest patches (3a) U...more >>

I am trying to run a DTS package using C#. When I try to execute DTSRun it runs hidden, I do not see the command window anywhere. Here is my code: System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo(); //set up processstartinfo info.FileName = "DTSRun"; ...more >>

I'm trying to return the column names of my query results. In ADO, I can simply do this: objResults.Fields[1].Name That code throws an error in an ActiveX Script Task. I'm really new at using DTS, so there might be an easy solution that I'm overlooking. (I hope!) :-) Thanks in A...more >>

Hi All I want to transfer the data from one db to the other db for the same table which exists on both the sides. Can one please let me know about what are the best steps to follow using the DTS packages. 1. Create Source DB connection --- Copy the contents to file --- using the file copy th...more >>

Hi All, Please help me, i am in a nice mess due to a problem in dts export/import. When i try to export tables from my local server to remote it gives me this message. --------------------------- Copy SQL Server Objects --------------------------- [Microsoft][ODBC SQL Server Driver][...more >>

I am in the planning process of upgrading the hardware on our cluster. I need info/advice/suggestions on how to go about this. I am running an active/active cluster on 2 ML370 servers. They are running Windows 2000 Advanced Server/SP2, MSSQL 2000 Enterprise/SP2. I want to know if all I hav...more >>

I created a new connection, however, I get the same result. If an empty string is sent to the lookup would that cause it to hang? What about the lookup itself... should it be returning something? At times when I use Ctrl+Alt+Del to stop the application I get a (Windows End Task) message stating ...more >>

Hi, Can anyone tell me what the best way to automatically download a .dat file from an ftp site and import into sql server? Should I use ftp.exe? Then, how do I convert to .csv automatically so that I can import to sql server? Will I have to write a VB .dll, not too hot with programming, so...more >>