[Urgent] Duplicate data when importing from excel (reads same worksheet name twice)

  • Adam Bean

    One Orange Chip

    Points: 26628

    Hey all,

    I'm attempting to setup a package that reads through multiple workbooks. Currently each workbook only has one worksheet; however the name of the worksheet changes with each book (usually the name of the workbook). Anyways, I followed this: http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package and basically what's happening is that I'm duplicating each worksheet. I put some break points in as well as leveraged the derived columns to determine that for each worksheet, I'm hitting the same name twice. For example "Sheet1" and "Sheet1$". I've seen this before when simply using the Import Wizard that it sees both names. Historically I've always used the "$" name; however I'm 1) unsure how to fix this problem and 2) why are there two names per sheet regardless?

    Any help would be most appreciated.

    Thanks

  • Adam Bean

    One Orange Chip

    Points: 26628

    Now what's even more odd is that I got through about 12 files (with the duplicate issue); however now I hit a file where it says the Sheet doesn't exist ... "Sheet1" for example - not the $ version.

    How do I tell SSIS to only pull the $ name of the sheet? I feel like I'm missing something very simple here.

    Thanks

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    There are a few ways to do this. Both are workarounds but should do what you need.

    1) Without using a script task.

    a. Place an empty sequence container in the inner for each loop container.

    b. Draw a precedence constraint on success.

    c. Edit the expression to read SUBSTRING(@SheetName,LEN(@SheetName)-1,1)==”$”

    2) With a script task in control flow

    a. Add a script task.

    b. Add a new variable “IsValidSheet” as a Boolean

    c. Pass @SheetName as read only and IsValidSheet as Read Write to the script

    d. Add code Dts.Variables(“IsValidSheet”).Value = Dts.Variables(“Sheetname”).Value.ToString().EndsWith(“$”)

    3) In the data flow

    a. Add a conditional split.

    b. Use the formula from 1c

    c. Ignore this case and just push through rows that fail this check (the default condition).

    2 is likely the easiest to understand.

    1 and 3 are basically the same trick in two different places.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply