March 30, 2006 at 2:53 am
Hi
We have an asp.net application that imports excel data into a table. Before we had SQL2005 that programs calls a stored procedure that runs a dts package.
How do u call a dtsx package in a stored procedure in sql 2005 ?
Many thx
El Jefe
JV
April 3, 2006 at 8:00 am
This was removed by the editor as SPAM
April 3, 2006 at 9:09 am
I think you'r need to set this as a job and start the job.
April 3, 2006 at 9:20 am
thx
will give it a shot
JV
April 3, 2006 at 9:55 am
Use xp_cmdshell and dtexec
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Usage: DTExec /option [value] [/option [value]] ...
Options are case-insensitive.
A hyphen (-) may be used in place of a forward slash (/).
/CheckF[ile] [Filespec]
/Checkp[ointing] [{On | Off}] (On is the default)
/Com[mandFile] Filespec
/Conf[igFile] Filespec
/Conn[ection] IDOrName;ConnectionString
/Cons[oleLog] [[DispOpts];[{E | I};List]]
DispOpts = any one or more of N, C, O, S, G, X, M, or T.
List = {EventName | SrcName | SrcGuid}[;List]
/De[crypt] Password
/DT PackagePath
/F[ile] Filespec
/H[elp] [Option]
/L[ogger] ClassIDOrProgID;ConfigString
/M[axConcurrent] ConcurrentExecutables
/P[assword] Password
/Rem[ark] [Text]
/Rep[orting] Level[;EventGUIDOrName[;EventGUIDOrName[...]]
Level = N or V or any one or more of E, W, I, C, D, or P.
/Res[tart] [{Deny | Force | IfPossible}] (Force is the default)
/Set PropertyPath;Value
/Ser[ver] ServerInstance
/SQ[L] PackagePath
/Su[m]
/U[ser] User name
/Va[lidate]
/VerifyB[uild] Major[;Minor[;Build]]
/VerifyP[ackageid] PackageID
/VerifyS[igned]
/VerifyV[ersionid] VersionID
/W[arnAsError]
May 18, 2006 at 2:21 am
Hi,
If you want to start it directly from procederue You could also check
this procedure, it's working with 2000 server and I'm not shure if it'll work on 2005 but You try it.
DTS which I start with it is design to import files, there's one connection in DTS which points to file with connection name SOURCE_FILE (case sesitive), so you just give name of DTS, and path to file to import on machine where you are running this procedure.
ALTER PROCEDURE [dbo].[MRS_RUN_DTS]
@sDTSPackagePath varchar(1000)=NULL,
@IMPORT_FILE VARCHAR(100)=NULL
AS
DECLARE @DTSPackageObject int
DECLARE @HRESULT int
DECLARE @property varchar(255)
DECLARE @return int
DECLARE @ErrDescrip varchar(255)
DECLARE @ErrSource varchar(30)
DECLARE @ErrHelpId int
DECLARE @ErrHFile varchar(255)
DECLARE @ErrMsg varchar(255)
DECLARE @sDTSSpecialUser varchar(50)
SELECT @ErrMsg = 'Error running DTS package'
--NOTE: remember to give the account running this procedure access to the sp_OA system stored procedures
IF (
@sDTSPackagePath IS NULL
)
BEGIN
PRINT 'MRS_RUN_DTS ''DTS_NAME'', ''FILEPATH'''
END
ELSE
BEGIN
SELECT @sDTSSpecialUser = 'dbo'
IF @sDTSPackagePath is NULL
BEGIN
SELECT @ErrMsg = 'Error DTS package not found'
RAISERROR (@ErrMsg,11,1)
END
--lightweight security and/or safety
IF NOT @sDTSSpecialUser is NULL
BEGIN
IF NOT @sDTSSpecialUser = CURRENT_USER
BEGIN
SELECT @ErrMsg = 'Error you cannot run this ID ' + CURRENT_USER
RAISERROR (@ErrMsg,11,1)
END
END
-- Create a DTS Package object
EXEC @HRESULT = sp_OACreate 'DTS.Package', @DTSPackageObject OUTPUT
IF @HRESULT 0
BEGIN
EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
RAISERROR ( @ErrMsg,11,1)
END
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'LoadFromSqlServer',NULL, @ServerName='servername', @ServerUserName='sqlmrs', @PackageName=@sDTSPackagePath, @Flags=256, @ServerPassword ='password'
IF @HRESULT 0
BEGIN
EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
RAISERROR ( @ErrMsg,11,1)
END
if @IMPORT_FILE is not null
begin
EXECUTE @HRESULT = sp_OASetProperty @objecttoken=@DTSPackageObject, @propertyname='Connections("SOURCE_FILE").DataSource', @newvalue=@IMPORT_FILE
IF @HRESULT 0
BEGIN
EXECUTE @HRESULT = sp_OASetProperty @objecttoken=@DTSPackageObject, @propertyname='Connections("source_file").DataSource', @newvalue=@IMPORT_FILE
IF @HRESULT 0
BEGIN
EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
RAISERROR ( @ErrMsg,11,1)
end
END
end
-- Set the FailOnError property to true
EXEC @HRESULT = sp_OASetProperty @DTSPackageObject, 'FailOnError', -1 --Set to true
IF @HRESULT 0
BEGIN
EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
RAISERROR ( @ErrMsg,11,1)
END
-- Call the EXECute method
EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL
IF @HRESULT 0
BEGIN
EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT
SELECT @HRESULT, @ErrDescrip
END
ELSE
BEGIN
SELECT 0, 'Succeeded'
END
--remove the object from memory
EXEC sp_OADestroy @DTSPackageObject
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply