I am new to using scripting in DTS.I want to call a stored procedure using VB script, I happednd to get some code for doing this.. I am reproducing it below.... however had two questons before i implemented it.... '********************************************************************** ' Vi...more >>

Has anyone run into an issue where a long file name for a source file (particularly DBF 4 or 5) when running the DTS package through development, but when you go to schedule the package so the package is running completely on the server, the import fails because the server can not locate the long fi...more >>

Hi, I'm new to DTS so forgive me if this is a bit trivial I want to transform data from one database to another on a regular basis. I have set up a Transform Data Task no problems that will initially copy the data as required. However, when running the package on subsequent occasions, I only...more >>

I have written an activex script to disable a data transform task when the file size is zero and put it in the work flow properties ti not run a task of uplaod if the file size is zero. The script is as given below.. Function Main() Dim fso Dim f Dim FileName ' file to check ...more >>

I've created a Dynamic Properties Task that uses global variables to substitute the server name and database name into the connections and tasks of my DTS package. The global variables worked great for setting the server name property of the connections (which were values), but now I have a pr...more >>

We have designed a DTS package in SQL 2000 that moves data from the source DB2 to the destination Oracle. As each row successfully inserts into the destination table, a flag is changed on the source table to indicate it was processed. Currently we have an ActiveX script task with code to...more >>

I need help on this. This is a problem I just inherited from someone else. There is one table which has a text field/column. This column in the table A have multiple records stored as one field. example: rec1: This is a test rec2: This is a test2 rec3: This is a test3 In this table, al...more >>

I have a DTS Package that pulls information into SQL Server from an ISeries AS/400 System. in one of the fields, There is some records with data and some without. I want to do a DTSLookup but i dont know i can fill in the blanks with information from another table without overwriting the f...more >>

Hi, I'm trying to import/export data between SQL Server, ..dbf file and text file by using SQL-DMO in C#. Is there anyway I can call "DTS Import/Export Wizard" from C# program? I know how to use existing DTS packages, but I need to add user interface. I think one of DTS method or function ...more >>

I know DTS has been redesigned for Yukon. Will our existing SQL Server 2000 DTS Packages work in Yukon or will they need to be rewritten?...more >>

I keep getting the following error message when trying to run a package that contains only two steps... An ActiveX Task that is declaring properties for an Bulk Export Task Error Source: Microsoft Data Transformation Services (DTS) Package Error Description: General Error -2147220501 (800403EB). ...more >>

Dear All, I would like to configure DTS to create dynamic filenames for a text file i.e. File_20031107.txt, does anyone know how to do it ? Thanks Peter...more >>

Does anyone know if it is possible to automate analysis service cubes to copy and paste from within a dts package? If so could you point me in the direction of any reference material. Regards Craig ...more >>

Would anyone know how to check for the existence of a table before starting a transform task against it? I.e. if the table exists, go ahead and copy it, but if not, exit the package without an error message. thanks, -marty nicholson ...more >>

Is there any way to download the text file from website: (http://www.federalreserve.gov/releases/H10/hist/dat00_sd.t xt) into SQL Server using DTS package? Thank you ...more >>

HI, my DTS package is using NT-authentication when logging in to Sql Server. The intention is to have it scheduled, and kicked off by the Agent Service. The question is: what kind of credentials will by DTS process have ? My own feeling is the DTS execution will inherit the user-ID from...more >>

I have a DTS package that copies data from tables in one database to the same tables in another database, deleting records in the destination tables first. How can I easily change the source and destination database references throughout the DTS package so I can reuse the DTS package elsewhere?...more >>

I am attempting to pull tables from a JD Edwards install on a as400. I am using Client access odbc driver, and each time I do the copy of the table I get screwed up fields where the date fields are supposed to be. In fact the date fields are converted to decimal. thus 9/9/2003 becomes 10123...more >>

a table got screwed up and I need to restore it from a nightly backup. All I can find to do is restore the entire database, how do I restore only one file at a time?...more >>

Could anyone tell me how to setup FTP on DTS to transfer files from the FTP server to the server drive, check for only the new files on the FTP and then transfer them to the server. Thanks for your feedback ...more >>

I am looking for information on how to use DTS to export a text file and to have it append to this text file when the DTS package is run in the future. Any information is greatly appreciated. Thank you. ...more >>

Hi All, Is there a way to find the Drive / Disk space from Activex Script. Cheers, Sanka...more >>

Is there a way through ActiveX script to access the SQL in a transform data task?...more >>

I just received a table migrated from the ServiceCenter. There is a long char column, which is in fact a series of column in ServiceCenter data. How can I parse it into additional fields in SQL Server; or is there any instruction/guideline, from which a utility tool, e.g. Crystal Report...more >>

Hi! I want to import a tab separated textfile to a SQL 2000 table. The row terminator in the textfile is newline. Funlayer.txt: Value1 003 Value2 4,7625 value3 7,0000 ************************************* Commandline: bcp TEST..FunLayer in FunLayer.txt -Slocalhost -T -r\n - fbcp....more >>

Hello, I have a DTS package that needs to export the results of a stored procedure into an Excel spreadsheet. The stored procedure is returning results from global temporary tables. I have a transform data task. The source tab contains the stored procedure name. But on the transform...more >>

Hi all! Past few days I tried to find out what is going on, but without results, so please find some time if you know wjat is going on here... SQL Server 7.0 SP 2 NT 4.0 SP6a... - One database, daily full database backup, backup of tranasction log each hour during working hours, everythin...more >>

How can i transfer a DTS package (the job run well on server1)from server1 to server2, i try open 1 package from server1 and then save the package to server2. I can run the package on CMD use DTSRun and on SQL Enterprise Manager, but while i schedule the package as job , the job on server2...more >>

Hello, Is there a way to stop stored procedure execution upon user requests. A stored procedure is called from APS.NET page. Actually a new thread is started that calls sp, the page is then reloaded every 5 seconds and progress bar is displayed. Usually it takes a few seconds to 10 mins to ex...more >>

Hi, Info: WinNT 4.0, MS SQL 2000 + SP3, NCR ODBC driver I've set up a Teradata connection using the Teradata ODBC. I've set up a transform task to query the Teradata db and insert the results into a SQL 2000 db. When I perform simple queries, there's no problem. When I try to use a much l...more >>

Hi, Can anybody help me out. How to store the dts packages as vbcode/vbscript file through programatically. I tried to store using dts package object using the method SaveToStorageFile and SaveToStorageFileAs. But it is storing in binary format only. I want it in VB Script form similar...more >>

My DTS create a different text file every day. I need to send that text file via ftp. I pass by a Execute Process Task and i use ftp -s:file.txt(that file icluded the ftp parameters and mput command). How i could pass the dynamic file name to my ftp task? Thanks...more >>

Given master table of xid (identity), fa,fb, etc... and detail table(s) of xid, f1, f2... What's the standard practice for performing a bulk import (from another SQL Server or file based) that handles keeping the master/detail id's in sync? Obviously cannot keep the same ID's but do need to us...more >>

I have a DTS package that has been running for months now without a = problem. One of the steps checks to see if it can get a lock on a cube = so the package can loop until another DTS package can finish processing = a different cube in the larger virtual cube. All of a sudden I get a = Microsof...more >>

I have a VB application running DTS package on a client machine. Looks like I have to install enterprise manager on the client machine, otherwise the application cannot create the dts package object. Is it the only way to make it work?...more >>

I have a SQL table where every record needs to be deleted unless the record is listed in an EXCEL spreadsheet. I am thinking that DTS is the way to go. I have used dts to export and import data, but haven't tried anything like this. Does anyone know if this is possible to do or how to d...more >>

Hopefully this posts... I have a DTS Package that is pulling in a text file, importing the records into a temp table. While in the temp table, 2 queries are ran against the data to move any "incomplete" or "dup" records into 2 separate "user" tables... Then run a count statement against the "dup"...more >>

Moin Peoples, I have a problem with Export from MSSQL to MySQL. The DTS write INSERT Error Collumn 5 (DVDB, DBType_STR) DATA Overflow (or something like that In German is this Datenüberlauf). The item from the tables are identical (means type= varchar lenght=255) What means this error and where ...more >>

The log shows the following message when trying to transfer rows from MS SQL SERVER to IBM DB2 OS390. **************************************************************************** ************* Execution Started: 11/4/2003 2:49:40 PM @@LogSourceRows: C:\temp\mssqlgbd2Trace\dtsErroLog2.txt.So...more >>

I am trying to run a DTS package that basically copies an entire database to another location. But it runs until it gives me this error: "ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint 'cimalloc_fkc1'. The conflict occurred in database 'CIM_BU', table 'SMPKDT'." I...more >>

I have a stored procedure on SERVER_A, which I want to call via DTS on SERVER_B. The DTS has a connection to, and calls the stored procedure on SERVER_A, but has a parameter which needs to be a variable. I have made this parameter a global variable in the DTS The DTS is called by a user log...more >>

Hi, I use DTS to transfer data from Excel to SQL table. There are some ids appear more than one times in the file but I just want the latest one. How can I eliminate the previous rows for the same id? Thanks Ava...more >>

After creating some quite complex DTS packages, we need to ensure that they are backed up. I wanted to use a maintenance plan but cannot see any options for this. Help please, as I cannot find the files using search either Cheers Paul...more >>

I create 1 job on server1 and save DTS package DTS_server1 on server1. Then i need transfer the job to server2, i open DTS package DTS_server1 and save as DTS_server2 on server2. On server2 ,execute the DTS package DTS_server2 is normal, but schedule the package DTS_server2 to a job DTS_se...more >>

I need to DTS a text file that is placed up on our network and named file.daily.20031104 (today's date) How do I get DTS to grad that file since everyday it has a different name? Someone told me to set up a stored proc that renames that file first...then let DTS grab it, then rename it bac...more >>

I have a simple dts package that copies data from one table to another (deleting rows in the destination table) for about 20 tables. The source database will always be the same but I'd like the ability to quickly change all references to the destination database for the deletion and insertion. ...more >>

All: I've got a scenario where I need to export SQL tables to MS Access and then allow a user to download them. A couple of questions: 1) Is DTS the way to go, even if the environment is a shared hosting environment? 2) If so, how in the world would I deal with communication between the ...more >>

I have an external Excel spreadsheet which I am importing via DTS. The spreadsheet is divided into sections and therefore the format of each section is different. I've overcome most of the issues this raises, but one remains. Seemingly the DTS engine makes it's decisions of a rows type ba...more >>

New to SQL server here, so if this has been asked, apologies in advance. Using DTS to convert a file, and running into a key problem creating the new file. The file has a two column primary key, and I'm not sure of the syntax to set this up as part of the DTS package. Books online says I can do...more >>

Hi: How do you change the "owner" in a DTS package and a scheduled job? After my organization migrated all user signons to a new Active Directory domain, the domain sign on to the PC changed. Since we are using Windows authentication for SQL server security, I no longer can change my ...more >>

Ok, Trying to work around the bugs using MS DTS in VB... Cannot use the "WithEvents" keyword in VB, soooo, ....cycle through the Stesp in DTS.Package.Steps from the loaded package - Hooray, everything seems to execute .. HOLD IT! ...Now the "For Each" loop in VB executes the steps out of...more >>

I'm not very familiar with excel, but i'm having trouble importing some fields into sql which are droping their leading zeros. ie. 001 becomes 1 in my table. the column in SQL is char (10). thanks in advance, Warren...more >>

We lost our SQL 2000 Server to a disk error. We had a few DTS packages but they are gone after reinstall. Some of the old SQL directories are still accessable but I can't find the DTS definitions to restore. Does anybody know where they are stored and how I can get the back without recr...more >>

Is there a way I can change the connection properties (Server Name, DB Name etc) dynamically. Basically I have a Table consisting of Server Name, DB Name, user name and pwd fields. I have a DTS job that should be able to read each record from this table and change the connection properties...more >>

I recently replaced 1500 lines of older VB code (some of it faulty) with 3 DTS packages under SQL Server 7 - All service packs are up to date (VB and SQL): I am getting errors when running the package under VB6. I'd be very appreciate if someone more experienced in DTS from VB could take a pe...more >>

Hi, I am new to dts. I would like to add activeX script task to my DTS package. when I executed in designer, it failed, and says "Active Scripting was not able to initialize the script execute engine". what do I need to do? Do I need to do somthing on db server or on my box? Thanks...more >>

I need to import a CSV file, 5 million records, into SQL server. I will need to add a new field and then map data into that field based on an existing field and a small lookup table. Which is faster??? Get the data into SQL Server and then running an update query OR doing mapping during...more >>

I saved my DTS package as a .dts file. It has a few versions. Now that it works I want to delete the old versions so it only has one version, the latest. How do I do that? Is there a utility I can download that will do that.? Saving to the SQL server is not an option, our DBA won't let us do...more >>

Hi, I have a dts written by somebody and is running, but I doubt it eveer worked. When I check the output file, it says: Step 'Create Table Step' succeeded Step Execution Started: 11/4/2003 10:12:25 AM Step Execution Completed: 11/4/2003 10:12:25 AM Total Step Execution Time: 0 seconds P...more >>

HI, I have a function worked before in DTS, but I got error recently: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= jmail.SMTPMail Error Description: The message was undeliverable. All servers failed to receive the mes...more >>

I have a sql server connection, a text file (destination connection), and wish the dump the contents of a table to a delimimted text file. I am not new to DTS but have never attemtped this particular function before. I have read about the dummy file table etc, but am still not getting this. Can ...more >>

Hello, I have SQL server 7 SP4 running on Win2k SP3 and MDAC 2.6 (sp2 I guess) I have recently run into problems with DTS Packages scheduled to run some queries and then export tables to text files. The query step would run, but the export to text file step will start exporting and then t...more >>

When I export numeric fields to excel using a Data Transform task the fields become text with an apostrophe. What is the best method for handling this? Currently I am copying a blank spreadsheet then exporting the data. Can I use an Active X script to format the columns in Excel? If so, pl...more >>

I am running a DTS package from a stored procedure using XP_CMDSHELL and sending it parameters. (I just initiate the SP from Query Analyzer). The package typically runs properly. When I was experimenting with the DTS package & transaction control from DTS, I set the Package Property ...more >>

I am using SQL2000. I have a DTS package that incrementally updates 2 dimensions, then incrementally updates the cube. Not always, but sometimes, I get the error 'Invalid Clipboard Format' when trying to process the cube. I get the same problem if I incrementally update the cube and dimensi...more >>

Is there a way to send an email alert based on the current run time of a job? For example, I want to send an alert if a certain DTS job runs for more than 2 minutes. Is this possible? If so, can anyone tell me how to set this up?...more >>

The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e2f): INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_fctSales_dmProduct'. The conflict occurred in database 'MgkOlap', table 'dmProduct', column 'item_code'.) Package failed because Step...more >>

The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e2f): INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_fctSales_dmProduct'. The conflict occurred in database 'MgkOlap', table 'dmProduct', column 'item_code'.) Package failed because Step...more >>

hi, I am trying to save the SQL Query Analyzer results to a .rpt file. and i open it in a XL sheet. every thing is ok, but when the numbers like ex: ClientNo 0069007 after saving it to rpt file and if i open in XL sheet , the the two zeroes of the ClientNo are being supressed. i.e. ...more >>

I am trying to import from Excel without success. The problem seems like a data convertion problem. The destingation table is DBTYPE_BOOL and the source is DBTYPE_R8. How can I force the excel into a fixed format (say boolean) so that this error will not occur. I tried it with other forma...more >>

"If the SQL Server was started using the Local System account, the DTS package has no permissions outside of the computer that is running SQL Server." My server is logged on by the local system. I have a DTS package that needs to email files. The scheduled job is being executed via the sy...more >>

Hi NG, i got some DTS-packages saved as structured-file (.dts) Is there a way to store them into a sql-server by vbscript (like an installroutine)? kind regards frank ...more >>

Hi, Please provide me the details about the data tool which generates sample data(I want atleast million rows)....more >>

Howdy all. Im not a guru VBS guy and Id like to use it to script out SQL2K db objects. Say I just wanted to script out the views and nothing more. Can VBS do this? Can someone get me pointed in the right direction? TIA, Chris ...more >>

On SQL Server 2000, when I first go to DTS/Local Packages/New Package, I get a popup message "DTS Designing Error". Within the package, I created an Oracle/ODBC connection. Created a connection to the local SQL Server. Attemped numerous times to create a transformation data task from the Oracle...more >>

Hello, I'm calling a dts package inside a Stored proc (exec xp_cmdshell..dtsrun)and I'm getting an error: Error: - 2147217900 (80040E14); Provider Error: 14262 (37B6). Microsoft said they have fixed this bug in the sql server 2000 sp2 release. We are using sp3 but I still get this probl...more >>

Hello, I have created a DTS package that inserts and deletes records in an Access database. I would like to run the Compact method against the Access database when the package completes. I imagine this will have to be done with an ActiveX script task. Is there anyone who can show me som...more >>

I have a DTS step that on failure sends an email through an ActiveX script. I do not want the package to indicate that it has failed though. How can I set the execution status of the package to be successful and not failure?? TIA, Scott Lindsey...more >>

Hi, I have a DTS, there is a connection in it. I use the use sa to connect, but I got "Login failed for user "sa"". I can login by sa in QA. what's the problem? Thanks...more >>

I'm running SQL Server 7.0. Once a while, I got an error message when I executed a DTS package. This problem disappeared after I restarted the database service. Below is the error message: Eror Code: -2147217887; Error Source: Microsoft OLE DB Provider for SQL Server; Error Description...more >>

MS online books say the following: Use the schedule option in the DTS run utility (dtsrunui) if you need to schedule a package saved as a structured storage file. Well, I've tried using the dtsrunui but it does not allow me to "schedule" the package. I can run the package but not schedu...more >>

MS online books say the following: Use the schedule option in the DTS run utility (dtsrunui) if you need to schedule a package saved as a structured storage file. Well, I've tried using the dtsrunui but it does not allow me to "schedule" the package. I can run the package but not schedu...more >>

Hi all, I have a DTS package with a transform data task which takes data from a dBase III file and inserts it into an SQL table. The source connection is a dBase III connection. The destination connection is a Microsoft Data Link file, which points to an SQL 2000 server. The task imp...more >>

I need to trigger a CSV file import into a SQL Server database from Access, using VBA code. Is there a way to trigger a DTS local package from an Access VBA module ? Many thanks for any help, Jonathan....more >>

Due to some operational constraints, I've written a procedure to create, set some properties and execute a small (time, size) DTS package from a stored procedure. It just moves some data from one place to another with some fairly simple tranforms. I'm using the sp_OA* family of procedure...more >>

Hi: I've covered a number of newsgroups and web sites today, but still cannot seem to find out why my DTS package (which works fine via SQL) fails to work correctly when I execute it from a C# program. Here is the code I use: object pVarPersistStgOfHost = null; string val; try ...more >>

Trying to import the following line using bcp 1;1;Tiger Woods;64;25.7;296;1,152 with the format file 7.0 7 1 SQLINT 0 4 ";" 1 THISWEEK 2 SQLINT 0 4 ";" 2 LASTWEEK 3 SQLCHAR 0 100 ";" 3 PLAYER 4 SQLINT 0 4 ";" 4 ROUNDS 5 SQLFLT8 0 8 ";" 5 PERCENTAGE 6 SQLINT 0 4 ";" 6 BIRDIES_EAGLES 7 SQLI...more >>

HI, I have a DTS run under sqlagent. I got the error: DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_2 DTSRun OnError: DTSStep_DTSActiveScriptTask_2, Error = - 2147220421 (8004043B) Error string: The task reported failure on execution. Error...more >>