HI, Can anyone tell what I'm missing to be able save an excel workbook to text format using dts. Excel_WorkBook.SaveAs "E:\EXcelData\rabbit.txt" Which save's it fine , but I need to be able to set the format for this to be done properly Regards Andrew...more >>

I'm using BCP with queryout and exporting data from a table to a .csv file. Using this as a template it works fine but is there anyway to get the column headings along with the data. I'm using this method because I can create different output filenames. I have a DTS to do this but I can't ...more >>

Hello everyone, I am using the "import" function in SQL to import a big text file to SQL. It will import all the lines, except that starting from line 989, it just to a line way down at the bottom of the text file, line 70987. Isn't it strange? I thought the import function will read fro...more >>

I basically need to send emails from MY DTS packages and also some of my stored procedures.There is no way of having any MAPI compliant software installed on the productions database server,so I came up with the idea of installing an SMTP server and use the extended stored procedure introduced h...more >>

I have mixed data in one Excel column (integer and string). If the integers on the top I can only import integers and all string values will be NULL. If the string values on the top I can only import strings but the integers will be NULL. Is there any way to import mixed data? Thanks for any ...more >>

Hello, I have a text file that needs to be read into SQL. I can read the file alright, but I have problems adding a primary key to the SQL table and make it increament automatically. If I read the text file into a new table, the primary key is not created. If I created an empty table and...more >>

Hi! I have a text file over 70000 lines, some lines are long some are short. I tried to import this text file into SQL, but all it did was to take the shortest line as the width and truncated the long times. Though I have in the "transform" to change the field to varchar and length to 100...more >>

Hello everyone, I am importing a flat file using dts. I notice that if there is an error in the file, the transactions are rolled back. I need to import each row as a transaction in itself. How do I do that ? For example if the There are 2000 rows and 1 row is bad then 1999 rows should be impor...more >>

I have a DTS package which exports from a SQL table to an Excel spreadsheet - nothing tricky so far. The (SQL Server) source table contains several columns which have more than 255 chars of data. When I created the destination worksheet in DTS Designer, I specified LongText as the data type for ...more >>

Besides writing custom tasks which use SMTP Server to send ongoing emails ,Is there a simpler and faster way to send an email through SMTP Server for example by creating an object or somthing in my ActiveX Script? Thanks ...more >>

I have a Windows 2003 server running SQL Server 2000 with a linked server on Windows 2000 server running SQL Server 2000. Both machines are on the same domain and Distributed Transaction Coordinator is running. I have applied the instructions in KB article 817064 ( http://support.microsoft...more >>

Has anyone had the issue where you have jobs executing or retrying and the server maintenance people reboot the server? After the reboot, I have been manually restarting any jobs that were executing or retrying. Is there a way to restart the jobs automatically/progammatically? Is there a tabl...more >>

Folks, (sorry bit of a long story) I Tested a DTS package last week. Nothing too complicated one connection to SQL DB and one to Oracle DB. It runs thirteen select and update statements against the Oracle DB. Last week I terminal served into the SQL server box the job is on and set it ...more >>

I'm trying to write a transformation script to write null whenever an overflow is possible due to invalid date/time string, rather than the DTS package fail because the data couldn't be imported from the file into the table. I'm not sure how to check to see if the cast is possible. The source...more >>

How can I get my admin notified by an error which happens in Data pump? for instance primary key violation?? I know that I can use "Send Email Task" but how can I catch these kinds of Error in my Data pump's activeX?? Thanks ...more >>

I am using this task to report errors to admin.In our production enviroment ,there is no Exchange server or everything else.What is this task using for sending emails? Do I have to configure things in production server? Thanks ...more >>

Hi, I'm Jennifer Kenney, I'm mostly SQL/TSQL, but I'm using DTS for obvious DTS things, and I've got an issue with DTS respecting the Row Delimiters in a text file (source) connection. The fixed-width text file has a CRLF row delimiter, visible when I read it in Word with the view options s...more >>

We have Outlook contacts in a folder. I know I can link to Contacts using Access, but I wanted to set up a DTS Package to move these contacts into a SQL Server Table. problem is I cannot get DTS to even find the contacts folder. I don't see a driver for this. Is there a way to do this? Th...more >>

I have a bunch of Activexscripts in my DTS package This DTS package is going to be schadulaed to run automatically .In my activeX code I use ADO connections in those connection strings I used Integrated Security.Will it be problematci when the DTS package becomes schaduled? Which type of au...more >>

Hi All, I would like to be able to dynamically change the server to which logging occurs for a given DTS package. For example, I have a DTS that is configured to log to Server A. I would like to pass as a parameter Server B and have the DTS package log to Server B. So far it seems as tho...more >>

Hi All, My DTS Imports data from a Comma Delimited TEXT from to SQL Server table. Thsi works fine 99% of the time. But Fails some time by giving the below ERROR - that I Log in a text file. Step 'DTSStep_DTSDataPumpTask_1' failed Step Error Source: Microsoft Data Transformation Services F...more >>

I have a DTS package that extracts data from a Sybase 11 datasource into a holding table in SQL Server 7 using a data-pump. The Sybase data-source is meant to return something in the region of 1million records. Because of a large date range from the query in Sybase, even putting it into a ...more >>

I'm using Transformation Data Task to copy data from one server to another database server. I need source and destination connection parameter to set dynamically for every execution. I used global variables to set connection parameter dynamically but no luck and after a bit of searching I found t...more >>

I have a package launched trougth a my program. Sometimes, due to connection problem with a remote sql server, it hang on a query. I want from a my application to stop the package. I did not get error or any exception from the package, nor the OnCancelQuery is called. The only way I see to stop...more >>

I'm trying to save a excel file to tab delimited format using a dts script. However I can open the sheet I can save the sheet as a different name, but I don't seem to be able to save it as a tab delimited file Can any one help ? Thanks in advance Andrew...more >>

Hi All, I use the DTSRUN utility with /L option to provide the log filename with path for logging. I think in Package property "Error File" option is same as the above but there the NAME will be given in design time. Also there is an option to log to SQL Server - MSDB and also to eventlog...more >>

Is there a way to execute a DTS package from a VB application without the user having sysadmin privileges? Is there a grant execute command or other means? We have a VB application that will be used by several lower privileged customers. We don't want clear-text passwords in any code or ini f...more >>

I'm calling an Execute sql task from my script programmatically and I want it to return its result which is a count into a global variable?? How should I specify it? Thanks ' Get Package Object Set oPkg2 = DTSGlobalVariables.Parent ' Get Exec SQL CustomTask Set o...more >>

my DTS has activex script task which is to call a web service (the web service is running on different machine). When I execute the DTS Package, it succeeds. But if I execute the job to run DTS, I got the error DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11)...more >>

Hi: There is a datetime field in a SQL Server database. Using ADO, I extract the maximum date as follows: strSQL = "SELECT MAX([Timestamp]) AS MaxDate FROM " & TableName which I then execute. I then set a variable to this value: varMaxDate = objRS.Fields("MaxDate") and then I use thi...more >>

Hi all, I am building a DTS pakage to read from a text file and Populate a SQL Server table. I am using the Enterprise manager to build the package. I wanted to know if there is a way where I can specify information like the path of the source text files, Login/pwd,server name of the detinati...more >>

Configuration: Server-1: COM+ components installed on W2K3 Server-2: SQL 2000 Server SP3 installed on W2K3 We use an application that has its components on server-1 and accesses data on server-2. Everything works fine when querying data (ex.customer information) but when we try to save any ...more >>

hi I am getting followinh error in only win XP sp2 machine otherwise it was working The number of failing rows exceeds the maximum specified. Microsoft OLE DB provider for SQL Server (80004005): unspecified error Any one have any idea? Regards AH ...more >>

Hi, I am trying to export some records everyday to a destination (.xls) file through SQL Server Enterprize Manager. The DTS package works fine .... BUT the problem is every time the DTS package runs the records get appended in the .xls file. HOW CAN I MAKE SURE THAT THE .XLS FILE IS OVERWRI...more >>

I'm trying to do FileExists with a full path, and one of the directories is always the wrong case. Is there any way to tell the FSO to use text-compare? Thanks. Josh ...more >>

Hi, Does the "Dir" function work in DTS VB Script language (SQL2kServer) ? If not how can i put all the files contain in a directory as input file ? Rgds...more >>

initiated manually. This is driving us nuts. Whenever I manually open and run the job from the console it works fine. However, whenever I schedule the job, it fails on two steps of the job. The two steps that its failing on are db pushouts to a server that is on the same subnet. The step...more >>

I would like to keep a FileSystemObject open across phases. Specifically, I would like to open a text file during the Pre Source phase, write to that file in the Row Transform phase, then close the file in the Post Source phase. I thought I could keep the FileSystemObject in a Global Variable. I ...more >>

Hi, I have a publisher and some pushsubscriber with merge replication. When I insert a textfile (DTSFlatFile) with dts to a publisher table, the data will be inserted in the publisher table, but the merge-agents don't copy this new data to the subscriber tables. What is the reasen for thi...more >>

I would like to know if there is a way to reference the value of a field while importing data into a table. I am loading data from a flat file into a SQL Server table using a custom ActiveX transformation. I set the value of the table field to a value from the flat file. Can I immediately...more >>

Hello all, I have saved a DTS file as a Visual Basic File. I have two questions. Can I create a button in Excel that will execute the DTS and how do I do it? :) Thanks ...more >>

We have a few large DTS packages that make up our ETL process for our Star schema reporting database. These packages run every night. If one of those packages were to fail mid-way through, we would have to track down the error(which is whole 'nother problem) and then restore the entire (ve...more >>

Can you dynamically change the names of (text output files) . I have a datapump task that exports to 7 different output files. I would like to change the names of these files based upon the date/time I run the process. ...more >>

Hi all, I have a bunch of DTS packages to load our data warehouse periodcally. Recently it often invokes the "timeout expired" error. Sometime in this package, sometime in another one. I also find the error always occur when loading the package, namely not in a "bad" T-SQL. Can anyone he...more >>

I have wide source table (not exactly a table - data comes form query) like ..... col5 col6 col7 col8 ..... I want to store colums 5-8 to one doctionary table and remaining columns to another. I can this to trigger on dummy destination table and instead of but i must turn off fast loa...more >>

I need to create a whole bunch of DTS packages based on a list of input files and tables. I have the mapping of file name, table name, and what each input field maps to in the table. All this is in a spreadsheet. Is it possible to generate a new DTS package from VB, VBA, VB script, etc? If s...more >>

Hello, How can I achieve the following? Have a batch file that will get executed from a stored procedure using xp_cmdshell. The batch file will call another stored procedure using OSQL. The stored procedure (after its processed the data) returns a number. Further process flow in the batch...more >>

Hi! Each week, we receive a two-page PDF file from UPS along with a separate flat file (a CSV) The PDF file contains the overview of our weekly invoice and the CSV contains the details of each shipment. I download the file from UPS and then use DTS to import the data from the CSV ...more >>

Hi Gang: I've inherited a database that contains a number of tables with bit value columns. I am trying to write a script with bulk insert statements to reconstruct the database. When I export the table data, the bit fields get converted to string equivalents (True and False.) When the b...more >>

I am trying to import some rows from a Excel sheet to a SQL Server table, but when the first 200 rows on the sheet does not have values for a column, the dts asumes that the whole column does not have values and import a NULL value into the table, even if the column has values on the 250th row...more >>