Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server
Article

SSIS: Create Table From File

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
8 Jan 2013CPOL3 min read 38.8K   246   11   2
How to execute SQL from a file for various purposes.

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.

Image 1

  • • 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.

Image 2

Each folder contains their respective table structure

Image 3

Each file with CreateTable- prefix contains create table SQL script.

Image 4

What you Need

This script was tested in SQL Server 2008.

Create Project

At first open SQL Server Business Intelligence Development Studio.

Image 5

Then go to File->New->Project and select Integration Service Project.

Image 6

From Control Flow Item toolbar select "Execute SQL Task" and drag onto Control Flow tab.

Image 7

Double click "Execute SQL Task" and this will open a new window.

Image 8

Select Connection and then click "New Connection".

Image 9

Click "New" for New connection or Select from left tab if connection already exist.

Image 10

Select "Server Name" and Authentication, Click "Test Connection". If everything is OK then click "Ok". Then again "OK".

Image 11

Click "SQLStatement".

Image 12

Then write SQL command for create database. Click "Ok" and then again "Ok".

Image 13

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".

Image 14

Double click "Foreach Loop Container" and then from left panel select "Collection". Then select "Enumerator" value and then select rest of the value.

Image 15

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.

Image 16

Image 17

Image 18

From Control Flow Item toolbar select "Execute SQL Task" and drag into the "Foreach Loop Container".

Image 19

Right click "Connection Manager" tab and select "New file Connection".

Image 20

Select any file and rename it by "FileConnectionFromFolder".

Image 21

Select "FileConnectionFromFolder" and assign value to Expression.

Image 22

Image 23

Image 24

Image 25

Image 26

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".

Image 27

Now Execute.

Image 28

"Stage" database has been created with three table.

Image 29

Conclusion

I hope this might be helpful to you!

References

  • Building a Data Warehouse - Vincent Rainardi.

History

None so far.

License

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


Written By
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.

Certification

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 :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez

Comments and Discussions

 
GeneralMy vote of 5 Pin
TeriMS9878-Jan-13 14:09
TeriMS9878-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.