Type 2 dimension table layout

In order to track Type 2 dimension processing, metadata columns are required.  Below is an example.

SSIS 2012 Multi-column hash key transformation

Have you ever needed to do data comparisons over many columns and desire to use a hash key algorithm?  And needed to do it in SSIS?

I invite you to look at the information at CodePlex:

http://ssismhash.codeplex.com/documentation?referringTitle=Home


This is unbelievable stuff and it should be noted they're the authors of said code and documents.  Just thought it was great enough to share.

How to pass a variable to an SSIS package on execute?

Q:  How to pass a variable to an SSIS package on execute?

A:  A few ways.  One can execute via a command line and designate the variable value, execute via SQL Agent and pass the value or use a config file.  I'm going to demo the 1st two below.

Execute via SQL Agent:

Or via command line:
*note: this example uses a SSIS package stored in a file folder.

/FILE "C:\SSIS\projects\SSIS_PKG.dtsx"  /CHECKPOINTING OFF /SET "\Package.Variables[User::myvar].Properties[Value]";myvalue /REPORTING E

SQL Server Database Backup Script

Looking for a quick database backup script with a datetimestamp?  Replace the [DBName] with your database name.  Note the [FolderPath] needs to be updated as well.

SQL Server Database Backup Script with datetimestamp:



--db name   [DBName]

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''[DBName]_networkdevice'', ''[FolderPath]\[DBNAME]'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [DBName] TO [DBName_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'[DBName backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'[DBName] backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='[DBName]'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='[DBName]')
RESTORE VERIFYONLY FROM  [[DBName]_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice '[DBName]_networkdevice'

Execute SSIS package from command line for 32bit

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /DTS "\MSDB\[FOLDER]\[PACKAGE NAME]" /SERVER [SERVER NAME] /DECRYPT [PASSWORD] /X86 /CHECKPOINTING OFF /REPORTING E

Row Number: Quick TSQL Row_Number

Create a quick row number in a sql statement:

select ROW_NUMBER() OVER(ORDER BY [COLUMN] ASC/DESC) AS Row
,* from [TABLE])

ETL Framework Stages

ETL Framework Stages:

STAGE:    storage area between the source data and the data warehouse or ODS or BI marts.  typically temporary in nature.  Used for data cleansing, landing of data in a like format and placing the data out of its source format.

PERSISTENT STAGE:  storage area of data that allows transactional and incrementally changing data to be stored.  Typically the data is kept close to its originating structure and is not related to other sources as one would see in a dimensional approach or 3NF ODS.  It is not used as a system or record, but rather a processing area for historical storage  Meta data columns help process changes to keep history and the likes of type 2 (or others) disciplines can be applied.

ODS:  An operational data store (ODS) is designed to integrate data from multiple sources. The data used then as the system of record and will be used to update/insert data back out to source systems.

DW:  The data warehouse (DW) is a database used for reporting and analysis. It acts as a repository of data that can be fed from STAGE directly, PERSISTENT STAGE (for historical purposes), or the ODS . Data warehouses typically contain current and historical data.

BI MART (DATA MART):  A focused slice of the data warehouse built to focus on a specific subject area.  It can be separated from the DW to help with storage, security, or further business logic not desired in the DW.