Transferring a Database Using the SSIS Designer - Troubleshooting
(Page 7 of 7 )
After encountering the same error message, attempt was made to verify whether the IDE did what it is supposed to do. The first thing checked was to verify if any permission was violated. Finally it was decided to look at the Database files for the source and the destination.
Source
"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA","";
"TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""
Destination
"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA","";
"TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""
Do you see the problem with this automated wizard? It is trying to copy from SQL 2005 to itself, although it was explicitly told to copy it to the SQL Express. To remedy the situation, change the destination file string to the following:
"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQL
DATA","";
"TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQL
DATA",""
Now click OK to the Editor screen and execute the package one more time. The progress now shows the following displayed.
The output of the debug window shows that the package exited with code 0(0x0), a success.
Final check on SQL Express
If you now go back to SQL Express Server and expand the databases node, you should find the TestBase database. This is indeed true as shown here. The TestBase database has been copied over to the SQL Express Server.
Summary
The package design using the SSIS Designer for transferring a database is very easy. The automatic configuring of the destination created a problem which was rectified by associating the proper destination file with the proper server. It was interesting to note that it took over 12 seconds to transfer a near empty database (total data + log = 5 MB) with just two user tables with both servers on the same machine. The error message produced when the task failed was not very helpful.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 8 
