I am writing an ActiveX Script using VB Script Language in my DTS. One place I have to use a FOR EACH .. NEXT statement. Now there is a condition i am checking inside this statement and if it pass i want to go to the next element without executing the rest of the statements in the loop. Now this ...more >>

I'd like to know if it is possible to do very limited normalization tasks using the DTS designer. I've inherited a table: tblGarbage ------- field1 field2 field3 I want to map this into my relational database : tblPersons ------------ Per_PersonID Per_GivenName ..... tblPerso...more >>

Is it possible to disable the DTS Bulk Insert task through ActiveX scripting if so can anybody direct me to some good documentation? Many thanks Simon ...more >>

When I am doing dts from SQL Server to Oracle, on one of the tables I am getting this error Data for source column 14 ('TRN_NOTE') is too large for the specified buffer size. Where is this buffer and why am I getting this. How to avoid this problem. TRN_NOTE is and NTEXT column. -Nags...more >>

Hi. I am creating a DTS package to import data from an Oracle database. My package copies all the Oracle data to a SQL table. I am trying to modify the package in order to copy only the newly added rows from the Oracle database. The Oracle table does not have any kind of timestamp column and I...more >>

I am new to DTS and could use a little advice on creating a DTS package. I am import a few .txt files and they need to be imported in a particular order (File A first, then File B). What I have now is an text file (source) connected to a Microsoft OLEDB provider for SQL connected with a data t...more >>

Quick question: Is it possible to change the graphical connections associated with a Transform Data Task? I have a have a DTS package with 30 Transform Data Tasks all pointing at the same database connection, all with precedence constaints designed to result in a serial execution of each task....more >>

Hi! I'm transforming data from a SQL Server to another, what is a CHAR(10) in one table is a DATETIME in the other. The format of the string is YYYY-MM-DD, and this is handled excellent by DTS as long as the string has a value. However, when the string is empty (10 spaces), I want to put a NUL...more >>

I have a semi colon delimited file containing a date field in this format blah;Wed May 05 19:29:53 2004;blah;bla My DTS import always fails when I use the following setting Sourc ddd MMMM dd hh:mm:ss yyy Destinatio yyyy-MM-d the error is: Can not parse data input at 19:29:53 200 I've trie...more >>

hi all !!! I have two diifferent tasks that reads from table XYZ, does a lookup at the same table XYZ and finally updates also table XYZ. in order to avoid deadlock situations I use "with(nolock)" where ever possible (source query and lookup). both tasks run independently (at different time...more >>

Hello my DTS package deadlocks when I execute multiple instances. The following error is reported in the SQL error log and I do not see how this constitutes a deadlock. Any help in interpreting this information is appreciated 2004-05-04 20:19:57.91 spid4 Deadlock encountered .... Printing de...more >>

I have some sql code that I'm working on that I need to create a text file as it's output. I'm creating a text file that's more or less just a flat file, that has 01, 02 and 05 level records in it. The 01 level is a header, with a dollar amount in a field on it. Haven't figured out how to do...more >>

Hello I am trying to execute a dts package from a scheduled job, when i schedule the job from the package it appears in the jobs list but fails. I have tried quite a few commands like dtsrun and execute in the job step but don't have enough knowledge in this area... also, I previously ...more >>

hi I wrote an activex script in the data pump. I have a flag that will be set depending on the column value in the text file. Based on the flag, I was to transform the data from source to destination. So, the code looks like this Function Main( Flag = GetTheColumnValu If Flag = 1 The ...more >>

Hi I tried to import data from flat file using DTS it works fine. Field delimiter is comma and Row Terminator is {LF}. But I don't know How to mention Row Terminator For Line Feed in BCP or Bulk Copy.Please Help me. Thanks Kishore...more >>

I need to have a VBScripts or JScripts that retrieves DTS information and stores the information on global variables. For example, I want to retrieve information like step name, number of rows that have been transferred, error code & error message if an error was encountered, etc... Can som...more >>

I'm not able to export to CSV. I get an error that the file is in a read only database. I've tried doing this from two different systems, same result. I've also created new DSN's to connect to the Text CSV driver, with the same error msg. TIA JeffP.... ...more >>

Are there any facilities for debugging or stepping through a package execution?...more >>

Hi Everyone, How can i format my source string data and trim ONLY the "carriage return"? The trim embedded white space Trimming option in Trim String Transformation Properties box removes any white space embedded in the source string data but i need to leave the spaces. Thanks in adva...more >>

hey. i have thi DTSRun /~Z0x51410B01F807C5AA5278A1ACAC539C8FF4128B7F3619D461BF150F546CFB15D1EFE4DEF0DE5A143F5F7E1714D6E7A17BB16C66E67104D798CACBB647928438C97DD3746F199EF00CA91561CA25485BCC62CE78E122B964B5C4FFB6C36B17D83559FB01A357019A4BC0A31635C7C58F4349ADC9B259597D9386AEC790DC82A2D8EFEA0 how i...more >>

Hi I have successfuly exported a table to an existing Access database table but need to overwrite the existing table in Access rather than it appending the data each time as it is doing now. I have looked and looked for an overwrite option but cannot find... Am using SQL2000. thanks in...more >>

Greetings Everyone, I am a newbie to DTS and have a little tricky requirement (at least for me) and I need solution for the same. Here's my situation.... => I have 2 diff. databases (Oracle & SQL Server DB) residing on 2 diff. servers at 2 diff. physical location. => Need to schedule a DT...more >>

I'm getting a type mismatch error when trying to save a rowset into a global variable. I tried testing the method using the steps outlined in Books Online, as follows, below. I can create & save the DTS package up through step 7, but I get the "type mismatch", when I try to assign the (variant)...more >>

Hi I have a DTS package that exports the contents of a table to a text file over the network. The table has about 100,000 records. when run it exports the first, say, 17,000 records and the rest gives errors.. before it gives an error message at a specific row number. The data in the source table i...more >>

Hi, I am transferring some files from remote locations to local using FTP Task. But, I observed that the properties of the file dates were changing. On the remote location, the file created date was 4/1/2004 (for ex) and after the transfer the file created date on the local drive is the ...more >>

Hi all, I have a DTS which has a text file (source) which transform data to a table 'IMPORT' in my database. It is a very simple operation: the text file has only one field and it goes in one column 'import_record' of the table. Now this table has another column 'import_type' for which i want ...more >>

I have a SQL Server 2000 database as the back end for a web-based application that is hosted at an off-site colocation facility. I need the ability to create ad-hoc reports for it. The people who will be creating the reports are very familiar and comfortable using MS Access to create rep...more >>

We've got a warehouse of data that is generated with DTS. The database is SQL Server 2000 with the latest service packs on top of Windows 2000. What needs to be done to backup the DTS packages?...more >>

Existing functionality. Is there way to limit the runtime of a single job, a group of jobs, or set a limit on the max execution time for all jobs. Home grown. Can someone perhap provide some code snippets on how to determine a jobs runtime, and/or kill it if it exceeds some predetermine...more >>

This is my problem: I have a database installed on SQL Server. I wish to be able to export this database to a Access .mdb database. My problem is that autonumber fields are not easily replicated. Example: A conference can have a number of participants tblConference {id, confname} 1---...more >>

Hello, all. Running SQL 2K. I have several DTS packages that I've always been able to open successfully without a fuss. Since a few days ago, I've been getting a DTS Designer Error when trying to open my packages. The error says "Class Not Registered". All packages do plain- vanilla im...more >>

Good morning, I'm writing a transactional dts using Execute SQL Tasks and Data Driven Query Tasks to move data between 2 servers. Working from my own workstation with a Login that I know has no Server Roles defined, but does have Access Rights up to db_owner, I continually get ...Does not s...more >>

Is there a way to set up a normal copy from a foreign database to Sql Server 2000 without hitting the transaction log. We are not concerned with this as we are only using Sql Server 2000 for reporting purposes, but the transfer of about 80 files takes about 2 hours and we would like to cut ...more >>

Hi, i have a ExecuteSQL-Task thats Select some Data from a Source-Connection and store it in a Global Variable as a Recordset. Now I want to simply put the whole Data in a table with a different connection. The only way I know it works is with an ActiveX-Task and a ADO-Recordset. Now the...more >>

I am trying to move data from my ACCESS database to my SQL SERVER that is hosted somewhere on a server using Visual Basic. Should I use DAO ADO ADODB for the fastest upload? And wil lthe standard recordset update method work?...more >>

Hi All, I see the following DTS definitions, and they confused me: 1. Step that is executed based on the success of previous STEPS <STEP Id="s999"> <REQUIREMENT TYPE="SUCCESS" STEP="s4A"/> <REQUIREMENT TYPE="SUCCESS" STEP="s4B"/> <REQUIREMENT TYPE="SUCCESS" STEP="s4C"/> <REQU...more >>

I have a text file around 45,000 records I'm trying to Import. Its a CSV file and appears to die after 15,000 or so records Imported due to non-whitespace characters in a row. Is there any script or transformation I can do to cleanup the file. Sql7SP4 Thanks. ...more >>

is there a way within dts to run a select query and output the result set into a text file??? tia jt ...more >>

Is there a way to BCP a sectional bulk of data (like using process date) fr tbl1 (in db1) and copy/append into tbl2 (in db2) within the same server? I am trying to increase speed of my copy process here so that I don't have to copy one record at a time. Any suggestions will be very much a...more >>

Hi Can anyone tell me how to do: Text File connection to DTS in DTS object model in vb.net? Thanks RK...more >>

I'm using VB6 and SS2000. I'm searching for a table name and I want to search in the actual script in a DTSActiveScriptTask object. I can see the name of the script and the description but is there a way to actually see the script itself? There must be since it is stored somewhere. What object i...more >>

SQL 2K We have a job to run a DTS which loads and computes data from a database in the same server.. This jobs worked till yesterday, failed today with error message Executed as user: MyDomain\Myuser. ...: DTSStep_DTSExecuteSQLTask_11 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_11 DTSRun...more >>

Hi 1. Can anyone tell me how to connect a Text File(source) as datasource through programming (not through wizard) in vb.net 2. Is the Text Qualifier property present in connectionproperties of a DTS connection? If so, is it read only or can we modify the property with any text qualifier like si...more >>

Hello, I've got a problem. I have a ASP page in a Server, and I want to make a call to a DTS that is stored in other Server. All the examples I have seen are making the call being the ASP page and the DTS in the same server. Is possible to do what I want? How? Thank you for your help. ...more >>

Hi all, How do we prevent a job not to run more than once? I have a job that only need to run one time a day. If I accidentally run again then I have an issue. Duplicate data. Thanks All...more >>

Hey guys Ok I believe this is the correct news group I need to be in(I really hope so) I have been working on this problem now for 4 days. I am trying to simply add a new record to a text spreadsheet. Its C:\TEST.XLS. It has 2 columns (A and B) The name in A1 is AAA and B1 is BBB. Now fr...more >>

When I run a job, the job fails with the following message. Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed. This job used to run ok few days ago. What is wrong ? Please help.. Thanks, Ra...more >>

SQL Server 2000 EE I am automating the process of looping, importing, and archiving csv files that arrive daily (thanks to www.sqldts.com/?246). The flat files (csv format with "double quote" as text qualifier and no header) are placed in a shared folder. The import process works fine a...more >>

I have four files which I need to pick up and process. I have written the active x script to find the files and the execute sql to truncate the table and the connection and the transformation, but I am having trouble figuring out how to make them work together. I want to loop through the files and...more >>

I'm using VB6 and SS2000. I want to look through all of the DTS Data Pump Tasks in a package. I'm trying to find a package that contains a certain table. I pulled some code from MS site but it is creating a datapump object and I want to find one. I having trouble instantiating the object. I don't...more >>

I need to PUT multiple files to an internal FTP server on a daily basis. The FTP task seems to allow me to GET files "from the internet" but I don't see how to PUT files "to the internet". I need "the internet" type of connection with IP, Userid/Pwd since this is a secure FTP server. Any he...more >>

Can I automate the Windows XP right-click > send to compressed (zipped file) in a DTS task? My DTS creates a text file, but I need to zip it. Thanks, Al ...more >>

I have a mainframe extract that we aren't allowed to change that sends = us a flat file in the format at bottom. Any suggestions how to parse = the flat file so we can create a SQL table as: At very bottom is the = current SP we have that is working - but seems way to complex to = accomplish t...more >>

Hello. SQL Server 2000 I would like to change a global variable in runtime. I have a package which have Execution Task as SELECT orderID FROM orders WHERE orderID = ? I also defined a GLOBAL VARIABLE called my_var =10249 as input as well as output as row value . Now I would...more >>

My initial position: I've got two databases (DB2 8.1 and SQL Server 2000 SP3a) on two different win2k systems. The OLTP system is based on DB2 8.1 and the DW on SQL Server 2000. My job is now, to realize a high performance periodic data migration from the OLTP to the DW which could be starte...more >>

I need to set up our system to do a massive copy (about 300-400) files. I need to both be able to run all the copies at night, as well as the option of choosing a file to copy during the day. This is going to copy from a foreign database to Sql Server 2000. This was going to be done as b...more >>

I would like to make a dts automatice job like this: 1. use a union query to union table A and table B 2. select query by using table C joining the result of 1 by Primary key and fK. 3. make a new table D using the result of 2. How can I implement DTS, in 1, shall I make a temp table or ...more >>