0

Good afternoon,

Im having some trouble writing a simple script using SMO objects in C# to validate if an object exists and then create it. This code is within a Script Task Component in SSIS. The code executes successfully, however the new database does not show up on my local instance. Any help would be greatly appreciated.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

        public void Main()
        {
            //String DBName = Dts.Variables["TmpViewDBName"].Value.ToString();
            //String Instance = Dts.Variables["TmpViewDBInstance"].Value.ToString();

            String DBName = "localhost";
            String Instance = "TmpViewDB";

            Server TmpViewServer = new Server(Instance);

            //Windows Auth
            TmpViewServer.ConnectionContext.LoginSecure = true;
            TmpViewServer.ConnectionContext.Connect();

            if (TmpViewServer.Databases[DBName] != null)
            {
                TmpViewServer.Databases[DBName].Drop();
            }

            Database TmpViewDB = new Database(TmpViewServer, DBName);

            if (TmpViewServer.ConnectionContext.IsOpen)

                    TmpViewServer.ConnectionContext.Disconnect();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
user1253174
  • 1
  • 1

1 Answers1

4

I believe you need to add a line to actually create the object. As it stands now, you've only instantiated the object but never actually made the call to the database to create the remote object.

    Database TmpViewDB = new Database(TmpViewServer, DBName);
    TmpViewDB.Create();
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Wow, I feel really stupid :) Sorry to waste your time (and my own for that matter). – user1253174 Mar 06 '12 at 21:12
  • 1
    No worries, keep the question out there in case anyone else runs into a similar issue. Ran into a similar scenario with creating an agent job via SMO last week so I know the feeling. And if you found this answer helpful click the Up arrow next to it. If it resolved your issue, click the silhouette of the green checkmark. Welcome to SO. – billinkc Mar 06 '12 at 21:28