This is with respect to the error code that DTS generates on error. I have a DTS packages wherein i've given logging option as log into SQL Server. As a result all logs can be obtained from sysdtssteplog. But on failure the Error code depicted is a large negative integer. Is there any master t...more >>

Hello I am querying a table and outputting data to a text file where each field is separated by a tilda (~) . It works great. I notice that sometimes when the fields have no data in them I get two tildas in a row (~~) which is what I want. However, sometimes I get a field of blanks sepa...more >>

Hi, How to close/clear unused connections in DTS. once a transformation or execute sql step is over i want to close the connection used in DTS step. Many Thanks in advance. -- Regards, Yash ...more >>

Hello I have used dts, but have not had to write any Activx scripts. I am trying to update table A from table B if the vendor_number field matches, if it does not match, I want to insert a new record into table A. Following is the script I created, it fails with a dup key error. any help w...more >>

I have an Access procedure that creates 5 .txt files for a DTS package to import into a SQL Database. I occasionally get this follow failure. The text file for Task 3 is sometimes 0 kb and I wonder if that causes the failure; however, it seems that Task 5 is the culprit. Although when I loo...more >>

Hello- I am using the following logic: Step1--on success-->step2--on success-->step3---on success--->Success Email - IF ANY step (1, 2 and 3) fails then... -----on failure--->Failure E-mail/Alert The intention is to get page/e-mail for failure and e...more >>

Hi to everybody! I'm trying to make the Bulk Load of an XML file with some tables and I want to make SQL Server 2000 to assign a PK to a column determined in the XSD file (I expecto to find an option that allows me to see the key icon on a column when I click the Design Table option.)I've been...more >>

Hi All, I have a datapump which transfers records from CSV file into a Table(same scenario:-)).I just wondered if before inserting the row in the destination ,I can copy the eigth field in the row which is being tranfered from source into a global variable.this process should happen for eve...more >>

Allan, I have 33 fields in each record and I have to validate each field anyways and log every single validation error in a seperate table(**NO DOUBT ABOUT IT**).in my senario the only thing which is added is to check if the status_code is not set before and if not set it to 1(which mean criti...more >>

I am using DTS to import data into SQL2000 via an Excel spreadsheet. My first problem that was that a leading zero was dropped from zip codes using a simple "Copy Column" transformation. Additionally Zips with hypens were imported as NULL. Setting IMEX=1 in the extended properties of the ...more >>

Is i possible to call an insert stored procedure while I am transforming records using Data pump task (in my activex).According to this example ,I should be able to do it ,but It dosen't work for me. http://www.databasejournal.com/features/mssql/article.php/1459171 Thanks ...more >>

I'm transfering a CSV file into a Table . In Table I have a field called record number which I'd like to insert the record number of the equivalent record in the CSV file.How can I simulate it? Thanks ...more >>

I am using a simple lookup liske this : SELECT COUNT(USERID) AS countID FROM dbo.user WHERE (IS_ACTIVE = 'Y') AND (USERID= ?) USERID =(numeric(3,0),not null) and I am trying to send it the value through my activex code as this IF DTSSource("Col002").Value <> "P1" AND ...more >>

Hello all Here is an article about restoring DTS Connection passwords: http://jimmers.russia.webmatrixhosting.net/dtspackage2.aspx This maybe useful when you have only DTS package and need to get all information from it. Martin Rakhmanov...more >>

Hi there, can anyone tell me which connection is closed when you check the Close Connection on Completion option of a datapump workflow? Is it source, destination or both? Regards, Waldy. ...more >>

Hi All, I'd like to have a global variable which is reset on every row in my data pump task.I tried to reset my global variable in the first first field of my column mapping in Data pump task ,but to my surprise there is no order for columns to be transfered in each record. Any suggessio...more >>

Hi there, I have written a DTS package that ends with an ActiveX Script step to rename the Access database file that the package imports from. Executing each step works fine but when I run the whole package, the final step fails, presumably because the Access database is still o...more >>

Hi, Thanks for taking the time to read this. I am trying to set a global variable equal to a rs. I loop through the values and insert a comma, so my global variable value looks like (#####,#####), However, when it's fed to the source sql, it doesn't work. Now if I only have a single ...more >>

How can I use DTS to load 103 spreadsheets of varying names? The names are close but vary by date. Inside the spreadsheet I can pretty much count on the columns staying the same but sometimes they do change. Normally I just use MS Access import for the import and do some clean up. Then imp...more >>

I have three tables like this. Table1: studentID IsntID FN LN ====== ===== === === Primary Key :studentID + IsntID Table2: InternalID studentID IsntID FN LN ====== ====== ===== === === Primary Key :InternalID Unique constraint: studentID + IsntID ...more >>

I know very little about DTS (yet). I need to export to a text file. Each SQL 2000 record needs to create multiple lines in the text file. What's the best way to do this? I'm wondering if I need to use the FileSystemObject, or if I can do this in a more direct manner. Thank you. Gary ...more >>

Hi, I'm trying to find out how to edit/View my query that I created when I created my DTS package. When I go to Enterprise Manager and click on Data Transformation Services, Local Packages and then click on my DTS Package it takes me to the DTS Gui interface I can see my package when I cli...more >>

Hello, The contract administration department in my organization is evaluating a software package that uses SQL Server 2000. They would like to import data from the mainframe to this database. Essentially, they would press a function key which would create a flat file containing vendor name...more >>

I have a single SQL Server 2000 installed on my server but there are more than one users that building their own DTS packages on that machine. Is there any way that I can make a separate repository of the DTS Packages for each user/database -- Thanks Akber....more >>

I'm processing a flat file into a database, and it has disparate records... i.e. start positions for information are not the same for every record. Now, with Oracle, I could use SQL*Loader to process various load formats... but does DTS handle various load formats? I'm using SQL Server 2000. ...more >>

I have made a DTS package with several tasks included. When I run it through the designer on my working computer where I have SQL Server Developer edition installed, the package is executed successfully. When I use DTSRun.exe to run the package on the server (where the database is installed) i...more >>

I've read and followed the article http://www.sqldts.com/default.aspx?234 but I _still_ can't get the result from the stored procedure's output parameter to populate my DTSGlobalVariable -- and it's driving me nuts! I 've tested the stored procedure in Query Analyzer and it's working correctly...more >>

Hello there I have database with some relationships One of them declare as one to many. In order to import the data correctly i need to import first the table with the primary key and after that the table with the foreign key. Because the DTS import the tables by the table name (as far...more >>

I do this to backup the database on our production server BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT select @i = position from msdb..backupset where database_n...more >>

Hi All, I have a data pump task which pumps data from a source to destination,I'd like to check every single field in each row and if any of them is null,I'd like to update the last field in the row and skip other field processing in the row and move to the next row. How Can I do that? ...more >>

I have to Update an Access DB Table from Values out of an SQL Server Table, where both have an ID column (SQL Server Identity). Some rows in the Access Tabele have to be updated from the SQL Server table as the source. I thought using a data driven task would be the right thing ... I need ...more >>

Hi, I have a question, how can I export the table into a csv file and couple column need merge into a column while export to the csv file. Source colume: partno,material, color, grid, screen CSV file: partno, types Thanks, Dannie...more >>

In configuring log shipping, i have been attempting to follow directinos and set up a job to transfer logins frm the Production server to the standby server. This job includes a step that runs a DTS package that uses the Transfer Logins Task step. This step fails with an Unspecified Error ...more >>

My apologies for cross-posting, but I'm not sure which newsgroup this belongs in. I am attempting to execute a DTS package from Visual Basic, and I'm encountering a problem. In a nutshell, how does a low-level user (from a security standpoint) execute a DTS package? ' here's the relevant part...more >>

I have this script in my datapump task ,I'd like to update a field in the current row when an error happens ,, Function Main() IF DTSSource("Col001") <> "C1" THEN DTSDestination("ERROR_STATUS")= 8888 Main = DTSTransformStat_SkipInsert END IF DTSDestination("RECORD_TYPE") = ...more >>

Hi All, I'd like to use Data pump task to transfer my source (CSV file) to the Destination(Table) ,but I'd like during the transformation if any of columns is null ,the last column of the row which is a falg to be set.How I could achieve this. Thanks ...more >>

How Can I define an event sink for a DTS package ?Is something which could be reached through Data pump task? Thanks ...more >>

Hello. Does a DTS package internally store the last time it was executed? I'd like to generate a report of those that haven't been executed in over 6 months. ...more >>

Hi All, I'm using a Data pump task which transorms a CSV file to a Table (Source:CSV file,Destination:Table) .There are some columns in the Table which don't exist in the CSV file.I'd like to update those columns in transforming rows ..IS it possible? Thanks ...more >>

Hi All, I have an application that uses several stored procedures. I want to create an installation program for this application and within the installation script add the stored procedures to the database. I have created executables from DTS packages and called them from an installatio...more >>

I have loaded a test system with win 2003 sp1 RC2. When I edit a dts package on that server I can no longer edit the package from any other location. I get a message about the designer failing to load or save the package. I think I have traced it down to MDAC issue. The 2003 sp1 appears ...more >>

I have a CSV file which has a line (kind of comment ) in the first row and the last row ,I'd like to use Datapump to trnasfer its data to a Table but I don't know how to eliminate those two lines. Any suggestion? Thanks ...more >>

Hello. I have a DTS package that has several Process tasks that run batch files. These batch files contain a few osql commands where sql scripts are run. When I initially boot my machine, with my local sql server, and I try to run my DTS package the Process tasks fail. If I then open my ...more >>

I have a select script that runs ins a Transform Data Task and saves the = results to a flat text file (500 char). I need to be able to save the = results without a header(column name). I have to be able to remove the = header but see no way of doing it. The Transform Data Task requires a = colu...more >>

I have SLQ 2000 Standard SP3a running on a Windows 2003 server. When I try to import some tables I get the message: DTS Import/Export Wizard Error: CoCreateInstance The specified procedure could not be found. It does not let me to import any database. What could be causing this and how ...more >>

I'm creating a natural key for records that are generated with a dat and timestamp. The data and timestamp are in a textfile To get a unique key the timestamp needs to be combined with a coupl of other ints Using a DTS VB script DTSDestination("k") = DTSSource("TimeCol") + DTSSource("numb...more >>

Why isn't there a function to ignore all timestamp fields while copied data from one database to another? I had to set "ignore" for the timestamp for over 400 tables !!!...more >>

Hi, I'm having some trouble debugging the following script i used from sqldts.com. ---------------------------------------------------------------------------------------- ' 246 (DefineTheGVs) Option Explicit Function Main() Dim fso Dim fold Dim pkg Dim stpContinuePkg Dim stpExitb...more >>

I am trying to "clone" a few transforms in SQL7 from a copy of the original database into the same export files. In other words point 2 different data sources into the same export file. Can I do this within the same DTS package? ...more >>

I have a table that contains the last name, first name, middle initial, and suffix all together as one data element called "FULL NAME"; I need to have this broken into separate data columns in my return query and have been attempting to use CHARINDEX as a means to accomplish this task but to n...more >>

I have a Table with the following schema: StudentID Name Address Score Passed ============================ I'd like to create a DTS package which iterates through all the records in this table and set the flag "Passed" to 1 if : 1) Score is greater than 15 and 2) Address is not null ...more >>

I have a DTS job on a Windows 2000 AS server and SQL Server 2000, which needs to unzip a zip file as the first step of the transform and load. My understanding is that Windows 2000 AS Server has a base function to unzip items that you click on (in much the same way that XP does). Thus I w...more >>

I have a DTS package that loops through a temp table of records and imports a list of files based on these records. I'm wondering if it's possible to check the state of the package from within my ActiveX task that is doing the looping, to see if the user running the package has it the cancel but...more >>

Hi to everybody. I'm a newbie who imports its .xml files though the XML Bulk Load COM Object in SQL Server 2000 and after some DTS processing, they are exported to .txt files. My company is thinking about buying a SQL Server 2005 license. However, I'd like to know whether it's an option to ...more >>