Download
1 / 7
70 likes | 181 Views
SSIS tips & tricks. Peter Doyle. 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. Embedded quotes.
E N D
SSIS tips & tricks Peter Doyle
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
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)
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
Looping • Main thing to note is setting the connectionstring of the connection so that it becomes dynamic
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
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
