I have a task to import excel data to Sql server. The excel data comes from excel templates. There are different excel templates available for different cleints so the columns in excel are not same accross all the templates. The data has to be imported to one common table of SQl.
I did googled and there is lot many samples available on the net, but i cannot identify which is the best method.
May i request you to please guide me on how to achieve the above considering dynamic columns of the templates.
In the excel template start row and end row are not fixed.
Data Flow Task, the primary task for moving data about within an SSIS package, is tightly bound to the metadata of the source provider. If one source has string, int, string and the next has string, string, string, string, the metadata is going to be too different for that work in a the same data flow. While you could coerce the integer data into a string, the difference in column count cannot be overcome.
In your case, unless you have terribly complex business logic to apply to the data coming from these spreadsheets into the consolidated table, I would probably look to design N+1 packages. A package for each possible Excel Format you will receive. I'm assuming there is some logic for determining which template a file was sourced from (ClientAV1.xlsx, ClientBV2.xlsx, etc). These packages would have a Data Flow that was Excel Source to OLE DB Destination. The final package would be a parent/controller/master package. It would handle the coordination of those other packages based on available files.
Since you've tagged asp.net, I'm assuming you were fine with a .NET approach as well. In this answer, I cover how you can read in Excel files in .NET and while I feed it to an SSIS data flow, there's nothing preventing you from issuing bulk commands to load data into SQL Server.
Dynamic column mapping in SSIS is quite tricky, the simplest method where you dont have to worry about column mapping is to use Openrowset option to import data from Excel file to sql file. You can use it as a command and add it in Execute SQL Task:
SELECT * INTO <TableName> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Filename.xls', 'SELECT * FROM [Sheet$]')