Click here to Skip to main content
13,554,130 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


11 bookmarked
Posted 8 Jan 2013
Licenced CPOL

SSIS: Create Table From File

, 8 Jan 2013
Rate this:
Please Sign up or sign in to vote.
How to execute SQL from a file for various purposes.


In this tutorial I will show how to execute SQL from a file for various purposes.


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.


I hope this might be helpful to you!


  • Building a Data Warehouse - Vincent Rainardi.


None so far.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0

An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.

Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.


70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker

Web :
Blog :
linkedin :

You may also be interested in...


Comments and Discussions

SuggestionMessage Closed Pin
22-Apr-15 3:22
membercarmel22-Apr-15 3:22 
GeneralMy vote of 5 Pin
TeriMS9878-Jan-13 14:09
memberTeriMS9878-Jan-13 14:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02-2016 | 2.8.180515.1 | Last Updated 8 Jan 2013
Article Copyright 2013 by mparvez
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid