Hello, I need to pluck values from Excel 2007 cells. For instance want to have value present in M35 , I35 cells. In the Data flow task I am using the Script component with C# language. I am using the following code here using Excel = Microsoft.Office.Interop.Excel; public override void Input0_ProcessInputRow(Input0Buffer Row) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str; int rCnt = 0; int cCnt = 0; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open( "csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { str = ( string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2; //MessageBox.Show(str); } } xlWorkBook.Close( true, null, null); xlApp.Quit();  } I am getting the error in the first statement using Excel = Microsoft.Office.Interop.Excel; How should I access the excel files from SSIS 2008 Script component using C# Please help !! Best, Aniket

Don't do that. It will fail. Instead, use an Excel Connection Manager to identify the spreadsheet, and uncheck the "column names in first row" option. Then use an Excel Source component. Don't use "Table or View" access mode, use "SQL Command". Use the following SQL Command: SELECT F1 AS I35_value, F5 AS M35_value FROM [YourSheetName$I35:M35] You'll have to replace "YourSheetName" with the actual name of the tab... Talk to me now on

I am agree with Todd in all words, this link is a sample: http://www.rad.pasfu.com/index.php?/archives/26-Export-Data-Starting-From-Special-Cell-in-Excell-File-SSIS.htmlhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Thanks Todd. If I want to get the value from many Cells. SELECT F1 AS I40_value, F2 AS H35_value,F3 AS I35_val,F4 AS G38_val,F5 AS H38_val,F6 AS J56_val,F7 AS I38_val,F8 AS I9_val,F9 AS I11_val,F10 AS M9_val,F11 AS M11_val FROM [Lee Ranch$I40:H35:I35:G38:H38:J56:I38:I9:I11:M9:M11] I am getting the error. Is that possible Best, Aniket

No - I'm afraid you have to use an Excel-legal specification for a contiguous range. If you have multiple disjoint cells to retrieve values from, use several Excel Sources and Merge Join or Union All the results together. For example, to get some of your desired results, you could have two Excel Sources - one with this query: SELECT 1 AS join_key, F1 AS H35_value, F2 AS I35_value FROM [Lee Ranch$H35:I35] And the other with this query: SELECT 1 AS join_key, F1 AS G38_value, F2 AS H38_value, F3 AS I38_value FROM [Lee Ranch$G38:I38] Use a Sort component after each to sort on join_key, then Merge Join those together. Now you'll have a rowset with five cell values in it. Repeat with sources, sorts, and merges for your other contiguous ranges. Talk to me now on

Hi Todd, I did according to what you said. Now I have various 3 Merge joins going to one Union All. Here is how it looks at the output of UNION ALL 10 20 30 40 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 50 60 70 80 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 90 100 Now how do it purge them into a single row and insert as one row in the database Please help. Thanks, Aniket

Thank you VERY MUCH for that sample syntax. I'm saving it away right now... Talk to me now on

Hi, I was following this thread as I needed to use the technique in a project I was working on. We've found a way to do away with the need for join keys and sorts etc by using the following format of query in a single Excel Source Transformation (specified as an SQL command): SELECT [Sheet1$A1:A1].F1 AS Cell_One, [Sheet1$B4:B4].F1 AS Cell_Two, [Sheet1$C3:C3].F1 AS Cell_Three, [Sheet1$C7:C7].F1 AS Cell_Four FROM [Sheet1$A1:A1], [Sheet1$B4:B4], [Sheet1$C3:C3], [Sheet1$C7:C7] This pulls four values from various cells in a worksheet, you can also add in other worksheets into the FROM if required. Additionally you can add a range to each e.g. [Sheet1$A1:D10] if required. You can also use UNION ALL if, for example, you wanted to pull the first row and then all rows from the fourth row onwards: SELECT * FROM [Sheet1$A1:D1] UNION ALL SELECT * FROM [Sheet1$A4:D] Hope this helps. Pete.