如何使用SQLServer 2008从存储过程调用SSIS包? [英] How to call SSIS package from stored procedure using SQLServer 2008?

问题描述

我需要使用ssis将excelsheet数据导入到sqlserver表中.为此，我使用了一个存储过程，并将存储过程的参数用作"@path".
我正在从前端文件上传器传递此路径.现在，当我从存储过程中调用程序包时，由于无法使用DTS，SQL，Server，User或Password选项指定File选项"而给出错误消息.
我搜索了，但是什么也没得到.请帮我.我的代码是:

I need to import excelsheet data to sqlserver table using ssis. For this I am using one stored procedure and a parameter for the stored procedure as "@path".
I am passing this path from frontend file uploader. Now, when I am calling my package from stored procedure, it is giving error as "The File option cannot be specified with the DTS, SQL, Server, User, or Password options".
I searched, but I am not getting anything. Please help me. My code is:

--exec execpackage '\D:\jyothi\personal\Book1.xlsx'
ALTER PROCEDURE [dbo].[execpackage]
(
	-- Add the parameters for the stored procedure here
	@path varchar(max) 
)
AS
BEGIN
	declare @ssisstr varchar(8000)
	declare @packagename varchar(200)
	declare @servername varchar(100)
     declare @params varchar(8000)
----my package name
set @packagename = 'D:\jyothi\personal\excelsheet2\excelsheettype\Package1.dtsx'
----my server name
set @servername = 'YIITUSER1\SQL1'

---- please make this line in single line, I have made this line in multiline 
----due to article format.
----package variables, which we are passing in SSIS Package.
set @params = '/set \Package.Variables[path].Value];"\"'+@path+'\""'

----now making "dtexec" SQL from dynamic values
--set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
set @ssisstr = 'dtexec /f "D:\jyothi\personal\excelsheet2\excelsheettype\Package1.dtsx" /ser '+@servername+' /connection "\"Data Source=YIITUSER1\\SQL1;IntegratedSecurity=True;
           Initial Catalog=excel;Provider=SQLNCLI.1;Persist Security Info=True;
           Auto Translate=False;\"" '
set @ssisstr = @ssisstr + @params
-----print line for verification 
print @ssisstr

----
----now execute dynamic SQL by using EXEC. 
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode
END

在SSIS程序包中，我正在控制流中执行dft任务，并在数据流环境中执行excelsource，dataconversion，oledb目标.
在这里，我使用了两个变量作为包变量，用于将路径动态传递到excelsource并提供工作表名称.

请帮助我

In SSIS package, I am taking dft task in control flow and excelsource,dataconversion,oledb destination in data flow environment.
Here I used two variables as package variables for dynamically passing path to excelsource and for giving sheet name.

Please help me

推荐答案

我认为您在调用程序包时无需指定连接字符串.它将从当前上下文中获取连接详细信息，或者应在包本身中指定.

如果要指定连接字符串，则还应指定名称. 此处 [

I think you do not need to specify the connection string while calling the package. It will take the connection details from the current context or it should be specified in the package itself.

If you want to specify a connection string, you should specify name as well. Here[^] is some help.

这篇关于如何使用SQLServer 2008从存储过程调用SSIS包?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！