Please tell me if I can create SSIS package for the below requirement: I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table. The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table. I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table. I have given a sample data structure below: Source Data Excel1_Order.xls OrderNumber OrderQuantity OrderDate Order10001 100 01-01-2011 Excel2_Customer.xls CustomerNumber CustomerName CustomerAddress Customer0001 CCPrivateLtd India ReferenceTable Category DestinationColumn SourceColumn Order Disp_Col_1 OrderNumber Order Disp_Col_2 OrderQuantity Order Disp_Col_3 OrderDate Customer Disp_Col_1 CustomerNumber Customer Disp_Col_2 CustomerName Customer Disp_Col_3 CustomerAddress DestinationTable Category Disp_Col_1 Disp_Col_2 Disp_Col_3 Created_Date Order Order10001 100 01-01-2011 getdate() Customer Customer0001 CCPrivateLtd India getdate() Is there any way to handle this in SSIS? Thanks in advance.

Check for Pivot transformation here is sample http://www.proteanit.com/b/2007/08/02/ssis-pivot-component/ http://uk.linkedin.com/in/ramjaddu

Check for Pivot transformation here is sample http://www.proteanit.com/b/2007/08/02/ssis-pivot-component/ http://uk.linkedin.com/in/ramjaddu I dont want to convert rows to columns. i want use Reference Table to decide the column in Destination table for some source column value.

Hi, After analyzing your requirements. I got this idea to implement the solution. In this scenario we don;t need reference table. because, we can save the source and destination column mapping in the package itself. Solution: 1. Create a package and Add two data flow task (DFT) 2. DFT 1 : add exce source, derived column task and map the fields to destination 3. DFT 2: add exce source, derived column task and map the fields to destination 4. save and run Thanks Ayyappan Thangaraj UG Lead, Puducherry, http://SQLServerRider.blogspot.com

Even, I think you don't have to create a Reference Table. You can have two dataflow tasks, once the data flow is done through first source to destination, you can load the second source into the same destination using second data flow task(Like the above reply) Or you can use a Merge transformation, to merge both the sources. Chiran

Yes this is what I am doing tmporarily. But my actual requirement is the column mapping is dynamic and should be taken from reference table. There may be additional columns added in future or column order might be swapped... Is there any way i can handle this??

As there is no dynamic column mapping in ssis data flow , or I can say in this way: SSIS Data flow task doesn't support dynamic metadata, you can do it in these ways: 1- create separate source for each different structure excel file, and use them in different data path to fill destination as you need. 2- use other options like OpenRowSet to import data from excel into sql server, using OpenRowSet you don't to need to worry about column mappings, you just need to create dynamic sql command and run it with execute sql task. this is sample of OpenRowSet command to import data from excel file : SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')http://www.rad.pasfu.com

Hi BacktoWork, Dynamic colum mapping might be possible with SSIS api programming. check http://msdn.microsoft.com/en-us/library/ms136086(v=SQL.90).aspx. I tried this but in vain to dynamically map the mappings in data flow task. On the other hand there is a way to generate Insert scripts based on the columns ( for which the number might change). I was able to achieve this successfully. For excel you could use whatever mentioend by Reza. Check http://deepaksqlmsbusinessintelligence.blogspot.com/2011/05/how-to-load-data-from-multiple-excel.html if you want the code for generation of insert scripts into the destination based on the columns generated dynamically from the source. Happy to help! Thanks. Regards and good Wishes, Deepak.

Source Data Excel1_Order.xls OrderNumber OrderQuantity OrderDate Order10001 100 01-01-2011 Excel2_Customer.xls CustomerNumber CustomerName CustomerAddress Customer0001 CCPrivateLtd India ReferenceTable Category DestinationColumn SourceColumn Order Disp_Col_1 OrderNumber Order Disp_Col_2 OrderQuantity Order Disp_Col_3 OrderDate Customer Disp_Col_1 CustomerNumber Customer Disp_Col_2 CustomerName Customer Disp_Col_3 CustomerAddress DestinationTable Category Disp_Col_1 Disp_Col_2 Disp_Col_3 Created_Date Order Order10001 100 01-01-2011 getdate() Customer Customer0001 CCPrivateLtd India getdate() Is there any way to handle this in SSIS? Thanks in advance. If the column (fields) names are fixed and wont change AND the location of the column wont change YES you can use DFT , but you will need to read the file 4 times , please see http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa You will see 2 question and answers in this link you need to use the first one Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

If you can use third-party solutions, I would recommend you check the commercial CozyRoc Data Flow Task Plus. It is designed to solve problems exactly like the one you are describing. It can setup dynamic data flows at runtime and you can specify mapping (reference) table , which guides the task how to connect source to destination columns. The solution doesn't require programming skills and it is already used in production.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/