This query works in Query Analyzer: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempLabelData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tempLabelData] GO CREATE TABLE [dbo].[tempLabelData] ( [ItemNo] [varchar] (12) COLLATE SQL_Latin1_Gener...more >>

I didn't see this, but assumed it would be a very common question. We have many SQL servers and in some cases the DTS jobs copy selected data from ServerA to ServerB then lots of connection tasks pointing to ServerB. In most cases changing the name of ServerB to (local) would work, but not in ...more >>

I inherited a package which creates a flat-file on a remote pc ( ex. \\remotepc\dir1\file.txt). When the output file appears in the remote directory, it grows very slowly. The sql which is the DTS data source is a normal sql statement with a Group By. I take this to mean that the entire ...more >>

Hi All, Every time I importing a text file the columns name change. I can collect all the source columns in a string, when I call the pkg I pass this string as GV. In my Transformation I want to change change the name of the source columns. How can I do this ? Tks JFB ...more >>

I keep getting the error The number of failing rows exceeds the maximum specified Insert error, column 16('DateX', DB_TYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. I have the following code in my VB script to capture invalid dates. It is wor...more >>

Hello, I was successfully able to import all the tables from a SQLBase db to MSSQL. Now i would also like to import all the indexes for the tables. How can I do that? -- Regards, Sami [Remove Numbers from e-mail address to use it] ...more >>

From within a DTS pacakge ActiveX script I'm building a command with a .cmd extension and then trying to execute the command via shell.run. I've installed this successfully on 10 servers so far without a problem. On server 11 the process is hanging after the command file is created. When I exec...more >>

I have created several DTS package through EM on my own workstation, saved to SQL server. Then when I was on server itself, I try to open these DTS pacakge through EM, I got an error saying "SQL server doesn't exist or access denied".. Any idea how to resolve this? Thanks Wenli...more >>

i wrote a custom db maintenance procedure to reindex various tables in my database. depending on the parameter passed to the procedure, a group of tables get reindexed. There is a scheduled job that runs daily that calls this maintenance procedure. im having an issue where this job is failing ...more >>

I am trying to import a text file and am having problems. The file is not setup where each record is on a line. It contains numerous records on a line and then after each record is a lot of white space and then before a new record it contains a rectangle looking shape. I tried to copy and p...more >>

Hi, I am using SQL Server 2000 on Windows 2000 server. Here is my problem: @String = 'word1word2word3word4word5' - This value is passed to Stored Procedure from the script. Column1 in a table1 has many rows with "words" . One row in this column1 will have 'word3'. Notice that this 'word3' i...more >>

Hi! I need a well written book (or an article) recommandion on data warehousing and OLAP SQL Server 2000 that is pratical and very "how-to" written. It should guide you on how to make a productional system using DTS to extract data from several sources, creating dimentional and facts tables a...more >>

C#.net? Under some special circumstances, I need to run a DTS package to Get data, then process the data, then run the dts package again, .. this may go on for multiple iteratins. I have to use C#.NET to do the processing. Is there any way I can launch a DTS package Dynamically fron C#.NET? M...more >>

Hi All Have a table with this structure Code Name 100000010 Project A - area 1 100000011 Projekt A - area x 200000010 Project B - area 1 200000011 Projekct - area x ... From this I would like to create a new table with this structure 1000000 ...more >>

Hi, I want to select data from one sql server and insert them into another, then delete the data from first server. But I want to do it in a transaction so that if the delete fails, the insert can be rolled back. How can I have such transaction in a DTS packages? Any help would be greatly appre...more >>

Hi! I need a help!! How to download files in DTS package? Tanks Joao ...more >>

I have a text file (fixed length) that needs to be loaded into an SQL database. It has over 300 fields in the record and to use the Wizard would be WAY too cumbersome! Is there some way to explicitly define columns through code or otherwise that says where each field begins and ends? ...more >>

I have an execute package that has numerous transform tasks. When the package is run independently, it works fine. When I execute the package from within another package, I receive an error stating "Execution was canceled by user." I've seen the knowledge base article about this error when ...more >>

Hi, Can I have an example about an "working" DTS package including the steps involved, error checking and some notification procedures used? Thanks...more >>

Is the /A parameter from a DTSRUN command automatically assigned to the global variable defined in the DTS package? If not, how would this be done? I'm trying to pass path/filename information to change the datasource text file, but the global variable that I use to assign to the datasource ap...more >>

I have some tables that keep failing when I try to copy them to another SQL server using DTS. I've identified a pattern that the first failing row in the problem tables always has a comma in one of the nvarchar fields. I'd like to try to write a dts package that would filter out all the commas. I...more >>

Hi All, Sorry if this question is more VBScriptish one. I' trying to find an specifc file on my computer through my DTS package .I'd like to give my DTS Package a wild card(*.txt) and a word or phrase in the file (exactly like what widows dose) and find the files on my computer.I know that F...more >>

Hi all. I want to report the row counts for adds, changes, and deletions that occur in several stored procedures. I have a stored proc that's invoked by an ExecSQLTask, and global variables set up to pass the row counts from the ExecSQLTask to a reporting ActiveX script. But the rowcounts aren't...more >>

Hi, when imported fixed length text file to database the result is not completed, not all rows in the text file were imported by DTS. What happend with this.... -- Message posted via http://www.sqlmonster.com...more >>

Hello, I have a stored procedure (which uses the sp's sp_OACreate, sp_OAMethod, etc.) that should execute a DTS-package on the server. This DTS-packages exports data from SQL Server database to an existing Access 97 database. When I run the DTS on the server, everything works just fine and ...more >>

I am going bonkers trying to copy tables from one SQL Server to another. For example, for one table, when I try to copy it from Svr A to Svr B I get the following error: Error at destination row number 7. Errors encountered so far in this task: 1. {DBNETLIB]{ConnectionWrite(send()).]General N...more >>

I was using dts to try to copy a single table (using the copy objects process) from one SQL DB to another and got this error: [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_grantdbaccess', database 'master', owner 'dbo' I am the owner of both the sendi...more >>

Hi to everybody! I'm doing a XML Bulk Load of multiple XML files and given that there are a lot of them, it must fully be automatic. However some of these files have not been validated and contain errors that stop executing the DTS. As a result, I must extract the problematic XML files and re...more >>

Hi all: I used DTS to import data from xls file and i choose Microsoft Excel 97 - 2000 as connection, import xls data to server 2003 database. The problem is that all data from xls would consider as numeric when i doing transformtions to sql server. so if any letter in xls tha...more >>

I have a simple package created with the Import Wizard that reads rows from a text file to an sql table. The table has a primary key and the text is not supposed to have duplicate records, but of course it does. How can I tell DTS to skip duplicates instead of aborting rolling back the transac...more >>

Is there anything wrong with this? Or a better way to do this? Ryan Function Main() Dim objFSO Dim objFile Dim objFolder Dim strFileName Dim strParentFolderName Dim objPKG Set objPKG = DTSGlobalVariables.Parent strFileName = "" strParentFolderName = DTSGlobalVariables(...more >>

For each row of a table I'm transforming I want to write the row to table A and insert a row in table B and put the value of an identity column on table B into a column on table A. I can get a lookup to do the insert into table B but try as I may I can't get it to return @@IDENTITY. Lookup ...more >>

One of our source files has duplicate records which generate a primary key constraint violation during the insert. We tried performing a lookup as part of the transformation, specifying the same connection as the destination. If the primary key exists in the table prior to the import, the im...more >>

Hi all, I have a DTS package that queries couple of tables and exports to a flat file. It runs good if I provide all the parameters. But if I use ? in the where clause it gives me the following error: "No value given for one or more parameters" I did define a couple of global parameters and...more >>

I want to copy a couple of databases from an SQLServer7 installation running on an NT4 server to SQLServer2K Developer Edition running on a WinXP Pro workstation. I'm trying to do this with the "Copy Database Wizard" but I can't figure out how to tell it where the source databases are. Both c...more >>

Hi All I am facing following problem while running a job from another job... I am writing following command to execute the another job. Exec sp_start_job @job_name = 'CSD_Phase2_Weekly_Job' In response of above I am receiving following Error Messege. Server: Msg 14262, Level 16, St...more >>

Within a Dynamic Properties Task, I'm trying to set a Global Variable to the system date using the query: Select GetDate(). This should work, but it's not. I can set a variable using a query that selects a date from a table, but not using GetDate. Help needed, Rob...more >>

I am building a DTS Packet that pulls data from a view and uses that data as a conditional on Server instance A, Then writes the data to a Table in Server Instance B. Anyone, have an idea how to do this? Timothy Juarez ...more >>

Hi there: I developed a DTS package some time ago and the user reported that the package wasn't working anymore. The DTS uses 2 stored procedures separately to do inserts on a particular table, and when I tried to open that table to read it ('open table' in Enterprise Manager (this gives me a...more >>

I am setting up a DTS packet that looks at a view on one SQL Server instance and uses a query to insert data, in a table, on another SQL server instance. Anyone, know of a way to do this? Thanks ...more >>

I have designed a fairly complex multi-step DTS package. I need to allow user who don't have a full SQL Server application to run the DTS package. While I know how to include the dependent DTS dlls as part of my installation process, I am wondering if it is actually possible to use the native...more >>

Hi I am really hoping someone will be able to help me with this one. I want to manually enter a command in a SQL Job within SQL Server which will run a DTS Package with parameters. The reason is that my users are able to create scheduled jobs from a web front end which will then run the DTS...more >>

I'm new to DTS and I would like to know how to send an emai notification, that is, execute an activex script if any of the task in the package fails I foun "Fail Package on first error but how do I execute VB script on this If this has already been answered can someone point me to tha thre...more >>

Hi, I'd like to know if I could use a SQL task to actually load values into Global variables, or am I just missing something? I understand how to use them as passed in arguments to a task, but is it possible to update them back? Can you the results of a SQL task into a global variable or i...more >>

Hello, I have a DTS package that has a connection defined for a certain database so that I can run an execute sql task against the database. I then have another sql task that runs a stored procedure to attempt to restore to the same database. Due to the previous sql task my restore fails as...more >>

Hello Experts, I am not good at SQL Server. Would you do me a favor? If I use DTS Wizard to import data (both source and destination databases are located in the same database server) and the destination does not contain any tables, does the DTS Wizard copy the index from the source databa...more >>

I need to be able to select which data (specific date) the dts package will extract from one db to another, pref. using some sort of user input. (VB.net, Access etc) Allan ...more >>

I'm calling my stored procedure through a lookup in my activex code ,but it gives me this error: The statement has been terminated When I call the sp from outside (for isntance from Query Anakyzer) it works,but in DTS it dosen't ...more >>

I want to do the following. Take a list of files that are located in a table (that is updated from another stored procedure,) and run it thru a loop that does an import. (They are excel files.) I run the DTS but it is having a problem with the connection already open....more >>

I know you can use sp_enum_dtspackages to list the DTS packages on a given server, but is there a way to list the individual step names, etc.? TIA Cheers, dj...more >>

Hi, Here is my problem: I have an Excel file containing a list of funds. I can open it easily enough and read the funds. Now I need to execute a Transformation task for each fund by looping through the funds in the Excel file, and execute a SQL Query using the currect fund against a t...more >>

I tried to copy all objects from one SQL Server DB to another. The target DB was new with nothing but the default sys tables. When I ran the DTS task it looks like it copied all the tables and then failed with the error shown below. None of the views or SPs got copied? Any ideas on what causes th...more >>

I had one server with ip address like 198.176.0.12 and other with 198.176.1.23 I want to tranfer 6 table data from first server to second server through package. and both server had same sechema. and i want before my package execute all the deta in the table of second server should delete fro...more >>

I am looking for a way to link an old access database on a network drive to a SQL server. Can someone post some examples of how they might do this? I have read where I could import the tables from access and then setup the linked tables in access but this seems backwards although I am sure that i...more >>

I am about to work on a new project and i need some insight and maybe some directional advice. Working on SQL server 2000. Its a chain process im designing, a user submits data via web(asp) into db. I want some kind of automation that would, say check each half day if the the form/request has...more >>

I have a script that I'm running in SQL Query Analyzer and it returns the correct information. When I set up the DTS package, using the same script (copy & paste), the DTS package is dropping a couple of records. Based on other packages I've set up, I don't find any errors and the others wor...more >>

Is there a way to query the contents of your DTS packages? For example, if you are looking for the string 'MAILINGS' in your ACCOUNT database how would you go about doing that? Please post example if you know. -Toco-...more >>

Hi, I cannot open "DTS transformation" tasks in DTS Designer. When I try to edit one, enterprise manager simply seems to enter into an infinie loop. (But I can edit "Execute SQL tasks" work) I've "resolved" this problem when I rebooted the server but I do not want to restart the machi...more >>

Since we have applied sp3a to the sql servers we have been experiencing problems with dts packages importing/exporting text files. (Used to work fine on SP1, not sure about sp2) The issue is with the number of columns being imported/export. Here is the scenario: Exporting data from a table w...more >>

I have two databases, one local and one on a server that I access via VPN. I need to ocasionally synch the two databases. I am able to enter data locally when I'm "on the road" and when I return to the office I want to be able to make sure that the data I entered while I was away gets insert...more >>

Hi, I am using the wonderful DTS Bulk Export Task (www.sqldts.com/default.aspx?237) How do I export the column header (names of columns) into the first row in the output CSV file? The views that I export from SQL have different structures, so I can't use a constant format file. Any h...more >>

SUB: what is the Equivalent of DTSTransformStat_SkipRow(2000) in 2005 I want to know how to skip a row in sql 2005. Please let me know if any body have the answer...more >>

Hi, I am having a problem with Data Transform task What I want to do is to transfer the records from an Oracle Database to a delimited text file. The Table has 24 columns and around 5 million records. The problem is that when I click the 'Define Columns' button in the 'Destination' tab of th...more >>

i am unable to download the files from sun server using dts package. i have given user name and password for the root directory in it. what can be the cause? is the sun server are more secure then the windows server. if yes then how can i download and upload text file in the sun server. ***...more >>