Read and Export Excel data from nth Row in SSIS

This post shows how we can read and export data from excel starting from nth row.

In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 2 to Row 15.

To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components. 

Right Click on the Excel Source and select properties.

In the OpenRowset you will find the sheet name from which you are extracting the data. You have to make modification here to read the data from the nth row or range.

As you can see from the below Image I have already updated the OpenRowset value to

Sheet1$A2:A15

Now double click on the excel source and click on preview button, as you can see from the below Image data was read from the row 2 to row 15.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Check IsNumeric() with Derived Column Transform in SSIS

In SSIS we don’t have ISNUMERIC () equivalent within the SSIS expression language. You can't use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn't mean you can't check to see if a field is numeric using the SSIS expression language. Using script task we can check this, using SQL command in Excel ( SELECT F1 FROM [SHEET1$] WHERE ISNUMERIC(F1)=-1) source we can check this, but in this tutorial I will show how we can check if a field is numeric without script task.

Create a sample excel file as shown below

As you can see from the above data our column has both numeric and string data. Now I want to check which row is numeric.

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric.

Steps:

1-      Create Excel source connection

2-      Drag a Derived Column

After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name to the new column and use this expression:

(DT_I4)Column == (DT_I4) Column? 1 : 0

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to ignore failure on Error, as seen here:

  

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

3-      Add another derived column and add data viewer on it.

Package should look like this.

On Execution you should get the below output.

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field is numeric and which are not numeric by checking for NULL.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.