Hi, I have an already developed SSIS 2005 package which can import maximum of 50 columns of *.xlsx file. The file format is same except with the column numbers. I have many components in SSIS package (e.g. unpivot data, derrived column etc) which are developed for 50 columns. The package works fine for importing 50 columns. But sometimes it fails for excel sheet having 12 columns (columns < 50) it gives error for the source columns cannot be found. Surprisingly, if the contents are copied to another file it may work but not surely. Please let me know if I can do anything in existing package. Thanks in advance,

SSIS doesn't support dynamic meta data this means that if your data structure is different you need another source/transformation/destination with mappings. you can not use same data flow with same mappings when data structure isn't same. but there is another work around for you , you can use OPENROWSET to fetch data and insert it to destination like this: SELECT * INTO XLImport8 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "'SELECT * FROM [Customers$]') this is for more information: http://support.microsoft.com/kb/321686 http://www.rad.pasfu.com

this is for more information: http://support.microsoft.com/kb/321686 Reza, above link is really helpful. XAYBZC, you can also use Script task for the same. Please check the similar thread here.. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Thanks for the reply. Let me try to explain my issue : 1. Existing package runs fine for excel having 10 columns (and I tried with lesser columns, it worked). All other components (e.g. Unpivot, pivot) are mapped 1:1 with the column structure and no dynamicity is maintained. 2. I have to update this package to incorprate max 50 columns from excel. 3. All the time file will not have perfect 50 columns. 4. I have very less time to incorporate the fix. If I scale the application to read 12 columns and try with 11 column excel it gives me metadata error. But if I scale the original application (working for 10 columns) to read 11 columns and try to read excel with 4 columns. It reads successfully. 5. I put a data watcher after OLEDB source to see the imported result for 4 columns. I found that 4 columns have the respective data from excel, columns 5-10 (of data watcher) has int numbers 5-10. And surprisinggly 11th column (newly aaded) has a null. As all other columns have numerals why 11th column doesnt show anything? I cross checked all the properties for OLEDB and all input - output columns. I am not finding any discrepancies. This is a strange behaviour, though. I want a solution that gets quickly implemented without changing all other 5-6 components, can OpenRowSet does it...? Script task will enforce me to use script for whole of the logic of unpivoting and pivoting, I am trying to avoid that. Thanks again for your reply.

when you have transformations like PIVOT and UNPIVOT, you can not use OPENROWSET, OPENROWSET is useful when you want to import data from excel to sql server directly without any changes in data. But I think ETL suggestion is your only way, use script task and create source columns by script. Note that you don't need to implement other transformations like pivot and unpivot in the script. just use script task as source and create source columns there. you can simply fetch every columns you need, just you need to have output columns structure to be same as columns structure which you used in your current data flow. does it make sense to you or not?http://www.rad.pasfu.com

Reza This script works fine on 32 bit system , i doubt it will on 64 bit systemsBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/