ssis sql statement to determin if table exists inside an OLE DB Source statement

I Have a SSIS package that uses a for next loop and inside that I have a OLE DB Source that reads an excel workbook and then performs some code after that.
I need to see if there is a way to be able to read the source, and if a sheet exists, then perform this code. If it doesn't exist then do this code.

I added the following sql statement in the OLE DB, but it doesn't like it:
IF EXISTS (Select 1 from Information_Schema.Tables where Table_Name = 'TableB')
Select * from TableB
Else
Select * from TableA

I get the error below. Is the sql statement wrong, or is there a better solution?


TITLE: Microsoft Visual Studio
------------------------------

Error at Labor Version 57 & up [OLE DB Source [2470]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft Office Access Database Engine"  Hresult: 0x80040E14  Description: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
BKennedy2008Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<couple of wild guesses, sorry that's all I have>

>Source: "Microsoft Office Access Database Engine"
If your source is an Access database then the sql has to be Access SQL, and Access does not have an IF construct that can branch different SQL statements.

>Select * from TableB
>Select * from TableA
You'll want to make sure that TableA and TableB have the same schema, as SSIS does not handle well if a source can have different schema, and the resulting re-mapping required.
0
BKennedy2008Author Commented:
This would produce 2 table results. I only want 1 table's results.

Scenario:
 I have updated a workbook to import from another sheet inside the workbook, but I do not want it to fail if anyone is using an older version of the workbook. On the new excel workbook, both sheets will exist, but on th old one, only 1 sheet exists. I want to import the new sheet, if it exists, and if it doesn't, then I want to import the older sheet (What it is doing currently) so the old sheet, if used, does not fail

 Yes it is using the microsoft access database engine 12.0 to connect up, and the datasource is an excel workbook.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to import the new sheet, if it exists, and if it doesn't, then I want to import the older sheet
Perhaps a better approach..
*  Create a variable to hold the row count for the first source
*  Import both sheets as sources
*  Immediately after the first source add a row count control, saving the row count to the variable.
*  Then in the arrow going from the row count, add a 'Expression and Constraint' that the flow proceeds ONLY if the variable is greater than zero.   (  @the_variable > 0 )
*  In the arrow going from the second source, add a 'Expression and Constraint' that the flow proceeds ONLY if the variable equals zero. (  @the_variable == 0 )
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BKennedy2008Author Commented:
*  Import both sheets as sources
If the sheet does not exist, in this case, won't the package fail when it tries to import the sheet that does not exist?

I was thinking if the package fails, then try the second for next loop with the new datasource of the second sheet?
0
BKennedy2008Author Commented:
The problem I wil run into, is on the new sheets, the data will exist in both places. (Because the 1st sheet does caculations for the rest of the workbook) and my new sheet pulls data from that sheet)

I thought access may have something with a if then, but I guess I will have to reapproach the design of getting data into the SQL Server from excel.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>If the sheet does not exist, in this case, won't the package fail when it tries to import the sheet that does not exist?
Correct.  Either at compile time if Delay Validation is set to False, or runtime when it attempts to load the data if Delay Validation is set to True.

Perhaps split this up into two different data flows, with the same variables - expression and constraints, such that the second one is never loaded if the first one contains data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>If the sheet does not exist, in this case, won't the package fail when it tries to import the sheet that does not exist?
Guessing this would have to be done in a script task.  Since I don't have the answer of how to do that, I've added the Excel zone to this question, and will step back to encourage Excel experts to respond.
0
BKennedy2008Author Commented:
for right now, I copied the for next loop, took out items when it fails, and added a second for next loop to run if the 1st one fails. The 1st one will use the new source sheet, and the second one will use the old source sheet.

I just need to run it for about 2 weeks before everyone can use the new workbook.
I will see if this will work.
0
BKennedy2008Author Commented:
I have it working this way-

For/ next loop (Datasource is the new sheet)
Failure goes to
For/Next loop #2 (Datasource is old sheet)

The issue I have is it will return error always if the source is the old, even though it is success for the For/Next loop#2

Question: Is there a way to tell the package to ignore a specific Error? (I get Error 0xC02020FE No Column information was returned by the SQL Command.

If the source is new, and it errors on the For/Next loop, I need a way to determine that it errored out. Right now, the package returns that it was a failure, even though the for/next loop #2 was a success and the data imported.
0
BKennedy2008Author Commented:
I have it sending out an email through error handling. I should be good to go. Thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.