Using Expression Variables in SSIS

Here's a way to use Expression Variables in SQL Server Integration Services to dynamically create a connection string:

1. Create a new variable "SS_DST_Server"
2. Create a new variable "SS_DST_Database"
3. Create a new variable "SS_DST_Conn"
4. Set 'Use as Expression' property on SS_DST_Conn to true.
5. Under the expression, type in the connection string and append " + @[User::SS_DST_SERVER] + ";" where the server goes, and " + @[User::SS_DST_Database] + ";" where the database goes. Note it should start with =" and end with "

for example:
"Provider=SQLNCLI;Server=" + @[User::SS_DST_SERVER] +";Database=" + @[User::SS_DST_DATABASE] + ";UID=sa;PWD=asdasd;"

Now, instead of having to configure the connection string in a config file, you can store it in the package, and pass in the database and server name as a parameter.

There are other useful things you can do with this procedure.

Create a variable called "DATE_FILTER" and an expression variable "SQL_LOAD" with the expression "Select * from MyTable where mydate = '" + @[User::DATE_FILTER] + "'"

Dynamic SQL in your SSIS package.

Note variables are case-sensitive, so upper case is good practice.

Here's some sample connection strings...

www.connectionstrings.com

Swapping partitions in SSAS 2005 - MSDN Forums

Swapping partitions in SSAS 2005 - MSDN Forums: "You should be able to use the new database synchronization feature of AS 2005:

http://msdn2.microsoft.com/en-us/library/ms174928(SQL.90).aspx

>>
Synchronizing Analysis Services Databases

The Synchronize Database Wizard synchronizes two Microsoft SQL Server 2005 Analysis Services (SSAS) databases by copying the data and metadata from a database on a source server to a database on a destination server. You can also use this wizard to deploy a database from a staging server onto a production server, or to synchronize a database on a production server with the changes made to the data and metadata in a database on a staging server.

While the wizard synchronizes the data between the two databases, users can continue to query the destination database. After synchronization finishes,, Analysis Services automatically switches the users to the newly copied data and metadata, and drops the old data from the destination database."

Integration Services - Powerful and painful

In my opinion, one of the more idiotic things about SQL Server 2005 is Integration Services. Not the product itself, which is actually quite powerful, and since I would not touch DTS with a ten-foot pole picking it up was relatively easy for me. It is fun to work with, didn't crash on me a whole lot (unlike Visual Studio 2005 which is like watching Houdini, since it disappears randomly) and was fairly intuitive once I got the basics down.

What stinks is the methods of execution of these packages in a production environment.

Two main problems for me are package security and remote execution.

1. Password Encryption: What's with all of the options for package security? User Keys, Certificates, Package Passwords. How about abstracting this stuff right out of the package itself and into another tool, like a code obfuscator. It's wonderful to have, but sometimes, like a door, you don't always need a lock on it. Why do I have to type in a package password for EVERY PACKAGE everytime I hit Build? That's like typing in a password for every file when I type DIR in DOS. Also, what's with passing the /DECRYPT password in every time I have to run from a command-line? Can't we stick this in an encrypted config file somewhere?

2. Remote Execution - This is where I got burned. SQL Agent is the recommended means of executing packages on the SQL Server from a remote PC. So how do you pass in passwords and other parameters to the command line? I ended up creating two command-line tasks with sqlcmd, one to edit the job task step sp_update_job_step and another to run the job sp_start_job. Of course, when you run sp_start_job it immediately comes back. This could cause a problem for jobs that have to wait for others to run before starting, so you need to grab another custom script that checks every 10 seconds to see if it's running.

Other things that bug me about SSIS - not knocking the developers who put tons of effort into this - but just stuff that bugs me.

- The 5-10 second delay executing packages from command line.
- Clicking the play button in Visual Studio doesn't play the package selected. It could do many things, depending on what mood it's in.
- The script editor. Sorta reminds me of Excel VBA but without the ability to tab back and forth between the package and the script.
- Passing parameters from a command line. %8 %9 %10 %11 just doesn't cut it for me.
- No encrypted parameters in config files.

Deployment question: SQLDB - SSIS on different boxes with SQL Agent for scheduling - MSDN Forums: "SQL Agent is my preferred scheduler, and maybe yours too, but many organisations have their own standards. Many houses use IBM/Tivoli for example, and SQLAgent is not required for SSIS. If you would prefer to use it, then yes you are stuck. I think buyng SQL Server for SQLAgent is an overkill, but if you look at that SQL Instance in the same way as you look at MSDE, then it is no big deal. There are plenty of proucts our there that install DB engines. I saw a report that said Informix (I think that was the one) was one of the most widely deployed DBs, and that is because CA shoved it under the covers of ArcServe backup, probably for the scheduling :) "

Yes, my client does prefer to use a Unix-based scheduler, and apparently this means we're stuck using SQL Agent, called from another scheduler.

Part 1: Automating the XML Data Mapping Process in Excel 2003

Part 1: Automating the XML Data Mapping Process in Excel 2003: "Conclusion
The sample code in this article is generic and can be used for most scenarios requiring support of flexible XML mapping with little or no modification. Currently we use this code in a project called 'The Rhythm of the Business 2.0,' which supports a flexible and constantly changing Excel template used by more than 1,000 Microsoft employees to assess the entire company's performance on a monthly basis. In our project we also needed to harvest the unaltered data-point descriptions for use in a reporting application. We did this by adding meta tags, using square brackets for marking up the description positions. (This article does not include the code that retrieves the description values.) We encountered another reporting issue related to the Excel formulas. In a future article, we will describe how to take advantage of the grid structure to programmatically extract and store Excel formulas in a Microsoft SQL Server database and replicate the UI-side calculations using dynamic SQL."

dotProject Demo :: dotproject - Open Source Software :: Open Source Project and Task Management Software

dotProject Demo :: dotproject - Open Source Software :: Open Source Project and Task Management Software: "Thank you for your interest in our web-based project management tool.

dotProject is built using free open-source applications and is produced and maintained by a small, but dedicated group of volunteers. dotProject is programmed in PHP, and utilises MySQL for a backend database (although other databases such as Postgres could also be used).
Our recommended server platform includes Apache 1.3.27, PHP 4.2+, and MySQL. In the spirit of free, peer-reviewed, open source application development, we would also encourage you to use an operating system such as Linux, FreeBSD, or OpenBSD. However, additional operating systems such as Windows, Mac, and other flavours of *nix are also supported. "

ConnectionStrings.com � - � Forgot that connection string? � You will find it right here! �

ConnectionStrings.com � - � Forgot that connection string? � You will find it right here! �