Monday, April 9, 2012

Excel sql SSIS with variable file name

-- exec dbo.import_data 'C:\Documents and Settings\varunj\My Documents'
ALTER       procedure dbo.import_data
@FilePath varchar(2000)
--@filename  varchar(2000)
as
declare 
 @sql nvarchar(50),
        @Bank_code numeric(35),
        @Bank_transaction numeric(35),
 @Money_trn   numeric(35),
 @Check_num varchar(25),
  
 @FilePath varchar(2000),

 @dtsname varchar(1000),

 @cmd varchar(2000)


set @dtsname = 'Package.dtsx'

select @cmd = 'DTExec /F "' + @FilePath + @dtsname + '"'

print @cmd

set @sql = ''

--insert new records
insert into dbo.import_data2 (Bank_code,Bank_transaction, Money_trn, Check_num )
select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a left join 
dbo.import_data2 b 
on a.bank_code=b.bank_code
where b.bank_code is null

-- update existing records
update dbo.import_data2
set money_trn = x.Money_trn, Bank_code = x.Bank_code, Bank_transaction = x.Bank_transaction, Check_num = x.Check_num
from 
(select a.Bank_code,a.Bank_transaction, a.Money_trn, a.Check_num
from dbo.import_data1 a join 
dbo.import_data2 b 
on a.bank_code=b.bank_code) x
where import_data2.bank_code = x.bank_code

--clean up import_data1 (temp table)
truncate table dbo.import_data1

exec master..xp_cmdshell @cmd

GO


http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/310/passing-a-filepath-in-ssis

No comments: