I'm writing a dts package that imports a file. I want to allow my user to
browse to the file, but can't figure out how. Any suggestions?

Thanks, Andre

You would generally do this in the FE app that calls the package. Once the user has chosen the file you pass the name and location
to the package and execute.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Andre" <no (AT) spam (DOT) com> wrote

Thanks Allan. I found this package on your site, but it would probably
require me to register this dll on every computer that I want to run this
package from - right? But at least it does exactly what I want it to do.

http://www.sqldts.com/?226

Andre

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Correct

The problem here is the user needs direct access to the package itself which is maybe not something you want. Personally I prefer
to build the package my way with my valkues and have the user enter via another way anything they need to change. I do not in
general allow them to mess with my packages.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Andre" <no (AT) spam (DOT) com> wrote

Ok, thanks for the advice. I appreciate your input.

Andre

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

In message <#cRJ83l0EHA.2600 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Andre <no (AT) spam (DOT) com>
writes
You will need this DLL and also the DTS DLLs on every machine. There is
an MSI available as well to make it easier to install the task.

It still isn't a great idea as Allan points out, but still it may be
useful.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Allan,

What if you wanted to query a folder and delete files based on number of
days using a DTS package, can this be done?

- Sonya

"Allan Mitchell" wrote:

Number of Days what?

Days Old?

Sure you can do that.

You can Loop through the directory and check the file date.


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Sonya" <Sonya (AT) discussions (DOT) microsoft.com> wrote

Another guy was helping in creating a script to do what I want. I'm having a
problem with the directory command in the code. Maybe you can suggest
something that I am overlooking. Below is the code using tsql:

---- Removing Older Backup Files -------------------

-- Remove any log files older than 10 days
DECLARE @Error INT, @D DATETIME
SET @D = DATEADD(dd,-10,GETDATE())

EXEC @Error = hq_remove_old_log_files @D


SELECT @Error
GO
------------------
Drop proc hq_remove_old_log_files
go

CREATE PROCEDURE hq_remove_old_log_files
--@DelDate DATETIME

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Delete VARCHAR(300), @Msg VARCHAR(100), @Return INT,
@DelDate Datetime

-- Used later when we cast strings to Datetimes
SET DATEFORMAT MDY

-- Create a table to hold the results of the DIR command
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))

-- Create a table to hold any errors
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
DROP TABLE #Errors

CREATE TABLE #Errors (Results VARCHAR(1000))

-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec @Return = master..xp_cmdshell 'dir /OD C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\DATA\*.Bak'

SET @Error = @@ERROR

IF @Error <> 0 OR @Return <> 0
BEGIN
IF @Return = 1
SET @Error = -1
SET @Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END

-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'


-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @DelDate
AND SUBSTRING(FName,40,40) LIKE '%.BAK'
ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname
WHILE (@@fetch_status = 0)
BEGIN


-- Delete the old backup files
SET @Delete = 'DEL "C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DATA\' + @FName + '"'

INSERT INTO #Errors (Results)
exec @Return = master..xp_cmdshell @Delete


IF @@RowCount > 1 OR @Return = 1
BEGIN
SET @Error = -1
SET @Msg = 'Error while Deleting file ' + @FName
GOTO On_Error
END

PRINT 'Deleted ' + @FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)

FETCH NEXT FROM curDir INTO @Fname
END


CLOSE curDir
DEALLOCATE curDir



DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO




"Allan Mitchell" wrote:

I am creating a DTS package to check disk space on the servers within the
network. So far i have this but I'm not sure what else I need. I entered the
server to test the script. The results I want is the Servername, Disk space
usage, Disk space available and the date of the report. I want to schedule to
run this initially twice a month.
Any suggestions on the code?

Function Main()
Main = DTSTaskExecResult_Success
Server.CreateObject = "sonyamcneal"

'Script to display a list of drives connected to this machine

Set FileSystemObject = Server.CreateObject("Scripting.FileSystemObject")
'Create a filesystem object

Set Drives = FileSystemObject.Drives 'Create a drives collection

'step through the drive collection, and extract
'the drive letter and the type of drive
'according to the Microsoft VBScript documentation
'there are 6 distinct types of drive

For Each DiskDrive in Drives

DriveLetter = DiskDrive.DriveLetter
DriveType = DiskDrive.DriveType

Select Case DriveType
Case "0" DriveType = "Unknown type of drive"
Case "1" DriveType = "Removable drive"
Case "2" DriveType = "Fixed drive"
Case "3" DriveType = "Network drive"
Case "4" DriveType = "CD-ROM drive"
Case "5" DriveType = "RAM Disk"
End Select

Response.Write "Drive " & DriveLetter & " is a " & DriveType & "<BR>"

Next

Set Drives = nothing
Set FileSystemObject = nothing

End Function


"Allan Mitchell" wrote: