2

Trying to put the final touches on a workflow I've been setting up and am running into a bit of a problem.

What I am hoping to do is as follows:

  • Have an Excel spreadsheet with multiple tabs that I give to an "end user"
  • Have one tab on which the end user inputs data and have another tab that has a bunch of values that are calculated based on the user input tab and some other lookups, etc.
  • Have an SSIS package that imports non-blank rows from the calculated tab into a table in SQL.

The way the spreadsheet is currently set up is I have an IF check in the fields to either set them to a value or set them to nothing ('').

The problem I have is that when I do the import into SQL, the rows with '' fields are imported as empty rows, presumably because the fields aren't actually blank (they have formulas in them).

Anyone know of a way to fix / work around this? I tried changing the formulas to set the fields to #N/A instead of '', but if I do that, the import fails.

Thanks!

Improve this question
2
  • 1
    You are trying to filter out the blanks in SSIS right? Can you share the specific approach in SSIS that you're using please. (e.g. SQL command on the Excel Source, conditional split, other)
    – mendosi
    Nov 9, 2016 at 0:03
  • You could try incorporating NA() into your calculations of the final output on the Excel side. I hear SSIS imports #N/As as NULLs, so maybe rows consisting only of those would be treated as blank rows as well.
    – Andriy M
    Nov 9, 2016 at 8:29

1 Answer 1

Reset to default
1

I would import the entire spreadsheet into an empty "temp" table, then run a sql statement to copy the rows I want across to my permanent table.

This way it is easy to setup rules so that you only get the rows you want and it is easy to test both the import and the copy jobs separately.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.