USE SSIS TO ANALYSE YOUR BANK ACCOUNTS

                    LOADING MULTIPLE EXCELS WITH SSIS

      I worked most of time on   SAP BO/ETL technology. I love to solve real time problems with technology. I just started learning SSIS by watching some videos,referring Microsoft official blogs then a thought comes to my mind how can I use this tool to analyse my Bank Account data. I do small investments in share market /PPF/RD so I am sometime confused where my money is going..!!:)

This is what I did.
Downloaded all account statements year wise.

So here come solution how you can load multiple excels with same format into SQL SERVER. 

1)Create table in the SQL SERVER DB(try to capture all columns in bank statement)

Below is the excel which I downloaded form Bank

Now we are ready to go to SSIS. Open BIDS create new SSIS Project and package.

Logic: Load excels to table one after another and once loading of excel completed move excel to some other folder 


1)Create table with same number of columns

CREATE TABLE [dbo].[MH_SAVINGS_ACC](
[ValueDate] [date] NULL,
[TransactionDate] [date] NULL,
[ChequeNumber] [varchar](200) NULL,
[TransactionRemarks] [varchar](1000) NULL,
[WithdrawalAmount] [numeric](18, 2) NULL,
[DepositAmount] [numeric](18, 2) NULL,
[BalanceAmount] [numeric](18, 2) NULL
) ON [PRIMARY]


2)   Open BIDS create New project and package

3)Create variables as
InputFolder:C:\Users\Mahantesh\Desktop\Extra\ACC ANALYSYS\---This is where excel files are located
InputFullPath:C:\Users\Mahantesh\Desktop\Extra\ACC ANALYSYS\Test.xls-----Full path selecting sample .xls file
FileName:Tet.xls--Sample xls file
ArchiveFolder:C:\Users\Mahantesh\Desktop\Extra\Archive\--After loadig of the file move to this path
AchiveFullPath:C:\Users\Mahantesh\Desktop\Extra\Archive\Test.xls---After loadig of the file move to this path


4)Drop Foreach loop container to the control flow area set all parameters and the path of the files

5)Drag dataflow into foreachloop and inside dataflow just define the source and destination. 

Set parameters as bellow
OpenRowset:OpTransactionHistory$C13:I--Reason data present between these row and coulmns

$C13 means data starts from C13 (see excel image) "I" means complete I column 

Defining full paths
AchiveFullPath:@[User::ArchiveFolder]+@[User::FileName]

InputFullPath:@[User::InputFolder]+ @[User::FileName]

6)Set delay validation for excel connection and excel source true.

Note:Starting Filename is empty so for next loop we need to pic file don't for get to change 
expression of the Excelfilepath to @[User::InputFolder]+ @[User::FileName] 

7)The drag File system task and set source and destination as below.

For more detail please see the video. Happy learning..:)