dts

DTS- Debugging Tips

In a legacy project that I'm on, we have several processing that are preformed via DTS. DTS is not something I worked with a lot back in its hey day.... I was in college. More specificity, these process are in ActiveX code blocks -- which is basically VBScript for database. It is really hard to debug. Anyway, I'm wondering if any pas...

SQL Server 2000 FTP

I'm trying to use SQL Server 2000 DTS Package to download a file from a FTP server. However, the FTP module in DTS only shows the files in my ftp login "Home" directory. I need to change the remote directory once I've connected. How do I do this? I can quite happily browse to the directory in the Files tab of the FTP task. The direc...

Catching events from an SSIS package loaded in C#

I have an SSIS package that executes several tasks. I manually added an event handler inside Business Intelligence Studio 2005 at the package level for the OnExecStatusChanged event. My question is, how can I add a handler for this event inside C#? I have loaded the package as pointed here and I also created a custom class inherited fro...

Importing Excel into SS2000; Error on Field; DTS

I'm trying to import an excel file in to a SQL Server 2000 database using DTS. This is nothing fancy, just a straight import. Where I work, we do this 1000 times a day. This procedure usually works without an issue but something must have changed in the file. I'm getting the below error: I've checked to ensure that the column "Assi...

DTS DB2 Connection Problem

Hi all, I want to copy an Oracle Database to a DB2 one using DTS in SQL Server 2000. When I set up both connections using their respective driver, I can read both databases, and link the copy operations between them. But when I want to run the DTS package, an error shows up saying "Driver not capable." Here's the log, thanks in advan...

where are DTS packages stored in SQL Server 2005?

I have created a few DTS packages and saved them on the server. where I can edit these DTS packages on the server? ...

DTS Packages Failing in SQL Server 2005

I am getting the following error when my DTS package is scheduled: Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER I am not sure how to get my DTS packages to execute. They run fine when I manually execute them I am running SQL Server 2005. DTS Packages were created using the Import/Export Wizard. ...

How can I transfer database objects from a local database in App_Data Folder in a VS 2008 web app project to an SQL Server 2000 database?

We have a VS 2008 web application project under development for which a local database (mdf file) was created in its App_Data folder. Now we have changed our plans and decided to use a SQL Server 2000 Database instead of the file database in App_Data. To achieve this, I want to use DTS Import/Export wizard to move the local database obj...

Execute an SSIS Package created with VS2008 on SQL Server 2005

Maybe a stupid question but... I created an SSIS package using VS2008 Professional. I want to deploy and execute it on a server running SQL Server 2005. When I try to run it I get an error stating the version of the Execute Package Utility on the server is different than the version of the package I'm trying to execute (makes sense, si...

How to Export binary data in SqlServer to file using DTS

I have an image column in a sql server 2000 table that is used to store the binary of a pdf file. I need to export the contents of each row in the column to an actual physical file using SqlServer 2000 DTS. I found the following method for vb at http://www.freevbcode.com/ShowCode.asp?ID=1654&NoBox=True Set rs = conn.execute("selec...

DTS set the SQL statement for a Step in another step

Since I cannot seem to use the ? in the more complex SQL statements we are executing we read somewhere that you can set a previous step to setup the SQL for you. This is an ActiveX step which sets the steps SQL statement and substitutes in global variables. So the SQL step has SELECT 0 in it and the step before it is essentially a VB Sc...

Creating multiple Access Databases as Targets in a SQL 2000 DTS Package

I'm new to creating DTS packages with SQL Server 2000 (although I'm not new to programming). I'm interested in creating a DTS package which dynamically creates a few Access Databases to hold the results of some queries executed against a SQL Server 2000 Database. I've seen a question on here that was answered by the author, so I know i...

Data types of columns in DTS disconnected edit

When viewing a DTS package via Disconnected Edit, Destination or Source Columns within a Transform Data Task have a DataType property with values like 129, 130, 131, 135. Where can I find a list of what these values mean? ...

excel macro to change unformatted text data to correctly-typed data

I'm populating an excel sheet from DTS, but the resulting output is formatted as text since DTS sees the cells as varchar(255). I'm planning on adding a macro that will format the values correctly as numeric or datetime. What VBA would I use for this? eg the value in a cell shows as "2009-01-01 00:00:00". If I press F2 to edit the cell,...

DTS Excel Import Problem (SQL Server 2K) Only First Row is imported

Hi, i have a Problem with the DTS Excel Import. I use a Excel data source and a Data Transformation Taks to import the data in an existing table. First Error was a Buffer is too small because of a ntext column in the table, but i have set the fetch buffer size to 5 and the error was gone. But now only the first row is imported, tha da...

SQL Server whole database csv export

A consultant employed by my client has asked for an export of the the first n rows (approx 30 or 40) of all the tables in one of our SQL Server 2000 databases in csv format, including column names on the first row. The database has about 100 tables so I don't want to do this manually. Can anyone recommend a script, tool or other appr...

Upgrading SQL Server 2000 to 2005 or 2008 - DTS to SSIS

Our office uses SQL Server 2000, and by and large it has served our needs well. I'm looking into the possibility of upgrading to SQL 2005 or 2008 because of the new features not found in 2000 (when I saw Index usage statistics which told me which Indexes were the most used and which were never used, that blew me away). I'm not too conc...

Microsoft SQL server hosting that supports DTS

Could someone suggest a shared SQL Server solution that supports DTS packages? Update: I was hoping to use the PostgreSQL ODBC driver and a DTS package to keep data synchronized between MSSQL server and Postgres. After getting my hands on SQL Server 2005 and attempting to do this I realized SSIS doesn't seem to play nicely with the Pos...

Syncing SQL server 2000 with 2005 -- will DTS still work?

Hi, we have a local staging server running sql server 2000 and a remote public version also running sql server 2000. The remote version will be upgraded to 2005 and I am wondering if the DTS packages we have in place will continue to function between the two machines? If not, what would be the simplest, most efficient way to sync betwee...

Transfer data from Sybase 8.0 database to SQL server 2008?

Hi I have a dts package in sql server 2000 that gets data from a Sybase 9.0 database. I am trying to create a new DTS package to setup as a job in SQL server 2008 using the dsn connection file but I cannot get past selecting the dsn file part? Sorry for the vagueness of this question but I basically want to get connected to a Sybase 9...