i've run into a problem with running a dts job that imports an access db into sql on same win 2003 server. the database has no relationships and has worked fine until i recently added a new field in a table in access. crazy as it may sound, the dts job moves every field over fine except the ne...more >>

Looping, Importing and Archiving @ http://www.sqldts.com/default.aspx?t=6&s=103&i=246&p=1&a= I used this package to import 958 text files of stock quotes into a single sql table that now has 9.38 million rows The queries are slow as molasses. I was told that having all these quotes in one t...more >>

How to move dts packages between environments without having any difficulties in making the packages corrupt after the load into the new server. Thanks,...more >>

I want to transfer all of my data from my current sql server to a new one. I was wondering what the best way was to accomplish this task. I want to keep my primary keys, constraints, objects, etc. in tact. I have setup dts packages in the past to copy the production server to a test server. Howe...more >>

I have an ActiveX step that validates a global variable, when the validation is false, I do a main = DTSTaskExecResult_Failure the task exists nicely, but does not report failure so the package moves on. How do I fail this properly so the package halts execution ? ...more >>

when i do this in query analyzer EXEC xp_cmdshell 'exec TestStoredProc 1 > \\server2\test\test.txt' I get an error saying no permission to the network drive. Is there a way to specify the user and password here? Thanks...more >>

I have an import text file whose definition might vary. Is it possible to create an ActiveX script task that will check the field names in the text file before importing to ensure they are the same as the destination table field names? For example, the script should test to see if the 3rd fi...more >>

Hi all, I have a series of Macros housed in an Excel workbook. All to fire on a workbook open event. The workbook macros retrieve data (into a tabular format) then a workbook event closes the workbook and saves the data. Next, a DTS data pump would bring over the table into SQL Server....more >>

Dear Professional I wanna copy the remoted database into local database through DTS. Can any body tell me the steps for copying the databasese from remote server to local server. Thanks in advance. Noor ...more >>

I have around 10 txt files....I want to bcp them into a single table dtproperties.....is there a way to do that?...I want to do something like this... bcp db.owner.table in *.txt...more >>

Which one is best practice? To use a Execute SQL Task to called a Stored Procedure? OR Write the sql code in the Execute SQL Task itself? Thanks in advance for your help. Olivia ...more >>

DTS and sql scripting (TSQL/SP) What are the advantageous of using a DTS package over scripting the tables for data migration needs. We have some architectural decisions to make and would someone to throw light on why one should not use DTS as much as scripting. People at work are DTS a...more >>

I have written some vb based on the code created by SQL Server 2000 which loops via an xml file containing column info and does imports to several different tables in my database. The problem is, I now need to generate the XML file using SQL I have worked out what most of the information DTS needs ...more >>

I created a Local Package that drops then creates a table in SQL server, makes a connection to an Oracle database, then brings the data over. When I right click and click on Execute to execute the packge, it works fine. But when I try to schedule it, I get the following error: Create Table [JER...more >>

Hi 1)How do we implement Hub architecture in ETL 1)How do we implement Hub architecture in DTS is there any documentation on this Thanks in advance Sunny...more >>

Hi guys I am using Sql Server2k. In the database i have one field called 'orderdate' as datetime. Now, i want to compare the 'orderdate' falling in a particular period of date. So, i am writing down the query for that which is as follows select orderid,customerid,orderdate as...more >>

Hi all DBA I used sql 2000 server on windows2000 sofor in a PIII system. now as I got new machine PIV xeon with win 2003, i would like transfer this data with the same names to new one How to do that. can i attach the files. Presently Iam using Enterprise eidtion. Is there any problem if I use sta...more >>

Hi, I tried a really simple data driven query which concatened 2 string fields in 1 and I had this error (0x80042040) Then I tried without the concatening operation (a direct copy) and got the same error The source is a OLE driver for DB2 on a IBM iseries AS400 and the destination is a SQL data...more >>

Hi all DBA I used sql 2000 server on windows2000 sofor in a PIII system. now as I got new machine PIV xeon i would like transfer this data wiht the same names to new one How to do that. can i attach the files. Presently Iam using Enterprise eidtion. Is there any problem if I use standard edition i...more >>

I am using MS SQL Enterprise Manager Vers. 8, on Win XP. Quote from SQL Server Books Online: "You access DTS Designer through SQL Server Enterprise Manager, through the Data Transformation Services node of the console tree." However, the Data Transformation Services node of the console tre...more >>

Allen, Sorry to Bug you once again. I added the following transformation in my code Row Transform Function ' Copy each source column to the destination column Function Main() DTSDestination("c1") = DTSSource("c1") DTSDestination("c2") = DTSSource("c2") Main = DTSTransformStat_OK...more >>

Hi, I'd like to use the great transforming capabilities from DTS and point to a XML file as destination. Is it possible? Would I be able to use XSL too? TIA, Roberto. ...more >>

Hello I have would like to find out the best scenario for this package. I have 2 identical Databases Test1 and Test2 in Test1 I have 2 tables Customers and Addresses. I would like to export data from Test1 the 2 tables into Test2 database same tables. After the data is exported I would like to...more >>

can i schedule a database restore from a DTS package?? if so, what task do i use?? tia jt...more >>

I am having trouble importing data from Excel. The problem is when I have a column in Excel that contains something like "1, 1, xcra, and time1" none of the data comes over in the import. Is there something I can set so that all of the data comes over regardless of a cells content? Thanks...more >>

I wonder why DTS OLE DB can't support auto schedule and any possible solution? ...more >>

ON DATABASE 1 create table sample1 ( c1 int not null primary key, c2 int ) go insert into sample1 values(1,1) insert into sample1 values(2,1) insert into sample1 values(3,1) go select * from sample1 ON DATA BASE 2 create table sample1 ( c1 int not null primary key, c2 ...more >>

i need to create a dts package that will copy an entire directory of asp pages from one server to another. anyone know how to go about doing this?? thanks!...more >>

Hi. Is there a way to execute a DTS package from the command line? Let me describe the scenario: I have a bunch of files that are generated by another computer that have their own format. I also have a program called "decode" that decodes these files and puts them into a text file label...more >>

Hi, I have some long running DTS packages that call some stored procs that do significant processing. Is there are way to put performance counters around the calls to the stored procedures in the DTS package. Has any one done this? Does .NET help in this scenario? We are running SQL Serve...more >>

We have a Nightly Job, A sp which has an RPC call for getting the data and Inserts into table A.it runs fine but ocassionly sometimes Job fails becos of Primary key violation. So my system has data as of Yesterdays not of latest. But I read some where its possible to Insert all the re...more >>

Hello I have 2 identical databases and I want to export all data from Database A to Database B. And remove the data from database A after the export. Thanks for any help....more >>

I am attempting to setup scheduled tasks of DTS packages (on SQL 2000) for non admin users. The packages we are trying to implement use an ODBC connection from a legacy system to load an Excel file. The user can successfully create the package and execute it in design mode. However, whe...more >>

Does anyone know a method for specifying the output file size from a DTS package? I have a requirement to output data files in 64 Mb chunks from a DTS package....more >>

Found this neat but of code in a previous posting which I've used within an Access ADP Project. I have SQL server installed and the code works fine. When given to a user without SQL Server, the second line fails. Presumably it's something to do with the DTSPackage Object Library (dtspkg.dll) bein...more >>

I did a export from one database into another database and selected the copy objects option. I selected my table to export and when the export completed I discovered that in the destination the bigint fields had some negative values in them. Its as if they were converted to int during the export. Th...more >>

Hi, I hope someone can help... Currently, I have a DTS package that summarizes data from a table in an Access database and inserts it into a table in my SQL Server 2000 db. The sql code in the source query of the Transform Data Task is the following: select orderNo, OrderDate, AccountC...more >>

I have a DTS that makes a connection to an Oracle database and truncates a table. The DTS makes a connection to the installed Oracle OLEDB ODBC driver and has 1 SQL task for testing purposes. I can successfully execute the DTS from the package, but if I schedule the DTS or execute it from command ...more >>

I have a DTS package called "Package A". It contains an Execute Package Task which calls "Package B". Package A is scheduled via SQL Agent. When the job runs, it fails at the time Package B is called - the DTS log shows that Package A started "execute package task", but Package B has not started. SQ...more >>

I have an instance of SQL Server 2000 (SP3a) running on machine A. I want to export three databases on machine A and import them into machine B. There is no internet connectivity between the two machines. Since I have about 100 tables between all three databases, I don't want to export on a pe...more >>

Hello I am trying to create a package that will dump the data from one table into another and delete the table from the primary table after the export is done. Any help would be good Currently I have a Microsoft OLE DB Provider for SQL Server and a Transform Data Task that goes to another Micro...more >>

Hi all, I have a DB2 query (through an ODBC connection) trying to pump into a table. Does anyone know what the target side data type should be for a DB2 TIMESTAMP? I've tried datetime and smalldatetime? Thanks, Mark...more >>

I recently applied the Critical updates (mostly security patches) and a Windows 2000 SP4 Express setup to my workstation. Since then, I get the following error when trying to execute a DTS package that contains ActiveX Scripting: ActiveX Scripting was not able to initiate the script exe...more >>

Hi I've created a dts package that generates several output files from SQL & then uses the following sample VB script to export them via. cuteftp. Dim MySit Dim DestFolde Dim SrcFolde Dim FileToUploa Dim FTPsit Dim UserI Dim Passwor Dim Protoca D...more >>

I have a Table ( BHYearHeaders) which i need to update on a regular basis from a csv file the BHYearHeader stores header records of transactions from our client and the csv file updates the header records with a voloume number Table Licence varchar(6) Serial varchar(6) Volume varch...more >>

Hi, When I copy my DTS packages (in the .DTS format) from my developpment PC to my production Server and then re-create the packages on the server, I have a problem with the package which synchronizes the other packages : The Ids referenced by it are no longer valid (since the packages ...more >>

Hello. How can I, in stored procedure, export data from table in sqlserver DB to Excel. I don't want to export all the data that in the table, but only a few rows that I get in my "select" statement. Thank you very much *** Sent via Developersdex http://www.developersdex.com *** Don't...more >>

Hi I am trying to change the DestinationObjectName property of a datapump task by Dim oTsk, nomTabl nomTable="maTable" 'The table name of my destinatio set oTsk=DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDataPumpTask_1" msgbox oTsk.Name 'it work oTsk.D...more >>

Each day I download perhaps 20 text files from a mainframe. I wrote an ASP script that opens the files, reads the contents, and extracts data to be imported into SQL Server. There's not a huge amount of data in those files, perhaps 500 kb daily, but the process takes some time. I'm looking for w...more >>

Hi everyone; I'm planning to use Data Transaction coordinator (DTC) to handle transactions in my DTS packages for an upcoming data warehouse project I've done a couple of simple tests with DTS and DTC and everything worked fine. Have you encountered any issues with DTC and DTS that hamper...more >>

This code is bringing also the empty rows for the records that does not meet the critiria. How can I fix that? Thanks in advance for your help. Function Main() dim MyVar MyVar = DTSSource("COL3") If MyVar = "X" Then DTSDestination("COL1") = DTSSource("COL1") DTSDestination("COL2") = D...more >>

What is the syntax to convert varchar to datetime in ActiveX? Again, my source field is varchar and destination field is datetime. I tried using CDate and it didn't work. I'm not very familiar with ActiveX. Thanks in advance for your help. Function Main() DTSDestination(CDate("COL1")) =...more >>

I would like to know, from an ActiveX script task within a running package, if the package is running batch (scheduled) or if it was launched by the user (using the Execute Package at the UI). is this possible ? my objective is that this ActiveX script sets default values for the package wh...more >>

I have a DTS error where the DTS keeps returning a catastrophic failure everytime it gets tot eh second pump step .. It doesnt matter what the step does. Anyone know what this error means ? ...more >>

Under what security context does an ActiveX Script task run as if executed inside the DTS designer...? Where does it get the context, does it impersonate the user..? I realize that it's different when run as a scheduled job, whatever credentials the SQL Agent runs under are used... Is that corr...more >>

Hi I need to delete a file from remote server in one of the tasks in DTS package. Any pointers on this would be of great help Thanks Ajit...more >>

Hi, I wonder if anyone can help: I have tried to create a connection to an old database using DTS to extract information out of it. The db uses Real32Bit drivers and can be connected to fine through ODBC. However when I try I create the instance of the db in DTS after I click ok the ap...more >>

I always encounter problem while making connection from database to file. When I enter the destination tab (file), the window prompts "send error report" after I've pressed "Define Columns". It seems that it cannot map to the file format correctly. How to solve it? Thanks a lot....more >>

Hi Can anyone help me with this please. I have created DTS package for cubes to be refreshed. I have sheduled my package to run weekly. I have a question if my processing fails how I would be notified. Do I have to create a job. How do I create that Job. If some one can please let me ...more >>

I use this script to create a CSV file from a space delimiter file. Now I want to use the same script to create a tab delimiter file from a tab delimiter file using with the same global variables. The only extra thing is that there is a condition -> Col4=X. I just need help with the syntax...more >>

I have a simple DTS package that copies a database from one computer to another. It was originally tested runing on windows 2000, on both computers, and it worked fine. But once I attempt to run it to copy data from a Windows Server 2003 to a Windows XP computer, it crashes while trying to add a...more >>

I have a SQL Server 2000 install on a Win 2K server and am runinng a simple Transfer DTS package which transfers 1 table (F4211) from a JD Edwards install on a DB2. If I run the package locally on the server by right clicking and selecting Execute Package the DTS runs fine and transfers the table ...more >>

Hi,folks SQL Server 2000(sp3) with W2000 Server We have experienced recently with strange behaviour of our DTS package. We transfer data from AS400 to SQL Server (the package was created on the server side) and sometimes we get reversal hebrew words. We have found this happened when the user d...more >>

I am creating a DTS process that will extract data from a text file and place it into sql server. The data comes from a Paradox database and outputted into a text file. The file is delimited with a pipe (|) and text has double quotes "". Issue number 1. One problem I'm noticing, th...more >>

I'm trying to figure out how to pass the same variable to the sql statement and the transaction. I tried to specify a variable in my sql syntax during the dts wizard but it didn't seem to like it. The value of the variable will be used as a column in one place and a value for a different column....more >>

Hi, I need to import an excel file every month. The excel file should be imported into a table named 'NewInforec', and the existing table should be renamed to 'NewInforec_(month -1) At this time I have this code DECLARE @month INT SET @month = DATEPART(mm, GETDATE()) DROP TABLE NewInforec_o...more >>