Introduction
In this tutorial I will show how to execute SQL from a file for various purposes.
Background
When I am trying to develop my data warehouse then I need to create a lots of table for Stage, NDS, ODS, and DDS purposes.
So if I store all the create table scripts in a particular folder and execute them one by one, that will serve my purpose.
- • A stage is an internal data store used for transforming and preparing the data
obtained from the source systems, before the data is loaded to other data stores in
a data warehouse.
- • A normalized data store (NDS) is an internal master data store in the form of one or
more normalized relational databases for the purpose of integrating data from various
source systems captured in a stage, before the data is loaded to a user-facing data store.
- • An operational data store (ODS) is a hybrid data store in the form of one or more normalized relational databases, containing the transaction data and the most recent
version of master data, for the purpose of supporting operational applications.
- • A dimensional data store (DDS) is a user-facing data store, in the form of one or more
relational databases, where the data is arranged in dimensional format for the purpose
of supporting analytical queries.
Each folder contains their respective table structure
Each file with CreateTable- prefix contains create table SQL script.
What you Need
This script was tested in SQL Server 2008.
Create Project
At first open SQL Server Business Intelligence Development Studio.
Then go to File->New->Project and select Integration Service Project.
From Control Flow Item toolbar select "Execute SQL Task" and drag onto Control Flow tab.
Double click "Execute SQL Task" and this will open a new window.
Select Connection and then click "New Connection".
Click "New" for New connection or Select from left tab if connection already exist.
Select "Server Name" and Authentication, Click "Test Connection". If everything is OK then click "Ok". Then again "OK".
Click "SQLStatement".
Then write SQL command for create database. Click "Ok" and then again "Ok".
From Control Flow Item toolbar select "Foreach Loop Container" and drag onto Control Flow tab. From "Execute SQL Task" drag Green Line to "Foreach Loop Container".
Double click "Foreach Loop Container" and then from left panel select "Collection".
Then select "Enumerator" value and then select rest of the value.
From Left Menu select "Variable Mapping" and then select new variable and then create a variable. Click "Ok" and then again "OK".
Now this variable will hold each file name from the directory you have chosen.
From Control Flow Item toolbar select "Execute SQL Task" and drag into the "Foreach Loop Container".
Right click "Connection Manager" tab and select "New file Connection".
Select any file and rename it by "FileConnectionFromFolder".
Select "FileConnectionFromFolder" and assign value to Expression.
Now double click "Execute SQL Task" and assign value. "SQLSourceType" should be "File connection" and "File Connection" value will be
our newly created file connection manager "FileConnectionFromFolder".
Now Execute.
"Stage" database has been created with three table.
Conclusion
I hope this might be helpful to you!
References
- Building a Data Warehouse - Vincent Rainardi.
History
None so far.
