Migrate SQL Server Database to AWS (RDS) or Azure using SSIS
If backup and restore is not an option to migrate or copy the whole database which is not an option, specially to migrate managed database services like AWS RDS /Azure or any Environment which has restrictions to restore backups needs Import/Export mechanisms to migrate the data.
Both these techniques are very familiar to most of the DBAs, here I am highlighting the problems
1. Identity Insert should be ON for all the tables which has identity key property, Its very tedious manual process to identify which tables has this property and manually enable them in each data flow.
2. Keep Nulls should be on the tables with default values. E.g.: 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 have to be done manually. Without these the package would fail or perform slowly
How it works:
Note: This has been tested on SQL Server 2008 EE. If this doesn’t work in your environment, then modify the key strings as per the release.
1. Run the Export export data wizard and save the package 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
Check the below link which is using BCP to migrate the Data.