1 / 11

CSV files import automation

CSV files import automation. Kostya Khomyakov kostya@varigence.com.au. 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

timon-espinoza
Download Presentation

CSV files import automation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSV files import automation Kostya Khomyakov kostya@varigence.com.au

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

  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.

  4. What will we cover today? • Introduction • Main blocks of CSV integration package • Logical BIML steps for CSV integration package • Live Demo. BIML from scratch

  5. 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?

  6. 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 - ConfigFile Format - Transformation from CSV to Staging Tables }

  7. Logical BIML steps for CSV integration package Foreach (file in fileCollection) { Read 1-st line and get fields } Foreach (file in fileCollection) { a. Create Flat File Connections b. Create File Formats c. Transformation } 2.00 CreateImportCSVPackages.biml 3.00 CreatePackageProject.biml 1.00 CreateStagingTables.biml

  8. Logical BIML steps for CSV integration package Should be defined as variables in .Net + add variables: Files Folder Files Extension Challenges with CSV files: Header Row Delimiter Column Delimiter Row Delimiter

  9. Create SSIS packages automatic VS manual

  10. Live Demo

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

More Related