1 / 7

SSIS tips & tricks

Learn how to correctly identify and handle file formats in SSIS, including UTF-8, ANSI, XML, and more. Explore techniques for manipulating variables, looping, FTP, and zipping files effectively. Find solutions and tips for common challenges.

leannes
Download Presentation

SSIS tips & tricks

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. SSIS tips & tricks Peter Doyle

  2. File Formats • Identify the file formats correctly • Notepad2.exe seems good for identifying UTF-8 versus ANSI • Beware processing an ANSI file as UTF-8 as you will lose characters like umlaut ü and accented é • XML default format is UTF-8

  3. Embedded quotes • SSIS doesn’t seem to handle comma separated files with embedded quotes • Access and Excel can? • My solution is to pre-parse the file with a regular expression and replace commas with tabs • Use regex buddy to help with regular expressions (another dark science)

  4. Manipulating variables • Have demonstrated two approaches in now famous Package3.dtsx (within a sql task or script task) • Use http://www.simple-talk.com/sql/sql-tools/passing-variables-to-and-from-an-ssis-task/ as a reference • Probably using script task is more intuitive

  5. Looping • Main thing to note is setting the connectionstring of the connection so that it becomes dynamic

  6. FTP • For looping through multiple FTP sites. Have to code around this as passwords cannot be set dynamically on FTP widget • I used FtpClientConnection object (see code) • For recordset variables you need an object of System.Object • Demo configurations also

  7. Zipping • There is no widget so you will have to concoct your own solution • Batch files don’t work with unc’s • Use execute process task and live with the syntax which is frustrating

More Related