Download
1 / 7
70 likes | 186 Vues
Discover essential tips and tricks for working with SQL Server Integration Services (SSIS). Learn how to correctly identify file formats with tools like Notepad2.exe, and understand the importance of distinguishing between UTF-8 and ANSI formats to avoid character loss. Explore the challenges of handling comma-separated files with embedded quotes and consider pre-parsing with regular expressions. Get insights on manipulating variables, dynamic connection strings, and handling FTP connections. Additionally, find out how to create custom solutions for zipping files and executing batch processes in SSIS.
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
