Evaluate excel sheet names with spaces in foreach loop in SSIS

Question

I've created a foreach loop that loops through Excel files the sheets within them. Based on the name of the sheet, one of a number of data flow tasks is executed.

This is implemented by an execute SQL task that validates a variable against values in a table, and returns a value based on some conditions.

For some reason, it is working perfectly when the sheet name doesn't have any spaces (e.g. MyTab) however, when there is a space (e.g. My Tab), it does not match correctly.

I've tried evaluating the sheet name against the following values in the DB table.

My Tab$
'My Tab$'
'My Tab'$
{'My Tab$'}
[My Tab$]
['My Tab$']
"My Tab$"
My Tab
MyTab$
(My Tab$)
''My Tab$''
[''My Tab$'']
My$Tab$

However none of these will evaluate against the sheet name "My Tab"

The SQL I'm using to evaluate the variable is:

DECLARE @SheetName VARCHAR(100)
SET @SheetName = 'Tab1$'
IF EXISTS (SELECT 1
            FROM    [dbo].[xx]
            WHERE   sheetname = @SheetName)
-- The variable evaluates against the values in the sheetname column

    BEGIN
        SELECT 1 AS SheetExistsFlg
    END
    ELSE IF EXISTS (SELECT 1
            FROM    [dbo].[xx]
            WHERE   'Tab 2$' = @SheetName)
    BEGIN
        SELECT 2 AS SheetExistsFlg
    END
ELSE
BEGIN
SELECT 0 AS SheetExistsFlg
END

Any ideas?

Feel free to read the Tour page

Hadi
– Hadi

2019-03-31 11:56:47 +00:00
Commented Mar 31, 2019 at 11:56 — Hadi
– Hadi, Commented Mar 31, 2019 at 11:56

Edgar · Accepted Answer · 2019-04-02 06:33:32Z

1

Thanks very much for your suggestions.

I stripped the package back (to the loops without any import of excel data) and took Bogdan's suggestion of outputting the variable values into a table.

Interestingly the package was tripping up on the variables with spaces. I then stripped the dollar sign out, which didn't make any difference. So finally I stripped the single quotes out and this fixed the problem. Here is the transform I used:

(DT_WSTR,50)REPLACE(REPLACE(@[User::SheetName],"$",""),"'","")

answered Apr 2, 2019 at 6:33

Edgar

414 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yahfoufi Over a year ago

Well done! I think since you take Bogdan advice it is good to up-vote his answer. also you can up-vote others answers if they are helpful

user5208642 · Accepted Answer · 2019-03-31 13:05:38Z

0

Have you tried to output the variable that holds the sheets names to some table/file? So in this way you can see the outputed values.

Also, a nice approach would be to cleanse the sheet names after reading from the excel file, so when you set conditions you don't get dirty by dollar signs or quotes.

For example(just copy-pasted some C# code, hope it will help):

if (sheetName.EndsWith("$"))
    Output0Buffer.FriendlySheetName = sheetName.Remove(sheetName.Length - 1);
if ((sheetName.StartsWith("'")) && (sheetName.EndsWith("$'")))
    Output0Buffer.FriendlySheetName = sheetName.Substring(1, sheetName.Length - 3);

answered Mar 31, 2019 at 13:05

user5208642

Comments

Hadi · Accepted Answer · 2019-03-31 19:07:04Z

0

I think that you have a problem ordering IF...ELSE IF... ELSE statements. Try using the following syntax:

DECLARE @SheetName VARCHAR(100)
SET @SheetName = 'Tab1$'
IF EXISTS (SELECT 1
            FROM    [dbo].[xx]
            WHERE   sheetname = @SheetName)
-- The variable evaluates against the values in the sheetname column
        SELECT 1 AS SheetExistsFlg
    ELSE 
    BEGIN
        IF 'Tab 2$' = @SheetName
        SELECT 2 AS SheetExistsFlg
        ELSE
        SELECT 0 AS SheetExistsFlg
    END

Side note: sheet names ends with $ sign, then you don't have to add values such as [''My Tab$''], {'My Tab$'}. Make sure that the sheet name contains only one space not multiple spaces or Tab.

edited Mar 31, 2019 at 19:07

answered Mar 31, 2019 at 11:54

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Comments

userfl89 · Accepted Answer · 2019-04-01 15:54:58Z

0

Use the square brackets but without a $ or quotes in the sheet name. This would be [My Tab] in the examples posted above your code or [Tab 2] in the code.

DECLARE @SheetName VARCHAR(100)
SET @SheetName = 'Tab1$'
IF EXISTS (SELECT 1
            FROM    [dbo].[xx]
            WHERE   sheetname = @SheetName)
-- The variable evaluates against the values in the sheetname column

    BEGIN
        SELECT 1 AS SheetExistsFlg
    END
    ELSE IF EXISTS (SELECT 1
            FROM    [dbo].[xx]
            WHERE   '[Tab 2]' = @SheetName)
    BEGIN
        SELECT 2 AS SheetExistsFlg
    END
ELSE
BEGIN
SELECT 0 AS SheetExistsFlg
END

answered Apr 1, 2019 at 15:54

userfl89

4,8201 gold badge13 silver badges19 bronze badges

Collectives™ on Stack Overflow

Evaluate excel sheet names with spaces in foreach loop in SSIS

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related