Temporary tables are created in the TempDB database, which persists for a particular session. The objective is to maintain that session, until the temp table information is used and dump the data into a physical table. To maintain a session in SSIS, there is a ‘Retain Same Connection’ property of the Connection Manager. If we [...]

SQL Server Integration Services (SSIS) has under-gone through some significant changes in SQL Server 2012 which I will outline in this article. Connection Managers Now you have project-based connection managers which mean those connection will be available for all the packages that you are creating. This avoids recreating frequently used connections for every package.  Those [...]

Using stored procedure with temp tables for SSIS packages. Some stored procedures are complex and so require temporary tables or table variables. Let us say we have the following procedure to extract data. CREATE PROC ProcWithTableVariable AS DECLARE @Table TABLE (id Int) INSERT INTO @Table VALUES (1),(2) SELECT * FROM @Table And SSIS OLE DB [...]

Bulk insert is much faster than using other techniques such as  SSIS. However, when you are using bulk insert you can’t insert to specific columns. If, for example, there are five columns in a table you should have five values for each record in the text file you are importing from. This is an issue [...]

SQL Server integration Service (SSIS) can be used to import data from heterogeneous data sources ranging from SQL Server, Oracle to CSV, XML files. To Import a single DBF file to SSIS 1. Open Microsoft Visual Studio 2008 and create a SSIS project. 2. Drag and Drop a Data flow task to Control flow and [...]

This is the continuation of my previous article: Reporting Services 2008 R2: Geospatial Visualization – Part I. Part I discussed the  main characteristics related to the Map Report Item, Data sources for spatial data, how to create a map report using shapefiles, and finally add analytical data to it.  Our goal in the part is [...]

Introduction According to the “Data Quality and the Bottom Line: Achieving Business Success through a Commitment to High Quality Data.” The Data Warehousing Institute. Report Series 2002, data quality problems cost U.S. businesses more than $600 billion per year. Why such a cost? If your data is not up to quality, your KPIs and data [...]

Overview: Business Intelligence (BI) is a much known term in comparison to the term Spatial Intelligence (SI). Spatial Intelligence is not limited to just space agencies, in fact it’s used day to day by many of us. A simple example can be analyzing usage of your blog from different parts of the world. By analyzing the [...]

This article leverages the examples and concepts explained in the Part I through Part IV of the spatial data series which develops a “BI-Satellite” app. Overview In the spatial data series we looked at free-form text based address data and worked towards developing a Geospatial Dashboard . In this artilce we will look at using [...]

Overview SQL Server 2008 introduced a new category of datatypes known as spatial datatypes which store spatial information. The new spatial datatypes are geography and geometry. SQL Server Management Studio comes with good good support for these spatial data types like the spatial results tab and facilitated access to .NET Common Language Runtime (CLR) functions [...]