stevienova.com

In many shops, I am guessing there are multiple servers. Development, Production, maybe a Staging, etc. With SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS), you set up data sources, connection to databases. In SSAS you usually have a connection which then you build your data source view off of, and in [...]

Sometimes you have some pretty complex ETL’s going in SSIS, and you might have multiple projects/solutions that need to call other SSIS Packages or SQL Agent Jobs and you have a pretty big production going on. You might have an ETL solution that needs to kick off other packages, and you can either import those [...]

Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration [...]

Today I received an email asking to some extent best practices on development with SQL Server Integration Studio (SSIS) and Business Intelligence Developer Studio (BIDS) in a team environment. Here is part of the email: Me and another DBA belong to the same team, we have a SQL server with SSIS running. We use the [...]

For a while, I have been working on figuring out a “better” way to get data from DB2 to Microsoft SQL Server. There are many different options and approaches and environments, and this one is mine, your mileage may vary. Usually, when pulling data from DB2 to any Windows box, the first thing you might [...]

Most ETL systems (at least that I have seen/studied/worked on) that populate data warehouses run something like   1) Load Dims a) populate an unknown b) populate dim data 2) Load Facts a) join/lookup to dim’s, and if no match, set as “unknown” dimension record 3) Process Cube   This type of system works in [...]

In SQL Server Integration Services (SSIS), pretty much every task or transformation lets you set “expressions” up. Expressions are basically ways to set property values programmatically. Here are two scenarios where you might use expressions (there are 100’s of uses, these are just two that are kind of related). Multiple Database Select – You have [...]

So, you install SQL, and if you are in the US, you 99.9999% of the time install it with the default collation, or language, or whatever. US English, Codepage 1252, etc, etc Latin blah blah… Then, you are tasked with pulling data from somewhere else, but it happens to be a different language or collation [...]

Last week I was in a heated discussion about Business Intelligence, and it came up that “Business Intelligence is just writing reports, what is the difference from just writing some reports?” I tend to disagree. Writing reports is, well, writing reports. You can write reports off of tons of data, and yeah, probably get some [...]

In the Microsoft Business Intelligence world, most people use SQL Server Integration Services (SSIS) to do their ETL. Usually with your ETL you are building a DataMart or OLAP Database so then you can build a multi-dimensional cube using SQL Server Analysis Services (SSAS) or report on data using SQL Server Reporting Services (SSRS). When [...]