*Please see the middle of the query for the question. I need to join in the --middle. I was wondering if I could do this with a where clause. Thanks!! /************************************************** **************** * * TRANSFORMATION 1A * Description: This Transformation will be ran f...more >>

SQL 2K Please provide any thoughts on handling DTS error while called from a SQL job. My SQL Job starts a DTS using dtsrun utility and when the DTS fails, I don't have much details. If I setup in DTS , Property -> Logging -> Error handling , will I get all the detailed error information...more >>

Hello again, So, in my vb.net app I run a DTS package from a button. No problem the first time. But if I click the same button to run the same package, I get an error message that a connection already exists. So I added an If statement with a static integer var. If var < 1 then g...more >>

Hi, I have mmoved a DTS package from one server to another and am having errors executing it va a web app. It runs fine when executed manually. It is executed through java by issuing an xp_cmdshell which in turn executes a dtsrun for the package. The java user has datareader/datawriter to...more >>

Hello, I tried running DTS to import data from a text file to a Sql Server 2000 table which contains an Identity column (autonumber column). I got an error that the Identity column cannot be null. I used to be able to import this file before I added the Identity column. Is there a way ...more >>

After installing XP Pro SP2 my DTS connections give me an error "Class not defined". All my connections that normally appear when trying to create a new DTS Package are not available. The DTS packages execute but I cannot edit nor create any new packages....more >>

Does anybody know what what SQL logic ist DTS using when getting data from FLAT FILE WITH WHITESPACE as a colum identifier ? What sp / view or whatever is used ? Michael ...more >>

Hi I DTS, in an FTP task can you access a secure ftp server....if so how do you do it.... eg I have a site like xx.xx.xx.xx on port xxxx with a specific username and password. How do you specify the port. I tried ftp://xx.xx.xx.xx:xxxx but it didn't work. Even ftps://xx.xx.xx.xx:xxxx didn...more >>

Hi, i try to import foxpro (.dbf) table with DTS of Ms SQL, but i have this error messagge "ONLY FOR SAME TABLE NOT TO ALL": Error calling OpenRowset on the provider I use Dirver for Microsfot Foxpro Why? Rgds Luca...more >>

Two questions: 1) What API does DTS use to access the database, OLE DB? 2) Using the DTS APIs....can I export a SQL table to FoxPro? to DBase?...more >>

Hello, We have a series of related DTS packages and We want to use one .ini file to drive all the Dynamic Property tasks for all of them. Once We get to approx. 6KB in the .ini file, We seem to hit a size limitation. The Section drop-down list in the Add/Edit Assignment dialog box trunca...more >>

Hi All, How can I execute the dtsrun command in the vb.net program? Please advise. ...more >>

Can anyone give me some direction in how to make a package to do what this script is doing? I have been checking the dts related web sites and groups and haven't found what I need to understand how to do this. BEGIN TRANSACTION DECLARE @ULogin varchar(50), @UPassword varchar(50), @UFirs...more >>

I have sp that I have written that gets info from a table. but it the text file I need header and results set. For ex.in the sp looks like this create proc info as declare @count int set @count = (select count(*) from info) select getdate() select * from info select 'total count '+co...more >>

Hi All When a number of my tasks fail I would like to send an email. I have set up a send mail task (that works fine) and I have setup a workflow of on fail got to my send mail task. My problem is that the package bombs out at the first sign of an error. The first place I looked was the pac...more >>

I have a package built in VB.NET being run from an ASP.NET webpage. It is pulling large amounts of data from SQL Server into an Access database for download by the user. I have two Web servers running this package and only one of them is getting the following error, while the other Web server ...more >>

hi, I am wondering if I can create a recordset in ActiveX Script and then Insert/Update data. e.g. Dim rst set rst = OpenRecordset("TableName") Do until rst.eof xx xxx rst.update loop the above statement work in vb/vba does it work in DTS, too? Thanks Ed...more >>

Hi, I have a ActiveX task that shows the error message as "invalid Task Result Value" But it used to be working fine. anyone have the same expenerice? Thanks Ed...more >>

Hi, I have cruised the archives but I can't find anyone with a problem enough like mine to help me out. I have a DTS package that works perfectly fine from within Enterprise manager. I can use DTSRun from the command line too, and it works perfectly. It has an ExecuteSQL task to clear a tab...more >>

Hi folks, I've had a look around and couldn't see a similar problem so thought you'd like a challenge (although this may be quiet easy) Scenario: Novell NDS directory showing the computers on the network and the users logged into them. Connect to this 'database' source with ODBC provid...more >>

I have a user who created a DTS package with an owner password. Now they can't remember the password. Is there a way to retrieve the password or better yet is there a way to change the password then remove it? Thanks for any help. Nancy ...more >>

Hi, I have MSDE SP3 installed and the only thing that I have changed recently is my Windows login Password. In the Enterprise Manager the SQL server is registered using mixed authentication. When I try to open any DTS in Design mode, I get an error dialog with the title 'DTS Design e...more >>

I want to change the connection information of a large number of dts packages when migrating from development to producton..... How can I do so? Thanks in advance... Ajay...more >>

is there some performance considerations when using execute sql task - is it better to use a large stored procedure compared to 10-15 execute sql task. michael ...more >>

I have some data that I would like to use DTS to import to a MS SQL Server table. Two of the fields contain packed-decimal data. Does an easy way exist to transform the fields into unpacked (signed) decimal during the import? Can anyone please point me to some documentation that would give some i...more >>

I need any script suggestion to use in my stored_procedure_load. Looking to load only the new data into staging tables since last_load_date. I wish to enter the last_load_date information into a table on each succesful load...and use the last_load_date info. in my query. eg. SELECT a,b,...more >>

I need to do ETL/ data movement. The databases are located in two servers. (e.g: Server_1 : 2 databases, Server_2 : 1 databases) I am using stored procedures for ETL loads into staging tables. How do I establish connection to different SERVERS using from my stored procedure. can any body...more >>

I'd like to get a string stored in a SQL table directly into an ActiveX Script. (More specifically, I am retrieving the name of a file which was the last one processed. The log of files processed is already in a SQL table. The table would be used to determine if the file has already been process...more >>

During the installation of our product, three things occur: 1- We install a version of embedded SQL Server; 2- We create our databases; 3- We generate a DTS package from a VbScript. This later step is the problem: on VbScript-generated DTS packages, the simple action of subsequently adding a...more >>

Is there a good reference to the DTS APIs besides the online books and the VB generated code in the wizard? I think I am going to be asked to write a few generic tools here for our group using the DTS APIs in a C++ application. Thanks....more >>

Hi All, I have a DTS package that queries a table and generates an Excel file. The excel file is saved on a remote file server. When I execute the package it runs fine and saves the file in the correct location. However when I schedule the package it fails. The error log states that Jet...more >>

Hi group, I need to skip certain tasks in a DTS package based on some condition (the presence of certain rows in a database table). What would be the easiest way to achieve this? I'm not very familiar with VBScript, so I'd prefer a solution without that... Thanks in advance! Jens...more >>

Can anyone tell me the difference between "execute" on a package versus "dtsrun" command line? I have a package that runs fine by right clicking and "execute", when I run from the command line with "dtsrun" it errors out with the following. This is definitely not a permsissions problem, as I ...more >>

I know I have touched on this before, is it possible to import records using DTS from the sequel server that will bring the table into excel and when we run this each night it will replace the old spreadsheet with the new one. I cannot seem to make it replace a spreadsheet and this is wha...more >>

Hello: I am working on a VB.NET program that allows me to copy whatever DTS package or packages from one server to another. I am using the following lines of code to do the actual copying: For Each strPackageName In arrPackageList objDTSPackage = New DTS.Package2 'Copy DTS package fro...more >>

I need to merge a table in an Access database with an existing table in our SQL Server 2000 database. The structure of the tables is the same. I found that the SQL Server table didn't have an index defined, so I created a primary index on the SQL Server table which matches the primary in...more >>

Hi, just wondering whether anyone has had this experience as well and if so how they've fixed it. It appears that a particular DTS package, scheduled as a job refuses to run an 'Execute Package' task. When the package is run in the context of the DTS designer it's fine - as soon as it r...more >>

Message that Allan Mitchell sent: my message below. Question will this allow the new records only each time we run it at night, that is what I want, new records added to existing. You will need to be able to identify these new rows. I presume you can do that with the key values. What abou...more >>

Hey all, I'm currently using a shopping cart software called .netCart, which is in ASP.NET and VB. I have been trying to import my local "fully working" database to my remote server, but then the exported remote database is not working with the software. Unfortunately, I have no prior exper...more >>

Hi! If I try to import a table in sql server 2000 sp3 enterprise manager, choosing my access 2000 database as source and my sql server as destination, when I hit Next, EM just goes down. In the Eventviewer Application log i can see this: Faulting application mmc.exe, version 5.2.3790.0, fa...more >>

Hi, I'm currently trying to get a DTS to export the results of a SQL query to a ..csv file. It used to be really easy to do in SQL7 - set up a connection, set up the destination file, set up the transform data task, set the source to a SQl query and when you clicked on destination, you...more >>

Hi! I have a problem regarding a DTS package. The package is residing on a machine that will be formated in near future. On that machine I have a simple DTS package that was created when choosing an "import data" from an access 2000 database too a sql server 2000 sp3. It simply deletes from...more >>

based on the exaple of theCookbook for using SQL Server DTS with .NET i tried the example with ASP.NET. But this doens't seem to work? If thta's right, why is that? With a console app or winform app the same DTS app works fine. Guy ...more >>

Hi I use bulk insert to load data from a csv file to sql server db. The character data in the file is surrounded with quotes. In the bulk task I have given the duoble quotes as the Text Qualifier. But when the data is loaded it loads with the double quotes. Any suggestion as to why its h...more >>

I have a problem with a duplicate key. The input to the datapump is a large txt file, the output is a SQL table with primary keys defined. I'd like to simply drop any rows which are duplicated on the input (i.e. I'll take the first input, and discard any duplicates). I'm aware that the SQL table ...more >>

If I were to create a codebase / library of "functions", such as error logging "protocols" / "frameworks" or data handling routines in a single package, but as seperate tasks or steps. How can I execute just one task in an extrernal DTS package? I tried PackageObject.Tasks("Task1").Execute, ...more >>

I'm using xp_cmdshell/dtsrun to execute a package that (sequentially) modifies data in one SQL Server database, transfers rows to a second SQL Server database, and modifies data again in the first database. When it successfully completes, all data modifications are made to the first database, bu...more >>

What would cause a DTS package when executed to change a SQL Server Alias from NamedPipes to TCP/IP? Thanks, Mark P....more >>

When I run sp_who2 I am seeing about a dozen spid's using the sa login running in the background and the command is Task Manager. Does anyone know why this is and would it hurt to kill those spid's? The lastbatch date for each spid is over 1 month old....more >>

I currently run a DTS packages to import data from an SQL server into an excel worksheet. It is set up to run the data every night, but the problem I'm having is that it is importing the entire table each time it runs and I only want new records. Please advise....more >>

I don't do much DTS but his should be real simple. I'm using the data import wizard to import an Excel spreadsheet into a table. I need to concatinate the first and last name fields in the source into the NAME field in the destination. So, chose to use an ActiveX script transformation and her...more >>

Hi, In my DTS, I am using ActiveX Script to call SP. The reason I am using ActiveX to call SP is I have to porcess many XML and load into SQL Server. Here is my probelm My Connection to SQL is works fine using ADODB, When I try to call SP using Paramters that I am having problem. With ou...more >>

I need to bring customer and order information into SQL Server from a Cougar Mountain accounting application. At present I am not even sure what version of Cougar Mountain is being used. Has anyone worked with Cougar Mountain? Does anyone know what type of database this application uses? ...more >>

Hello there I am trying to run xp_sendmail with an attachment. I have two attachments file1.txt and file2.txt, but I'd like implement in the xp_sendmail a condition where if file1.txt does not exist still attaches file2.txt and send the email Here is the sql i wrote so far, and is not q...more >>

I am looking to import 7million rows worth of data from a flat text file. all the rows seem to be copied over but they are not arranged like they are in the flat file. each row in the text file is a record and there are no column delimiters. hence the table should have only on column. also note...more >>

I'm not sure if this belongs in this thread but it's related to DTS. During the execution process of the DTS Package, I sometimes encounter this error:- Event Source : SomeStepsNameHere Error Code : -2147024882 Source : Microsoft Data Transformation Services (DTS) Package Description ...more >>

I used the Copy SQL-Server Objects Task to copy tables and views from a production server to a reporting server. On the production database I created a database user i.e. ReadUser. On the Reporting database I created the database user WriteUser. In the Task I defined the ReadUser for the Sourc...more >>

New to DTS and not a DBA, but would like to know if source contains more fields than a target is it possible to add those fields to the target in DTS .... actually I will be using the DTS APIs. Thanks....more >>

Hello, I want to copy data of one table from source database to=20 destination database (product upgrade, new table=20 definition extended). When I use DTS, I get "Unspecified error" and some other=20 random errors. The most srange is Exception Log, ie.: Error at Destination for Row num...more >>

Hi, I need to migrate a database from one server to another using DTS. The internal document that I'm following states "issues have been encountered in the past with tables being lost due to lack of space in TempDB. Check this prior to continuing" Does anybody have any pointers (as a % of...more >>

Our company bought a new AS/400. I need change the data source from OLd to New AS/400. But the DTS don't like it, if it change the host table or re-config the existing ODBC. The main problem is 2 as/400 have 2 different database name. Does anyone have a good idea to overcome this problem...more >>

Hi I am new to DTS, I use bulk insert to load a csv file which has all its character (as opposed to numeric) data in double quotes. But the bulk insert takes the data into the table along with the quotation marks. I know you can do this easily in Transform Data Task, but for lot of records I ...more >>

Hello. I am using SQL Server 2000 and have a BIZARRE DTS problem. I have a package that has two tasks. The first task deletes all the data in a certain table. The second task is a datapump between an Oracle database and the table just deleted. I am using the native Oracle Provider for OLE DB,...more >>

When I did the upsize wizard, for the relationships, I thought it was set to DRI. However, it was set to Triggers. I have since moved my access XP tables to SQL 2k tables and the data. Should it be an issue that the relationships are in Triggers, not DRI? Should I try to fix that? ...more >>

i need to develop a DTS package that uses Oracle8i datanase tables as source and SQL Server 2k tables as target. The mapping is one-one, meaning the source columns are mapped to the target columns (no transformation at this moment). I need to do this for approx. 180 tables. Is there a way to ...more >>

Hi there, I'm using MS SQL Server 2000 SP3, WIN XP SP2, When I try to open a DTS package from EM in SQL Server I'm getting an error message: Invalid Class String (I'm not sure if this package was created under MS SQL 7 or 2000) My questions are: 1. How to solved that kind of error ? (with no...more >>

Just wondering....I have been specifying some simple transformations using ActiveX scripts in the import/export wizard in the transformations dialog BUT if I accept the default...."Copy the source columns directly to the destination columns" is the grayed out script still executed at all? Tha...more >>

Hello We're developing some new code, trying to run a dts package from an asp web page. The package runs fine locally. However, when trying to execute the package via asp, using this line of code Set oPkg = Server.CreateObject("DTS.Package") We get the error Server object...more >>

Here's what I'm trying to do. I want one set of DTS packages to be able to run concurrently with different data sources/targets without having to modify the DTS packages. I have setup the packages to point to UDLs/INIs which allow me the ability to easily change the source/target, but the pat...more >>

Hi I'm not sure if DTS is the correct way to do this or stored procedure. Here is the code to produce and populate my test table CREATE TABLE [tblEntitlement] ( [strLogonName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [intYear] [int] NOT NULL , [decPreviousBorro...more >>

I need to export .xsl file to sql server 2000 with out using DTS utility. Only through dts package or asp coding Any help would be greatly appreciated. Dilip Singh Dodiya ...more >>