SQL Server Tacklebox- P14: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 3 The migratory data data from source to target can accommodate and on the speed of your network link. The two bulk transfer tools that we ll consider here are Bulk Copy Program BCP This tool has been around for nearly as long as SQL Server itself. DBAs have a hard time giving it up. It is a command line tool and if speed of data loading is your main criteria it is still hard to beat. There are several caveats to its use though which I will cover. SQL Server Integration Services SSIS I have found that SSIS is one of the best choices for moving data especially in terms of cost and in situations where near real-time data integration is not a requirement such as you may achieve with native replication or Change Data Capture technologies. Transforming data is also a chore that SSIS handles very well which is perfect for data warehousing. I will show how to use SSIS to load data from a source to destination and watch the data as it flows through the process. Whether you choose to use BCP or SSIS will depend on the exact nature of the request. Typically I will choose BCP if I receive a one-time request to move or copy a single large table with millions of records. BCP can output data based on a custom query so it is also good for dumping data to fulfill one-off requests for reports or for downstream analysis. SSIS adds a level of complexity to such ad-hoc requests because DBAs are then forced to design a solution graphically. In addition many old school DBAs simply prefer the command line comfort of BCP. I am not sure how many old school DBAs remain but as long as Microsoft continues to distribute I will continue to use it and write about it for its simple and fast interface. SSIS has come a long way from its forebear Data Transformation Services DTS and in comparison to BCP can be a bit daunting for the uninitiated DBA. However I turn to it often when requested to provide data migration solutions especially when I know there may be data transformations or .
