다음을 통해 공유

Database Migration or Copy Database through BCP/SSIS

  • 아티클

Most of the DBAs prefer backup and restore to migrate the databases which is pretty straight forward.
But , some of the migrations. especially to AWS RDS /Azure / Any Environment which has restrictions to restore backups needs Import/Export mechanisms to migrate the data.

The top 2 ways to migrate data is through either SSIS(Export Wizard) or BCP. choosing the one from these two depends on the environment.
If the environment has no direct connection to destination database , then we choose BCP else we can go with SSIS.

This article highlights and resolve the most common problems with Export Wizard .

**Export Wizard : **
Export Wizard has an option to save an SSIS package without executing. which is pretty good feature , but it has some limitations which needs manual intervention.

  1. Identity key insert should be on the tables with identity keys
  2. Keep Nulls should be on the tables with default values. Eg:. if the table , column which allows nulls and has a date data type with default value as getdate()  then after exporting the data it inserts the date values instead nulls , unless the SSIS dataflow destination property option FastLoadKeepNulls is checked
  3. fast load options are not set by default , it has to be set manually for the property sFastLoadOptionsBatchCommit 
  4. TABLOCK which is not enable by default
  5. ROWS_PER_BATCH is not configured by default

Above 5 settings has to be done manually.  with out these the package would fail
Think about a situation where you need to export 500 tables.

Below VbScript will create a new SSIS package with all the above options. 
Modify the vbscript to configure no.of rows and default batch size as per your requirement.

Steps :

  1.  save the SSIS package from export data wizard to C:\TEMP\northwind.dtsx
  2. copy the below vbscript code to a file in C:\TEMP\SetSSIS.VBS 
  3. Edit the Vbscript , Line 5 "filePath = "<ssis file path>"
  4. run the vb script
  5. it creates a new package with new name as <PKG_NAME>.NEW
  6. open the package in BIDS
  7. run the package

 

 Const ForReading=1
Const ForWriting=2
Set objFSO = CreateObject("Scripting.FileSystemObject")

folder = ".\"
filePath = folder & "QAHAFCI_HA_QA.dtsx"


dim NewrowsPerBatchString
NewrowsPerBatchString = "TABLOCK,ROWS_PER_BATCH = 50000</property>"

Set myFile = objFSO.OpenTextFile(filePath, ForReading, True)
Set myTemp= objFSO.OpenTextFile(filePath & ".new.dtsx", ForWriting, True)

dim sIdentity 

sIdentity =   "name=""FastLoadKeepIdentity"" dataType=""System.Boolean"" state=""default"" isArray=""false"" description=""Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on."" typeConverter="""" UITypeEditor="""" containsID=""false"" expressionType=""None"">false</property>"


sKeepNulls = "name=""FastLoadKeepNulls"""



sFastLoadOptionsBatchCommit = "name=""FastLoadMaxInsertCommitSize"" dataType=""System.Int32"" state=""default"" isArray=""false"" description=""Specifies when commits are issued during data insertion.  A value of 0 specifies that one commit will be issued at the end of data insertion.  Applies only if fast load is turned on."" typeConverter="""" UITypeEditor="""" containsID=""false"" expressionType=""None"">0</property>"

sFastLoadOptions = "name=""FastLoadOptions"" dataType=""System.String"" state=""default"" isArray=""false"" description=""Specifies options to be used with fast load.  Applies only if fast load is turned on."" typeConverter="""" UITypeEditor="""" containsID=""false"" expressionType=""None"">"

dim x


Do While Not myFile.AtEndofStream
 myLine = myFile.ReadLine


 If InStr(myLine, sIdentity) Then

          myLine=Replace(myLine,"expressionType=""None"">false</property>", "expressionType=""None"">true</property>")

 End If

 If InStr(myLine, sKeepNulls) Then

          myLine=Replace(myLine,"expressionType=""None"">false</property>", "expressionType=""None"">true</property>")
                              
 End If



 If InStr(myLine, sFastLoadOptionsBatchCommit) Then

          myLine=Replace(myLine,"expressionType=""None"">0</property>","expressionType=""None"">12345</property>")
                         
 End If


 If InStr(myLine, sFastLoadOptions) Then


          myLine=Replace(myLine,"expressionType=""None"">","expressionType=""None"">"&NewrowsPerBatchString)
   
 x=instrrev(myLine,NewrowsPerBatchString )
          myLine=left(myLine,x+(len(NewrowsPerBatchString)-1))
       
       



                              
 End If




 myTemp.WriteLine myLine
x=""
Loop


myFile.Close
myTemp.Close
'objFSO.DeleteFile(filePath)
'objFSO.MoveFile filePath&".tmp", filePath