So they are rather convoluted in my opinion, but very powerful.
Expressions in SSIS. The key to making dynamic packages is to use variables and expressions. Go to the variables window and create a new string variable, say DATE. (Use upper case for variables - they're case sensitive and this will save you headaches.) Then create another variable, let's call it SQL_QUERY. Make sure they're both string. Set the default date to 20050112 and the default sql to select * from mytable where date = '20051112'.
Here's where it gets interesting.
Go to the Package tab, then browse to the variables collection. Click on the SQL_QUERY variable and view the properties. Turn on Use as Expression. I use notepad to type in the expression, since they didn't put any ... window in there yet. (SP1??) And the expression is...
"Select * from mytable where date = '" + @[User::DATE] + '"
Your query should show up in the variable value property with the correct date at the end.
Voila. This should remove a bunch of useless script from your code. (See Project REAL where they were passing around the XML/A variable to the Analysis Services command task)
I can think of a few other places this would work (connection strings in variables maybe?).
Next goal is to try and figure out why it takes 5-15 seconds just to load a package from DTRUN.
Sunday, January 29, 2006
SQL Server 2005 articles
SQL Server 2005 articles: "Generating Test Data" + Everything else you ever wanted to know about SSIS
Friday, January 27, 2006
Sunday, January 22, 2006
Presentation Zen: Gates, Jobs, & the Zen aesthetic
Presentation Zen: Gates, Jobs, & the Zen aesthetic
Presentation Zen is a very clean web site with some great links on powerpoint presentations.
Presentation Zen is a very clean web site with some great links on powerpoint presentations.
8 mistakes when creating PowerPoint presentations
8 mistakes when creating PowerPoint presentations
Here's one they missed: hide reminders/desktop alerts so you don't get a message like "refill herpes meds" etc during your presentation:
Powerpoint > Tools > Options. > Preferences tab > E-mail Options > Advanced E-mail Options.
Under When new items arrive in my Inbox, clear the Display a New Mail Desktop Alert (default Inbox only) check box.
Here's one they missed: hide reminders/desktop alerts so you don't get a message like "refill herpes meds" etc during your presentation:
Powerpoint > Tools > Options. > Preferences tab > E-mail Options > Advanced E-mail Options.
Under When new items arrive in my Inbox, clear the Display a New Mail Desktop Alert (default Inbox only) check box.
Thursday, January 19, 2006
Thursday, January 12, 2006
Connecting to a Remote SSIS Server
Connecting to a Remote SSIS Server
There are some hoops to jump through for access denied errors with SSIS.
There are some hoops to jump through for access denied errors with SSIS.
Wednesday, January 11, 2006
Paolo's WebLog on SQL Server
Paolo's WebLog on SQL Server: "Parent child dimensions in Analysis Services 2005 "
Monday, January 09, 2006
Sunday, January 08, 2006
Download details: PSS Service Center Labs - 2005
Download details: PSS Service Center Labs - 2005
Overview
Do you want to know how Microsoft SQL Product Support Services (PSS) engineers break down problems? Originally designed as an opportunity for customers to work with Microsoft SQL Server Escalation Engineers at the SQL PASS 2005 Conference, these labs can be used for self-paced troubleshooting training on SQL Server 2005.
All labs are documented so that users can work through different scenarios in a self-study environment. These labs cover popular topics such as blocking, performance, memory, and debugging, and data recovery.
My way of hacking myself out of problems with software is usually Sysinternals Filemon & Regmon. There should be some interesting tips from above.
Overview
Do you want to know how Microsoft SQL Product Support Services (PSS) engineers break down problems? Originally designed as an opportunity for customers to work with Microsoft SQL Server Escalation Engineers at the SQL PASS 2005 Conference, these labs can be used for self-paced troubleshooting training on SQL Server 2005.
All labs are documented so that users can work through different scenarios in a self-study environment. These labs cover popular topics such as blocking, performance, memory, and debugging, and data recovery.
My way of hacking myself out of problems with software is usually Sysinternals Filemon & Regmon. There should be some interesting tips from above.
Microsoft Analysis Services - Wikipedia, the free encyclopedia
Microsoft Analysis Services - Wikipedia, the free encyclopedia: "History
Microsoft's foray into OLAP Server business began in 1996 with acqusition of OLAP technology from Israeli company Panorama. In 1998 Microsoft released the first version, named OLAP Services, part of SQL Server 7. OLAP Services supported MOLAP, ROLAP, HOLAP and Hybrid OLAP architectures, used OLEDB for OLAP as the client access API and MDX as a query language. It could work in the client server mode or in offline mode with local cube files. In 2000 Microsoft released the next version, named Analysis Services 2000. The rename from OLAP Services happened because of the inclusion of Data Mining services, so the product wasn't just about OLAP anymore. Analysis Services 2000 was an evolutionary release, it was built on the same architecture as OLAP Services and was backward compatible with it. The major improvements included more flexibility in dimension design, by supporting parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was much enhanced calculation engine with support for unary operators, custom rollups and cell calculations. Other new features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels etc. In 2005 Microsoft released the next generation of OLAP and Data Mining technology as Analysis Services 2005. Unlike previous released, Analysis Services 2005 was a revolutionaly release. It maintained backward compatibility on the API level, i.e. applications written with OLEDB for OLAP and MDX continue to work, but the architecture of the product was completely different. The major change came to the model in the form of UDM - Unified Dimensional Model."
Microsoft's foray into OLAP Server business began in 1996 with acqusition of OLAP technology from Israeli company Panorama. In 1998 Microsoft released the first version, named OLAP Services, part of SQL Server 7. OLAP Services supported MOLAP, ROLAP, HOLAP and Hybrid OLAP architectures, used OLEDB for OLAP as the client access API and MDX as a query language. It could work in the client server mode or in offline mode with local cube files. In 2000 Microsoft released the next version, named Analysis Services 2000. The rename from OLAP Services happened because of the inclusion of Data Mining services, so the product wasn't just about OLAP anymore. Analysis Services 2000 was an evolutionary release, it was built on the same architecture as OLAP Services and was backward compatible with it. The major improvements included more flexibility in dimension design, by supporting parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was much enhanced calculation engine with support for unary operators, custom rollups and cell calculations. Other new features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels etc. In 2005 Microsoft released the next generation of OLAP and Data Mining technology as Analysis Services 2005. Unlike previous released, Analysis Services 2005 was a revolutionaly release. It maintained backward compatibility on the API level, i.e. applications written with OLEDB for OLAP and MDX continue to work, but the architecture of the product was completely different. The major change came to the model in the form of UDM - Unified Dimensional Model."
Propagating Stimula: Setting Up a SQL Server Stress Test Environment in 8 Steps
Propagating Stimula: Setting Up a SQL Server Stress Test Environment in 8 Steps: "Setting Up a SQL Server Stress Test Environment in 8 Steps"
Great article, even for general requirements gathering and analysis.
Great article, even for general requirements gathering and analysis.
Managing Analysis Services Partitions with Table-Valued Functions (table UDFs)
Managing Analysis Services Partitions with Table-Valued Functions (table UDFs): "Here's an Adventure Works cube example:
1.) Create the following function in the AdventureWorksDW database:
----------------------
CREATE FUNCTION dbo.udf_FactInternetSales_Date_Range
(@Start_OrderDateKey INT, @End_OrderDateKey INT)
RETURNS table
AS
RETURN
(
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey >= @Start_OrderDateKey
AND OrderDateKey <= @End_OrderDateKey
)
----------------------
2.) Open up BIDS and the Adventure Work cubes and browse to the Partitions tab.
3.) Under the 'Internet Sales' measure group partitions, change the Internet_Sales_2004 source query to:
SELECT * FROM dbo.udf_FactInternetSales_Date_Range(915,1280)
4.) Do the same for the other partitions, except change the input OrderDateKey ranges passed into the UDF."
1.) Create the following function in the AdventureWorksDW database:
----------------------
CREATE FUNCTION dbo.udf_FactInternetSales_Date_Range
(@Start_OrderDateKey INT, @End_OrderDateKey INT)
RETURNS table
AS
RETURN
(
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey >= @Start_OrderDateKey
AND OrderDateKey <= @End_OrderDateKey
)
----------------------
2.) Open up BIDS and the Adventure Work cubes and browse to the Partitions tab.
3.) Under the 'Internet Sales' measure group partitions, change the Internet_Sales_2004 source query to:
SELECT * FROM dbo.udf_FactInternetSales_Date_Range(915,1280)
4.) Do the same for the other partitions, except change the input OrderDateKey ranges passed into the UDF."
Friday, January 06, 2006
Geek Noise
Geek Noise: "$size = new-object System.Management.Automation.Host.Size [int] $width = 132 [int] $height = 45 switch( $args.Length ) { 2 { $width = $args[0] $length = $args[1] } 1 { $width = [int] $args[0] $height = [int] ($width / 3) } 0 { 'No args...' | out-host } } 'Setting to $width x $height' | out-host $bufferWidth = $host.UI.RawUI.BufferSize.Width if( $bufferWidth -lt $width ) { $size = $host.UI.RawUI.BufferSize $size.Width = $width $host.UI.RawUI.BufferSize = $size $size.Width = $width $size.Height = $height $host.UI.RawUI.WindowSize = $size } else { $size.Width = $width $size.Height = $height $host.UI.RawUI.WindowSize = $size $size = $host.UI.RawUI.BufferSize $size.Width = $width $host.UI.RawUI.BufferSize = $size } "
Time to download Monad.
Time to download Monad.
Subscribe to:
Posts (Atom)
