I have a shared MS Sql 2008 database with my hosting provider and MS SQL Standard 2008 on my local box. They have the SQL Server locked down (rightly enough) but it menas I don't have sufficient privileges to use the backup database wizard or to generate scripts to create the database as a whole. To get around this I have been running ...
Is there any way of error handling in DTS packages. Here is the scenario, I have a dts package with some SQL tasks and Active-x script tasks. The SQL task will run the scripts to validate the data in a table and if the data is in correct, the script will raise error in the script making the task to fail. On failure of the SQL task the...
I'm creating a DTS package. After specifying the source, destination, and tables, I get the Save, schedule, and replicate package step, which has a Save DTS Package checkbox. If I check the box to save, I get these options: SQL Server SQL Server Meta Data Services Structured Storage File Visual Basic File Can anyone give a good exp...
I have a DTSX file in a project I'm taking over. I have Visual Studio 2005 Pro, but it just opens it as an XML file. SQL Server Management Studio 2005 does the same. I've seen people opening these files in some workflow-esque format; Business Intelligence Development Studio comes to mind. Is this part of Visual Studio or SQL? Does it...
I need to convert data that already exists in a MySQL database, to a SQL Server database. The caveat here is that the old database was poorly designed, but the new one is in a proper 3N form. Does any one have any tips on how to go about doing this? I have SSMS 2005. Can I use this to connect to the MySQL DB and create a DTS? O...
I have a VS 2008 project that is using DTS. The project is in source control, and I don't want to check out and modify the project references for fear of stubbing the toes of other developers for whom things are just fine. For me, right now Visual Studio can't locate the DTS reference used by the project. I have SQL Server 2008 instal...
SQL Server 2000; My source is a text file with the destination also having a uniqueidentifier column. I know how to do this with a database source but don't know how to do it with a text file as the source. ...
In the past, I have used Informatica for some ETL (Extraction Transformation Loading) but found it rather slow and usually replaced it with some PL/SQL scripts (was using Oracle at the time). (questions revised based on feedback in answers) I gather that DTS was Microsoft's ETL tool prior to SSIS. Would it be difficult to convert an ...
The task is to have SQL Server read an Excel spreadsheet, and import only the new entries into a table. The entity is called Provider. Consider an Excel spreadsheet like this: Its target table is like this: The task is to: using 2008 Express toolset import into an existing table in SQL Sever 2000 existing data in the table! Iden...
What technical issues am I likely to encounter when migrating a database from SQL Server 2000 to SQL Server 2005? Are there any common problems I should be aware of? Should i be worried about any T-SQL changes that may break when stored procs are executed against the newer version of the database? What about DTS packages? Are there any...
Say I have a text file that looks like this: date 1/1/2010 a,b,c a,b,d ... I want to import it into a table so it looks like this: 1/1/2010,a,b,c 1/1/2010,a,b,d ... What is an elegant way to do that? My best idea so far is to use a data flow package, and use a flat file source to read in the file (ignoring the first line) and lo...
So I made a package in SSIS to read data in from a text file and load it into a database table. What's the best way to set this up for non technical end users to run this when desired? My boss was thinking to have a SP launch it, and then have a report made in reporting services launch the stored procedure. Surely there's a better w...
Hi all, I am using SQL Server 2008 and Visual Studio 2008 I have two databases.I need to run a query in a database and grab those results and update a table inside another database. This process would be done every one hour automatically. How can I do it? Data Transmission Services(DTS) used from SQL 2000? Is there something better t...
I get an "Index was outside the bounds of the array." error when I do the following. 1) I launch the Import and Export Data Wizard (32 bit). 2) Data source: .Net Framework Data Provider for Odbc. 3) I provide a connection string and DSN for a 32 bit Progress OpenEdge 10.2A ODBC driver that I've set up. 4) I set up a flat file destina...
How can I make identical output from a transformation go to two separate places e.g., an OLE DB destination and a DataReader destination? Background: I have an existing package that reads data from a text file, does some transformations, and loads the data into a SQL Server table. Now I'm trying to make the package be callable from ...
I have a DTS package that runs on our MS SQL Server 2000 database server, and need a way for users to execute it from their own machines. (The package creates files that are then transferred by my application to the client machine.) I have included the "Microsoft DTSPackage Object Library" COM reference in the application, but still ge...
Hi, I am currently developing completely on a local machine and have a DTS package that on completion should run a local exe file that compresses the csv files that were just imported. If I run the package in the designer or from the list of packages then all is fine and the files are compressed. If I run the file from SQL Agent as a ...
I am trying to run several DTS packages from a sql 2000 box. The DTS calls will originate from .net 2.0 - 3.5 code. I have been unable to find a good way to programmatically accomplish this. What I have read is running a sql job from a stored procedure that calls the DTS package. Does anyone has any experience with this or know of a good...
I'm currently in the process of cleaning up a sql server 2000 instance. I found 10 Local Packages that were created 4-5 years ago. In an effort to clean up old processes, I want to delete these packages. My question is, how can I determine if they are still in use. Does the start and end time on the log file suffice? Thanks! ...
Hello! I'm looking for a tool or component that generates flat files given a SQL Server's query result (from a stored procedure or a SELECT * over a table or view). This will be a batch process which runs every day, and every day a new file is created. I could use SQL Server Integration Services (DTS), but I have a mandatory requiremen...