Call SSIS Package from SQL Stored Procedure

SET ANSI_NULLS ON

GO
SET
QUOTED_IDENTIFIER ONGO
CREATE
PROCEDURE [dbo].[GenerateIntPart](@Month as varchar(2)=NULL, @Year as varchar(4)=NULL)AS
BEGIN
SET NOCOUNT ON; DECLARE @ssis_cmd VARCHAR(4000)DECLARE @Packagepath VARCHAR(500)DECLARE @FileName VARCHAR(500)Declare @database as varchar(500) Declare @filepath as varchar(5000)Declare @sqlquery as varchar(max)Declare @oem as varchar(50)Declare @PackageType as char(1)--Exec GenerateIntPart '01','2012'select @filename=FilePath,@oem=code from somemasterselect @database=db_name(dbid) from master..sysprocesses where spid=@@SPID
set @PackageType='F'if @PackageType='F'begin
SET @Packagepath = 'D:\ImportCSV\ImporttCSV\bin\OvernightInt.dtsx' --Path of SSIS packageSELECT @ssis_cmd = 'dtexec /FILE "' + @Packagepath + '"'end
else begin
SET @Packagepath ='Import\NightData'SELECT @ssis_cmd = 'dtexec /SQL "' + @Packagepath + '"'end
set @FileName='' + @FileName + '\' + @Year+@month+ '\ParticipationSELECT @ssis_cmd = @ssis_cmd + ' /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::mFilePath].Properties[Value]";"' + @FileName + '"'SELECT @ssis_cmd = @ssis_cmd + ' /SET "\Package.Variables[User::mDatabase].Properties[Value]";"' + @database + '"'--SELECT @ssis_cmd = @ssis_cmd + ' /SER CONS-109A'print @ssis_cmd --EXEC master..xp_cmdshell @ssis_cmd
DECLARE @returncode intEXEC
@returncode = xp_cmdshell @ssis_cmdselect @returncode
END



For Importing a SSIS job into SQL -

http://msdn.microsoft.com/en-IN/library/ms141235(v=sql.105).aspx

You can import or export an Integration Services package from or to the following locations:

• You can import a package that is stored in an instance of Microsoft SQL Server, in the file system, or in the SSIS package store. The imported package is saved to SQL Server or to a folder in the SSIS package store.
• You can export a package that is stored in an instance of SQL Server, the file system, or the SSIS Package Store to a different storage format and location.

However, there are some restrictions on importing and exporting a package between different versions of SQL Server:

• On an instance of SQL Server 2008, you can import packages from an instance of SQL Server 2005, but you cannot export packages to an instance of SQL Server 2005.
• On an instance of SQL Server 2005, you cannot import packages from, or export packages to, an instance of SQL Server 2008.

The following procedures describe how to use SQL Server Management Studio to import or export a package.

To import a package by Using SQL Server Management Studio

1. Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box set the following options:
   
   • In the Server type box, select Integration Services.
   • In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
3. If Object Explorer is not open, on the View menu, click Object Explorer.
4. In Object Explorer, expand the Stored Packages folder.
5. Expand the subfolders to locate the folder into which you want to import a package.
6. Right-click the folder, click Import Package. and then do one of the following:
   
   • To import from an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
     Click the browse button (…), select the package to import, and then click OK.
   • To import from the file system, select the File system option.
     Click the browse button (…), select the package to import, and then click Open.
   • To import from the SSIS Package Store, select the SSIS Package Store option and specify the server.
     Click the browse button (…), select the package to import, and then click OK.
7. Optionally, update the package name.
8. To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
9. Click OK to complete the import.

To export a package by Using SQL Server Management Studio

1. Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, set the following options:
   
   • In the Server type box, select Integration Services.
   • In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
3. If Object Explorer is not open, on the View menu, click Object Explorer.
4. In Object Explorer, expand the Stored Packages folder.
5. Expand the subfolders to locate the package you want to export.
6. Right-click the package, click Export, and then do one of the following:
   
   • To export to an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
     Click the browse button (…), and expand the SSIS Packages folder to locate the folder to which you want to save the package. Optionally, update the default name of the package, and then click OK.
   • To export to the file system, select the File System option.
     Click the browse button (…) to locate the folder to which you want to export the package, type the name of the package file, and then click Save.
   • To export to the SSIS package store, select the SSIS Package Store option, and specify the server.
     Click the browse button (…), expand the SSIS Packages folder, and select the folder to which you want to save the package. Optionally, enter a new name for the package in the Package Name text box. Click OK.
7. To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
8. Click OK to complete the export.