SSIS tips & tricks

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