Hi, Could you help me modify the SELECT statement below that would accomplish my final goal. SELECT col1, col2 FROM table1 WHERE col3 < @V In col1 rows have many duplicates col2 has unique values col3 can have 5 possible values: 5, 10, 15, 20, 25 Variable @V can be equal to 5 or 10 or 15 o...more >>

I am trying to use DTS to copy tables, using copy objects, from one DB to another and after about 10 min of processing I get an error: "...EXECUTE permission denied on object 'sp_grantdbaccess', database 'master', owner 'dbo'. How do I get by this? I do not own the server - I just have an SQ...more >>

I'm still in learning mode with regular expressions and am attempting a few things during a Trasformation task from one table to another. Basically what I'm trying to to is this: during the copy/import from one table to another, I'm trying to somewhat validate an email field. When it returns...more >>

Hello, How Could i send mail using vb script in Active x Script Task??? ...more >>

I have an interesting scenario. I have to exact table structures for 2 identical systems that function the same way. There are 3 tables that are linked via primary keys, ex. table 1 table 2 - linked to table 1 via table 1 ID autonumber key table 3 - linked to table 2 via table 2 ID auto...more >>

Hi All I'm aware that you can do something with the naming conventions so it's easier to spot which packages concerns the same topic but do you save them on the sql server or as structured storage files ? With structured storage files I can get a folder structure to give me another step to mak...more >>

How can I create an excel file dynamically and export data to a specific sheet from sql server dts?. Karunakaran...more >>

I'm trying to use DTS to build test databases. I'm attempting to copy my production data with the exception of one very large transaction table. Using enterprose manager I build a DTS package that uses the copy object task. In the copy specification I uncheck "copy all objects" then use the ...more >>

I am experiencing problems with data load from some of the excel files. We get the excel files from a particular vendor everyday and some times the DTS package fails. In DTS package, I am using "Microsoft Excel 97-2000" for the data source. Whenever the package fails. I get the following error...more >>

Hi everyone I have a package that contains 2 Task (Active x script & send mail task) In The active x script task there is a loop in it for a condition until it is achieved, i wont for each loop, the send mail task be executed is it doable?? if yes, how??????????? ...more >>

Hi, I was wondering if it is nessesary to use the statements 'begin transaction' and 'commit transaction' in a sql task within a dts. I have a task where i update some data, but i thought that if the dts fails the whole job will be rolled back. Is this true? ...more >>

The only info I can find on syntax for using global vars is in activex script or in an sql query. I have already added a global variable and set a default value in the package properties. I have a Process Execution Task and would like to use the value of the global variable as the parameter t...more >>

Hello I created a simple dts package that ports data from one view to a txt file (sounds simple:) ) I created two datasources, one for the sql server and one for the txt file. Now comes my problem, I use sql query to select wich data to port to the txt file, the select statement is like thi...more >>

We created a DTS package that imports data from an Excel file to a Temporary table and then does an Update to the primary databse table. The DTS works properly when executed from the Enterprise Manager. What we really want is to allow the End User to execute the package from the Excel f...more >>

I was able to run DTS using xp_cmdshell. In my dts I have 3 global variables which I would like to pass. Can I pass a variable values using xp_cmdshell? xp_cmdshell 'DTSRUN /S ServerName /U user/P pass /N "DTS_Name" '...more >>

Hi Anyone than knows how to change the collation name for a database. Or can you only do that when you create a new one?? /Anett...more >>

Having problems with multiple columns in ActiveX script. I've tried everything I've found on Microsoft website, numerous newsgroups and websites. I'm just wanting to do a data transformation and I'm new to DTS but this will save me hours and hours of work. The lookup as follows:- SELECT ...more >>

Hello: I'm trying to use a parameter in an SQL Task. The task's data connection is just to the local SQL Server. It should be simple enough: DELETE FROM dTable WHERE DATEDIFF(d,[Timestamp],getdate()) < ? However, when I click on the "Parameters" button I get an error: "Invalid Parameter...more >>

Hello Is there a method of determining which objects (particularly tables) are including in DTS packages? We have a requirement to rename a table in a database and don't want to open every DTS package to check for a dependency. Nor do we want to just wait to see which packages fail once th...more >>

My DTS package has an SQL statement to select data from an Access database table. Adding the substring to the SLQ causes "ADO error: ." to be displayed. The SQL runs ok without the substring. I have tried putting the substring inside a Cast statement and that did not work either. DISTRI...more >>

Hello My DTS transformation is fighting with me. I am exporting a smalldatetime field from a SQL Server table to a text file. The requirements of the text file is that only the date (not the time) is put into the file. And the date must be in YYYYMMDD format. This seems amazingly simpl...more >>

Hi I have 2 Activex Script (one assigns variables from recordset and other is loop ) In the first one i have the following code Function Main() Dim rs rs = DTSGlobalVariables("gvRecordSet") if Not rs.EOF then Select Case rs("UdlId") case 1 DTSGlobalVariables("gvMetaDataUdl")...more >>

I accidentally renamed my connection (Text source) and now I have two connections in the list of existing connection (when I add a dynamic property,I will see them),my old name and the new name.How can I delete the old one? Thanks ...more >>

Hello, Instead of defining a dynamic property task in each DTS I rather would have an ActiveX Script task changing the properties of a package. 1. Is this possible? 2. How is it done? Thanks Rene...more >>

I'm using a datapump task to transfer records from a CSV file to a table,I am doing validation while tranforming and when it's done ,I use an stored procedure to enforce my business rules.somebody told me that Data Driven task will do the same thing and it's better than using stored procedure....more >>

I'm trying to convert date text to date time. I can use dateserial sucessfully as shown below, however it is not uncommon to receive bad dates in my data feed. Iv'e done a test and added 20051145 as a bad date, but dateserial converts is to 12/15/2005 (just adds the days). dim v_Day ...more >>

If the source database is a SQL Server database and the destination database is a SQL Server database, do you need to use DTS? Is it possible to use a stored procedure instead? Are there advantages to the stored procedure solution? ...more >>

I had a question on executing DTS packages on Win XP/2003. We developed a DTS package in VB 6 to copy data from an MS Access (97/2000) database to a SQL Server 2000 database. This standard .exe package runs fine on Win 2000. However, on Win XP/2003, it runs in a haphazard manner i.e. it copie...more >>

Hi I am using looping,importing and archieving example for importing multiple files into sql server database. here is my code create table -----> Active x Script 1 -------> Text File Source ------> transformation task -----> Active x script 2 Active X script 1 *********************...more >>

I'm trying to use the Execute SQL Task to call up a batch store procedure that takes typically 1 hour to run. This batch procedure involves retriving data from linked servers and some heavy data processing. If I were to run this batch SP from query analyser there's no problems but everytime...more >>

I have Transform Data Task that I want to Lookup some company values from another database based on a code from the source and insert them into the destination Under the Transform, Workflow Properties, Options, Use ActiveX script checkbox I have my code to call the Lookup below, but I keep ...more >>

Hi, I'm trying to load a table from a dts job that selects all the records from sybase table and imports it into an equivalent MS SQL 2000 table. I have done this successfully for 16 tables. However for one table I get the following error on insert: Error at source for row number 19000...more >>

Hi, has anyone an eyample for vb wo to do an query and afterwards export this query to exel? reg bxandi ...more >>

Hi, I am wondering the WriteStringToLog will write the log to the windows log or sql server log? THanks Ed...more >>

Hi I have a Parent package (which has a Global variable ) calls the child package. I have made the global variabe from Parent package as "Outer Package Global Variable " When i use this in Child package Activex Task DTSGlobalVariables("Child_GlobalVariable") = DTSGlobalVariables("Paren...more >>

Hi All, Please advised how to using query update table, but I don't want to clear table before updating....more >>

Hi I have an issue with some SQL Jobs: Mostly they quit with success after 30 sec, but sometimes they just freeze. For hours (and days) the Status for the job is just "Executing Job Step 'job name'" and I have to manually restart the job. How can I automatically restart such jobs that s...more >>

I have a DTS package that is designed to download files from a FTP server and then unzip the files into a pacticular folder. The strange thing is that if I where to run the package manually it works but if i schedule it to run periodically it'll fail on the unzip file step. I basically used...more >>