I’m not gonna lie to you, Marge…I haven’t exactly been keeping up with the RTFM365. And when I have read, I haven’t noted down what I read. As it turns out, it’s really, really hard to do something every single day for a year. Or even, say, for two months, which is about how long this project has been going on. I don’t know how people do 365 projects…but I’m going to keep trying, keep coming back to it.
Reading this week was in SQL Server 2008 Books Online unless otherwise noted. Please note: I’m exploring BOL and learning a little each day; I’m not here to read it to you or review it.
What I Read Learned
I read more than I’ll note here…I just want to document a few noteworthy items I picked up along the way.
Database Engine > Technical Reference > Transact-SQL Reference > Functions > System Statistical Functions
- @@CPU_BUSY (“Returns the time that SQL Server has spent working since it was last started” in “ticks”.)
- fn_virtualfilestats (“Returns I/O statistics for database files, including log files…also available from the sys.dm_io_virtual_file_stats dynamic management view”)
Designing and Implementing Packages (Integration Services) > Designing and Implementing Packages Tutorials > Tutorial: Creating a Simple ETL Package >Lesson 1: Creating the Project and Basic Package
- Flat file data types: “If the flat file source is Unicode, the Flat File connection manager defines all columns as [DT_WSTR] with a default column width of 50. If the flat file source is ANSI-encoded, the columns are defined as [DT_STR] with a column width of 50. You will probably have to change these defaults to make the string column types more appropriate for your data.”This interested me because of some recent minor research I did into text file encoding…note that Notepad defaults to ANSI. I can’t tell what Wordpad defaults to (is RTF unicode?), but you have the optionin Wordpad to save to Unicode .TXT format.
Understanding the Components of an Integration Services Package > Data Flow Elements > Integration Services Sources > Flat File Source
- “The output columns in the output of the Flat File source include the FastParse property. FastParse indicates whether the column uses the quicker, but locale-insensitive, fast parsing routines that Integration Services provides or the locale-sensitive standard parsing routines. For more information, see Fast Parse and Standard Parse.”Who knew flat file sources could be so complicated? Oh yeah, a bunch of people…
Designing and Implementing Packages (Integration Services) > Working with Data in Data Flows > Parsing Data >Fast Parse
- “By implementing fast parse, a package forfeits its ability to interpret date, time, and numeric data in locale-specific formats and many frequently used ISO 8601 basic and extended formats, but the package enhances its performance. For example, fast parse supports only the most commonly used date format representations such as YYYYMMDD and YYYY-MM-DD…“
Operators (SSIS) > Cast (SSIS)
- “If the code page is a multibyte character code page, the number of bytes and characters may differ. Casting from a DT_WSTR to a DT_STR with the same charcount value may cause truncation of the final characters in the converted string. …if you cast character data to a DT_STR data type using the 936 code page, you should set charcountto a value up to two times greater than the number of characters that you expect the data to contain…” OHHHHhhh, that explains some of those infuriating truncation errors I’ve gotten over the years…
Designing and Implementing Packages (Integration Services) > Working with Data in Data Flows >Integration Services Data Types
- Let me just say that an overview reading of this section – especially the Mapping Integration Services Data Types to Database Data Types section – is LOOONG overdue. I hereby do my future self a favor, and link the SSIS Data Types BOL article here. Future self: You’re welcome.