We can
execute a SSIS package and pass the parameters by sql query in sql sever.
Suppose we have created a SSIS package with:
Name of package with path: C:\SSIS\ImportData.dtxs
Suppose it has two user defined variables:
1. ConnStr
2. FilePath
Now we are creating a stored procedure to execute this SSIS package:
CREATE PROCEDURE uspImportdata(
@ConnStr AS NVARCHAR(2000),
@FilePath AS NVARCHAR(2000),
@PackageName AS NVARCHAR(2000)
)
AS
BEGIN
SET NOCOUNT
ON
DECLARE @StatusCode INT
DECLARE @Params AS NVARCHAR(2000)
DECLARE @SSISCommand NVARCHAR(4000)
DECLARE @ErrMsg AS VARCHAR(MAX) = ''
BEGIN TRY
SET @ConnStr = '/set
\package.variables[ConnStr].Value;"\"' + @ConnStr + '\"" '
SET @FilePath = '/set
\package.variables[FilePath].Value;"\"' + @FilePath + '\"" '
SET @Params = @Params + @ConnStr +
@FilePath
SET @SSISCommand = 'DTEXEC /f ' + @PackageName + ' ' + @Params
CREATE TABLE #ErrTbl(
ErrMsg VARCHAR(2000)
)
INSERT
INTO #ErrTbl
EXEC @StatusCode = xp_cmdshell @SSISCommand
IF @StatusCode = 0
SELECT 'Success'
ELSE BEGIN
SELECT @ErrMsg = @ErrMsg + ISNULL(ErrMsg,'') FROM #ErrTbl WHERE
ErrMsg LIKE '%Description%'
RAISERROR(@ErrMsg,16,1)
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
END CATCH
END
To execute it:
EXECUTE
uspImportdata
'Data
Source=ServerName;Initial Catalog=DbName;Integrated Security=True;',
'C:\SSIS\demo.txt',
'C:\SSIS\ImportData.dtxs'
No comments:
Post a Comment