We’re back to our ETL with SSIS project, and today we’re going to pick up with transforming the data from our raw format to the final destination for our contact data. So far we’ve scanned a directory for files matching a certain pattern. After finding a file matching that pattern we set up some variables,…
Month: January 2009
SQL 102 – UNIQUE INDEX
Ok, you understand how to create an INDEX. You understand the differences in CLUSTERED and NONCLUSTERED indexes. Did you know you could index a view? You can. But before you start adding indexes, just like you would for a table, you’ll have to create an UNIQUE CLUSTERED INDEX on that index. The reason for that…
SQL 301-ETL With SSIS, Part 3
Ok, so in our SSIS package we currently have all our variables set up, we have a for each loop that will look in a certain folder for all files matching a certain file pattern. Inside that loop we have a script that will build our archive path and error paths. Next we need to…
Code to Write Code
I’ve read “When you start writing code that writes code for you, you’ve moved up from a beginner to a professional.” Today I’d like to walk you through an example of where learning to write this kind of code pays off. At my we have a table that defines a hierarchy. Each level on the…
Server 2008 Firestarter Event
The SQL Server 2008 Firestarter event is a 1/2 day, free event for SQL Server developers and administrators. You’ll be able to learn about the newest features in SQL Server 2008, and how you can leverage them to improve your existing applications. Developer topics will include the new features in T-SQL. including the new data…
Get Rid of the Duplicate Records
Time after time I’ll get a table that has duplicates in it and I have to clear out all the duplicate entries while leaving a distinct set of records. There’s a couple of ways to solve the problem. Today I want to take you through my standard fix. Script the source table out to a…
SQL 301 – ETL With SSIS, Part 2
In my last post on SSIS, I started you into developing an ETL solution with SSIS. I walked you through setting up your solution, renaming the package, and setting up the variables we would use during the rest of these lessons. Today, we’ll pick up with a Foreash Loop Container. In most case, you’re going…
SQL 102 – CLUSTERED vs. NONCLUSTERED Indexes
Previously, I covered the basics of creating an index using CREATE INDEX. Today, I’m going to dig a little deeper. I’d like to teach you the difference in an CLUSTERED versus a NONCLUSTERED index. If you learn nothing else, know that a CLUSTERED INDEX physically orders the table, a NONCLUSTERED INDEX creates another “table” and…
70-433:Good, Better, Best Index Selection
With SQL Server 2008, you’ve got some additional tricks you can pull off with indexes. With 2005 you could INCLUDE columns in your index, now with 2008 you can also create indexes with a WHERE clause. This means you can limit the number of rows in your NONCLUSTERED indexes. By limiting the number of rows…
SQL 301 – ETL With SSIS, Part 1
I’ve discussed SSIS on this site before, but I’ve heard the feedback, you want real world examples. So, I decided to use the business case I address most often using SSIS, and that’s Exchange Transformation and Load. Let’s dive right in to this problem. You receive a file via FTP from a vendor every day. …