Task 17: SSIS with variable excel connection manager

1. In order to deploy an Excel Source with .xlsx extension you need to install the following provider:
http://www.microsoft.com/en-us/download/details.aspx?id=13255

2. Create a new SSIS project (Integration Services Project1)

3. Create a new package (LoadCostChanges.dtsx)

4. Declare 3 variables: UserID, FilePath and VendorUploadID (the variables will be sent as parameters from a stored procedure to the package)



5. Add from toolbox a DataFlow component (Import the Excel File) and an Execute SQL Task.

6. Because the excel file can have multiple empty rows we'll need to delete them using an Execute SQL Task (but this will be the last step for the implementation)

7.  In Data Flow component choose an Excel Source, a Derived Column and an OLE DB Destination component from Toolbox.

8. Excel Source configuration:

9. Excel Connection Manger configuration

10.  Derived Column configuration

11. OLE DB Destination configuration

12. Excel Connection Manager configuration
Connection String:  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ @[User::FilePath] + ";Extended Properties=\"EXCEL 12.0;HDR=YES\";"

13. The dbo.LoadExcel must be created with all the columns from all the excel files if there are columns which can be mapped in more than one Excel Source. 

CREATE TABLE [dbo].[LoadExcel](

[LoadExcelID] [bigint] IDENTITY(1,1) NOT NULL,

[VendorUploadID] [int] NULL,

        ............................................

        [UserID] [uniqueidentifier] NULL,

 CONSTRAINT [PK_LoadExcel] PRIMARY KEY CLUSTERED 

(

[LoadExcelID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

14. You need to connect to your integration server and deploy all your packages in SQL Server:

15. The Stored procedure which call the packages and sent them parameters : 

USE [TFMVendorPortal]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-----------------------------------------------------------------

--settings before running for the first time xp_cmdshell command

-----------------------------------------------------------------

/*

sp_configure 'Show Advanced Options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

EXEC sp_configure 'show advanced options', 1 

GO

RECONFIGURE WITH OVERRIDE 

GO

EXEC sp_configure 'xp_cmdshell', 1 

GO

RECONFIGURE WITH OVERRIDE 

GO

EXEC sp_configure 'show advanced options', 0 

GO

*/

ALTER PROCEDURE [dbo].[tfm_RunSSISPackages] 

@VendorUploadId int  ,@FilePath varchar(500), @UserID UNIQUEIDENTIFIER

AS

BEGIN

DECLARE @SQL NVARCHAR(max)

 ----TEST SCRIPT 

--DECLARE @SQL NVARCHAR(MAX)

--declare @FilePath nvarchar(max)

--declare @VendorUploadID INT

--SET @VendorUploadId = 233

--set @FilePath = '\\192.168.1.3\tfm\7\vernon_CostChanges_2012-10-15_17-13-33-718.xlsx'

set @SQL = '

EXEC master..xp_cmdshell ''""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\File System\LoadCostChanges" /SERVER "SFVM05" /set \package.variables[User::VendorUploadID].Value;"'+ cast(@VendorUploadID as nvarchar(max))+ '" /set \package.variables[User::FilePath].Value;"'+ @FilePath + '""'''

exec (@SQL)

END