Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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.

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.

License

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

Share

About the Author

mparvez
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
Follow on   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmemberTeriMS9878-Jan-13 14:09 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 8 Jan 2013
Article Copyright 2013 by mparvez
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid