Sharepoint online excel file

• PSB

  SSChampion

  Points: 13195

  More actions

  November 29, 2017 at 1:03 pm

  #330169

  Hi,

  1) I have an excel file that I have to read from and dump data into a sql server table (only the yellow highlighted portion) and get rid of the ones in blue.
  How do I get only the information I am looking for ?

  2) How to automatically read this file from sharepoint online using SSIS ?

  Thanks,
  PSB

• Martin Schoombee

  SSCoach

  Points: 19134

  More actions

  November 29, 2017 at 1:19 pm

  #1969974

  You should be able to filter or ignore any rows with no value in Col B in SSIS, or alternatively import all the data into a staging table and filter from there. 

  The answer to your second question: From what I know, you cannot do this natively in SSIS. You will probably have to use a script task and use the .NET object model for SharePoint, or execute a PowerShell command to copy the Excel file to a local/network location first.

• PSB

  SSChampion

  Points: 13195

  More actions

  November 29, 2017 at 1:28 pm

  #1969975

  I am receiving an error when I try to read the file as shown in attachment

• Martin Schoombee

  SSCoach

  Points: 19134

  More actions

  November 29, 2017 at 1:34 pm

  #1969977

  PSB - Wednesday, November 29, 2017 1:28 PM

  I am receiving an error when I try to read the file as shown in attachment

  You have some syntax issues in your SQL Command. I would recommend just using the "Table" option to extract all data from the worksheet. You can filter the data in a subsequent task.