SSWUGtv
Our host, Stephen Wynkoop, interviews Don Boxley of DH2i on today’s show. Together they explore the challenges of managing your systems, local/private, on the cloud – or wherever! As the cloud grows, or our systems become more distributed, system mangement becomes more complicated.
Watch the Show
SQL Server ETL History
SQL Server has had a number of tools for importing and exporting data over the years. Bulk Copy, in the form of
BCP.EXE was a tool available from the very first version of Microsoft SQL Server, and continues to this day. It could be used to import data from files into SQL Server. It also was used to Extract data from SQL Server into files.
ADO was enhanced to include BCP capabilities allowing it to be extended into custom applications. It also enabled the ability to execute BCP from data stored in memory rather than strictly from files.
Microsoft also released a couple TSQL query commands allowing for integration with BCP capabilities within an SQL Query. It works very similar to BCP.
Later SQL Server included DTS (Data Transformation Services) allowing data to be imported from and exported to many ADO/ODBC data sources. It had the ability to chain together events using a graphical user interface resulting in repeatable packages.
With the advent of SQL Server 2005, Microsoft released a completely new model for managing data. It could still move data from different data stores. In addition, it allowed you to transform the data with tools such as Pivot/Un-pivot, Aggregate, Filter, Convert, Lookup, and much more. This is the product we know today as SQL Server Integration Services (SSIS). It is a premier Extract-Transform-Load tool in high demand today operating on the Windows platform.
Today, with SQL Server 2012, you have available to you BCP, TSQL BCP Commands, ADO.Net BCP, a Simplified Data Transfer capability built into SQL Server Management Studio, and full scale ETL in the form of SSIS.
As you may know, SSIS is the preferred tool for ETL in SQL Server today, especially for scheduled or repeatable tasks. If you are not familiar with SSIS, we have online training available on a frequent basis. If you don’t receive promotions for training opportunities here at SSWUG you can enable that by logging into www.sswug.org and chaning your user settings.
Would you like more information about SSIS? Drop a note with your questions to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Total Database Information At Your Fingertips (Part – IV)
This article will help to get some basic information from your databases that may help you in different situations.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)
Featured Script
admin db – baseline and trends using sysperfinfo
Procedure to capture and use performance data from the sysperfinfo system table. Additional details for this script can be fo… (read more)