Post reply

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

  • August 10, 2012 at 6:03 am

    #265809

    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

  • August 10, 2012 at 7:12 am

    #1524410

    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

  • August 10, 2012 at 7:21 am

    #1524418

    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 2 (of 2 total)

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