Unanswered: Script Task Using SMO
I have an SSIS package that copies over database objects to another database. There is one Foreach loop that copies over a group of tables that will not have the data included. Another Foreach loop copies over the tables that will have the data. Both of these do not copy over the foreign keys.
I have a third Foreach loop that is to copy over the foreign keys of all tables. When I run the script, it runs for about five minutes (which leads me to believe it's working). It runs successfully without any errors, but when I look at the tables, there are still no foreign keys. Here is the script I'm using to copy JUST the foreign keys:
Is there something that I am missing?
Public Sub Main()
Dim dbSourceName As String = "ERIIProduction"
'Dim dbDestName As String = dbSourceName + "ORANGE_SSIS_TEST"
Dim dbDestName As String = "ORANGE_SSIS_TEST"
'Dim dbDestName As String = "ORANGE_SSIS_TEST"
'Connect to the local, default instance of SQL Server.
Dim srv As Server
'Dim srvConn As ServerConnection
'srv = New Server("MCDEVSQL002")
'srvConn = srv.ConnectionContext
'srvConn.LoginSecure = True
srv = New Server
'Reference the source database
Dim db As Database
db = srv.Databases("ERIIProduction")
''Create a new database that is to be destination database.
'Dim dbCopy As Database
'dbCopy = New Database(srv, "ORANGE_SSIS_TEST")
'Define a Transfer object and set the required options.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopySchema = True
xfr.CopyAllTables = False
xfr.CopyAllObjects = False
'xfr.CopyData = True
xfr.DropDestinationObjectsFirst = False
'xfr.Options.WithDependencies = False
xfr.Options.ContinueScriptingOnError = True
'xfr.Options.DriDefaults = True
'xfr.Options.DriIndexes = True
'xfr.Options.DriNonClustered = True
'xfr.Options.DriPrimaryKey = True
'xfr.Options.DriUniqueKeys = True
xfr.Options.DriWithNoCheck = True
xfr.Options.DriForeignKeys = True
'xfr.Options.Triggers = True
'xfr.Options.ClusteredIndexes = True
'xfr.Options.NonClusteredIndexes = True
'xfr.Options.Default = True
xfr.Options.PrimaryObject = False
'Establish Database Connection
xfr.DestinationDatabase = dbDestName
xfr.DestinationServer = "MCDEVSQL002"
xfr.DestinationLoginSecure = True
'Get Table Name
'Execute the transfer
Dts.TaskResult = Dts.Results.Success
Any help would be appreciated!
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."