Want to insert excel file data into table using ssis - format problem

Question

I have created an SSIS package i took 'excel source' taking excel file but my problem is I need to take B7 value from excel file and insert it into database table please help because

B7 values is generated from expression : =MAX($B$32:C284)

hi I want to take value of date i.e '8/26/2011' , and value of NAV i.e '93737452.52'

and want to insert into table i'm using 'excel source ' to take excel file made connection now Select 'Data access mode' in 'Excel source editor' as 'SQL command'

now what command I need to write to get 'date' and 'NAV'value as I have pasted picture of excel file please let me know the further steps plese .

Answer 1

Since you only need 2 very specific cell values from the spreadsheet, I would suggest using a Script Component to get the values from just those cells instead of using an Excel Source. Excel Source is really made for getting a bunch of rows & columns that are laid out consistently, not for picking individual cells.

To do this, first drag a Script Component from the Data Flow Transformations section of toolbox. When the select script component type dialog box appears, check the first option, "Source".

Next, edit this script component and pick Inputs & Outputs from the leftmost column and add the 2 output columns that you will need and set the correct data type for each.

Then click the Script option, choose your language (I picked Visual Basic 2008 in this case) and then click the Edit Script button.

When the IDE comes up, add a reference to Microsoft.Office.Interop.Excel and add an Imports statement for this at the top of your script. Then in the CreateNewOutputRows routine, get a reference to your workbook & worksheet and output the values from the specific cells that you want using the AddRow() method. As you can see from the code below, I am getting the values directly from cells b7 & c7 and converting them to the proper type.

Save your script and exit the IDE and you can now use this script component as your source for your database insert:

The script will read just the 2 values you want from the cells you specify and provide them to the Data Flow Destination.

While this may look like a lot more work than just dropping an Excel Source in the Data Flow, it gives you much more flexibility and control.