SQL Server Business Intelligence Development Studio

As software engineers employed to small and mid-sized companies we are sometimes,  if not all the time expected to be Swiss army knives. We are the DBA’s, Systems Analysts, Software Engineers, Testers and on occasion we are the users of the software that we design. After a while we realize the importance of using the available tools to greatly increase our productivity; one such tool is Microsoft’s SQL Server Business Intelligence Development Studio. In this post we will see how to leverage SQL Server Business Intelligence Development Studio in a task such as importing data.

There are a few ways to pass data from one database table to another database table.

  1. Write and execute TSQL statements that will transfer the data.
  2. Use SQL Server import tool.
  3. Use Business Intelligence Development Studio to create an SQL Server Integration(SSIS) package.

With the first option you would have to write all conversion code and set up code to do field mappings in TSQL, if you are not comfortable with SQL  then this will be a challenging task. This can be offset by the second option of using the import tool; however, the import tool can not do custom conversions of data types. All of these deficiencies can be overcome by using SSIS packages to design and automate import tasks. It is also possible to save these packages and have SQL Server run them as tasks. Lets look at how we can do this with an example.

Create a database called TestBench in your local SQL Server instance.

Create table -> Source  = {ID, FirstName, LastName, Gender}

Create table -> Destination = {ID, FirstName, LastName, Gender_ID}

Create table -> Gender = {Gender_ID, GenderName}

Create relationship -> relationships