May 10, 2015 at 11:49 pm
I need to create a package which is going to be used to import data from Excel to SQL Server DB, periodically.
The excel files from which these the data is coming can have different number of columns.
Lets say we need to upload the excels every week. One week there can be data in 5 columns but the next week the data can be till 15th column.
How should I approach this situation and design my package so that it can work every week without any manual change?
How can I get the number of column in excel so i can compare that count with my physciacl table column.
regards,
Vipin jha
May 19, 2015 at 12:49 pm
If you can always get 15 columns of data no matter what you can use a SQL Command Like This in your excel Source Where Member Detail is the sheet name, A5 is the row and column to start in, and P is the column to end with. I don't know if this will help for what you want to do so maybe I will try it out tomorrow.
SELECT *
FROM [Member Detail$A5:P]
May 19, 2015 at 1:17 pm
vipin_jha123 (5/10/2015)
I need to create a package which is going to be used to import data from Excel to SQL Server DB, periodically.The excel files from which these the data is coming can have different number of columns.
Lets say we need to upload the excels every week. One week there can be data in 5 columns but the next week the data can be till 15th column.
How should I approach this situation and design my package so that it can work every week without any manual change?
How can I get the number of column in excel so i can compare that count with my physciacl table column.
regards,
Vipin jha
Found it easiest to dynamically build packages for this kind of imports, the master package reads the structure of the file, creates a destination table and then creates a package that does the transfer & transformation.
😎
May 19, 2015 at 1:29 pm
Here is an example of something that has worked previously for me.
https://dwbi1.wordpress.com/2011/03/05/ssis-importing-a-file-with-dynamic-columns/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply