1

I am new to SSIS. I have a SSIS package that imports a table from a different SQL Server to mine. I created the package using the import wizard, but I understand how to open and edit using VisualStudio. The basic package drops the original table first, creates the new table then imports the data. I do this daily.

My problem is sometimes the other database isn't available. I don't know there is a problem until after the table drop. I need to know if there is a simple (enough for me) way to check the other table availability before I drop my current copy. That way I keep my most recent data if the new data isn't available.

I thought I could import to a temp table, then check it's row count and if 0 stop there. Or somehow check the other server first before dropping, but I don't know how to do either. Grateful for some pointers.

Edit: Need help figuring out how to do it in SSIS as well as how to do it in general.

shorton
  • 323
  • 3
  • 13
  • Possible duplicate of [check if database exists AND current login can access](https://stackoverflow.com/questions/19553515/check-if-database-exists-and-current-login-can-access) – Tanner Oct 18 '17 at 14:32
  • 1
    read this: https://learn.microsoft.com/en-us/sql/t-sql/functions/has-dbaccess-transact-sql you can add that in ssis, take the value it returns and then proceed if it is online else fail safely. – Tanner Oct 18 '17 at 15:01
  • Thanks Tanner. It's the detail on this part I'm fuzzy about: "take the value it returns and then proceed if it is online else fail safely". I gather from Kyle's reply if it fails (returns 1?) then the package will stop there. – shorton Oct 18 '17 at 15:41

1 Answers1

3

This is easy enough to accomplish. You have to check for other database tables before dropping is a simple command and here that is:

Pulled the query from here

Permanent Table

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL DROP TABLE dbo.Scores;

Temporary Table

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL DROP TABLE #T;

That will allow you to delete if the other exists.

Kyle Pearson
  • 107
  • 8
  • Thanks. That helps, but 2 follow-up's: 1) How to I format the database name to the other server? and 2) how do I build this in the SSIS package? Not sure how to do an If in the SSIS package and not go to the next step. I do know how to add a SQL statement and tie it to the next task. – shorton Oct 18 '17 at 14:55
  • @shorton did you check the link I sent you on the question? – Tanner Oct 18 '17 at 14:58
  • Right now I have a "Execute SQL Task" where the table is dropped, then a "Preparation SQL Task" where the new table is created, then the "data Flow Task" that does the import. In the SSIS Toolbox I don't know what to choose to allow a branch to stop. – shorton Oct 18 '17 at 14:59
  • I would suggest creating a stored procedure for this call. In that stored procedure you will have to create a linked server to the other database. How to accomplish this is to leverage the script component tool. You will execute the statement and proceed forward. Does that cover enough? I am happy to provide more details! – Kyle Pearson Oct 18 '17 at 14:59
  • @tanner, yes, I did. If the answer is there I didn't get it, sorry. – shorton Oct 18 '17 at 15:00
  • @KylePearson Is there no way without a linked server? Figured surely there was a way to put an "if source does not exists then stop" in SSIS flow. No? – shorton Oct 18 '17 at 15:03
  • @shorton you could do a simple query or have it validate metadata for that table and if it doesn't exist it will fail. You could also write that statement in the ssis package and have a data source for the server. That way no linked server is needed. We just enforce using stored procs when we can, – Kyle Pearson Oct 18 '17 at 15:09
  • @shorton also if this works please mark this as an answer. – Kyle Pearson Oct 18 '17 at 16:28
  • I did as you suggested. I just set up a "Execute SQL Task" with a simple select first column, first row query. If it failed the next step didn't appear to execute. That accomplishes what I need. Thanks. – shorton Oct 18 '17 at 16:59
  • Thank you both for the help. – shorton Oct 18 '17 at 17:00
  • 1
    Changed query to select HAS_ACCESS(... per Tanner's suggestion. Works, too. Seems to make more sense for what it's doing. Thanks again. – shorton Oct 18 '17 at 18:01