csv files import automation

Copyright © 2013 Varigence, Inc.

CSV files import automation

Kostya Khomyakovkostya@varigence.com.au

Copyright © 2013 Varigence, Inc.

A little bit about me

• SQL Server MVP from 2013 • MCSA SQL Server 2012 • Over 6 years of experience in Database Design

and Development in SQL Server • Over 8 years of experience in enterprise

application development using C# language• Speaker at SQLSaturday, 24HOP, SQLRally

Copyright © 2013 Varigence, Inc.3

What’s This All About?

Learn how to develop Microsoft BI Projects faster, more consistently, and more reliably than you ever thought possible…

… and have fun doing it.

Copyright © 2013 Varigence, Inc.

What will we cover today?

• Introduction

• Main blocks of CSV integration package

• Logical BIML steps for CSV integration

package

• Live Demo. BIML from scratch

4

Copyright © 2013 Varigence, Inc.

Introduction

• a lot of SSIS packages are very similar• packages importing data from different sources• packages exporting data to other sources• quite often .Net is used inside packages• packages used for dimension updates• …

• … but development takes a lot of time to create similar packages

Why not consider ETL processes as a set of models/patterns which can be easily added or removed?

5

Copyright © 2013 Varigence, Inc.

Main blocks of CSV integration package

• Connection to a DB• Create Staging Tables• Create Flat File Connection• Config File Format• Transformation from CSV to Staging

Tables

• Connection to a DB• FOREACH (csvFile in FilesCollection)

{- Create Staging Tables- Create Flat File Connection- Config File Format- Transformation from CSV to Staging Tables}

6

Copyright © 2013 Varigence, Inc.7

1.00 CreateStagingTables.biml

Logical BIML steps for CSV integration

package Foreach (file in fileCollection){ Read 1-st line and get fields}

2.00 CreateImportCSVPackages.biml

Foreach (file in fileCollection){ a. Create Flat File Connections b. Create File Formats c. Transformation}

3.00 CreatePackageProject.biml

Copyright © 2013 Varigence, Inc.8

Challenges with CSV files:• Header Row Delimiter• Column Delimiter• Row Delimiter

Should be defined as variables in .Net+ add variables: Files Folder Files Extension

Logical BIML steps for CSV integration

package

Copyright © 2013 Varigence, Inc.

Create SSIS packages

9

automatic VS manual

Copyright © 2013 Varigence, Inc.

Live Demo

9

Copyright © 2013 Varigence, Inc.

• Twitter– @BimlScript– @BimlDownunder

• LinkedIn Biml User Group– http://www.linkedin.com/groups?home=&gid=4640985&trk=anet_ug_hm– http://www.linkedin.com/groups/Biml-User-Group-Australia-5190127?home=&gid=5190127

• Varigence Mist– http://www.varigence.com/mist

• BimlScript– http://www.bimlscript.com

• CodePlex – http://bidshelper.codeplex.com/

• Biml Documentation– http://www.varigence.com/documentation/biml/

Resources