csv files import automation
DESCRIPTION
CSV files import automation. Kostya Khomyakov [email protected]. A l ittle 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 - PowerPoint PPT PresentationTRANSCRIPT
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