Tag Archives: SSIS

Create an Environment Variable (for use in SSIS Packages)

Posted on by
1

To create an environment variable on your PC/Server follow these simple steps:

  1. Click Start
  2. Right-click Computer and select Properties
  3. Click Advanced System Settings
  4. Under the Advanced Tab, click Environment Variables
  5. Click New…
  6. Enter a Variable Name and Variable Value
  7. Click OK, and you’re done!

The Environment Variable Editor

Fig 1. The environment variable editor

 
Updating Environment Variable Values

To update the environment variable that you have just created, you can redo the steps above and choose Edit… instead of New.. in step 5. Or, You can do the following:

  1. Bring up a Command window (Start>Run>”cmd”)
  2. Type SET, and press Enter
  3. This will give you a list of all the Environment Variables available to you.
  4. Find the one you want to update, type SET YourEnvironmentVariableName=YourVariableValue
  5. Type SET, and press Enter to check the new value
  6. Done.

Note – you’ll need to have administrator privileges to perform the above actions.

Template Locations for SSRS/SSIS files for SQL Server 2008

Posted on by
Reply

I like to try and create templates for each type of project that I am working on. This takes the work out of having to do the same tasks over and over again when it comes to creating new items (Reports Services Reports, Integration Services Packages). So where do you place those templates that you have created? I’m glad you asked:

 

SSIS Templates go here:

%Program Files%Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\

SSRS Templates go here:

%Program Files%Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\

De-cluttering the SSIS Toolbox

Posted on by
Reply

If you are developing solutions in SSIS and are having to deal with limited screen real estate, here’s a really quick tip to de-clutter that SSIS toolbox and hide some of the Control/Data flow components that you don’t use.

With BIDS (that would be Business Intelligence Development Studio…) open and an SSIS project loaded, bring up the toolbox and right-click it. You will see the following menu:

image

Select the Choose Items… option. Now, click on the SSIS Control Flow Items tab and deselect all the components that you are not using.

image

Repeat the above step for the SSIS Data Flow Items and Maintenance Tasks tabs.

Click OK and you are all done. The components listed should now reflect just those that you will actually use and should mean less vertical scrolling.

Isn't that better...

 

But wait, there’s another option…

If you would prefer to have all your components immediately available, but still want to organise the toolbox, then follow these easy steps:

 

image

Right-click the toolbox and choose Add Tab. Give the Tab a nice descriptive name.

image

Now click on and drag into that group/tab all the components that you are not using regularly, or indeed the ones you are (if that’s what you would prefer).

All that’s left is to apply a Sort Items Alphabetically and collapse the groups you are not concerned with and you have a nice tidy toolbox.

 

Happy days!

 

SQLBits VI – The 6 Sets.

Posted on by
Reply

It’s almost time for another SQLBits, and in my [humble] opinion, it’s not soon enough. These free conferences are fantastic and if you do have the opportunity to attend I suggest you do so. Whilst I have only a small idea as to what goes into organising a conference, I am pretty sure that it is a major undertaking. One that I am glad Simon Sabin (Blog|Twitter) and the rest of the SQLBits team (and let’s not forget volunteers here) are handling, and handling rather well. If you are keen to help out, in any way, contact Simon and offer your services.

I have only been to one SQLBits Conference (SQLBits V, Newport, Wales) and have talked about it here, here and here. I liked it. I liked it a lot (as if you couldn’t tell, right?). I got to meet loads of sqlservery people and had a number of good conversations, attend several brilliant presentations and a number of good ones (most of which I learned a bunch of new things), won a Redgate SQL Compare license (which I am still waiting for btw, Redgate, nudge nudge) and generally had a great time. While SQLBits V was a 3 day affair, SQLBits VI will be 1 day only and the theme is Performance Tuning and Scalability. Oh, and it’s free.

Conference Details

SQLBits VI will be held in London, and more specifically, at the Church House Conference Center, Westminster, London, UK on 16th April 2010. Starting at around 8am (for registration, swag-bag pick up and of course a few obligatory bacon baps) the conference kicks off with sessions across several tracks:

  • Database Administration
  • Development
  • Business Intelligence

With the focus being on performance tuning and scalability for all tracks, this promises to be a great conference for learning some interesting things.

Registration is open (and has been for a little while, if I’m being honest), but you will have to be quick. Becuase it’s only a 1 day conference, but mostly because it’s such a great conference and it’s FREE, places tend to be snapped up pretty darn quick. Also, if you register for SQLBits VI before 30th March 2010 you stand a chance of winning registration to the European PASS Summit (Germany), if you attend SQLBits VI then you stand a chance to win a registration to the PASS Summit (Seattle, USA).

One point on registration. As it is free, people tend to register even if they are not sure if they will actually attend. While this is not a bad thing in itself, if you are one of those people AND it turns out that you cannot attend then please cancel your registration. This frees up a place for someone else that can make it (but was too late [initially] for registration). There’s a lot that goes into planning a conference and most of this is done around the number of attendees. If a number of those registrants don’t pitch up, then there is a not only a whole lot of wasted money but a whole lot of wasted opportunity. Don’t be selfish, think of the community and act accordingly. Remember, it’s free!

Remember the after party

The sessions are bound to be fantastic, and you are likely to learn a great deal. But go home early at your peril. The prize giving, party and social after all the day’s events is not something you will want to miss. Why? Well, this is a prime opportunity to really get to know more SQL Server people. People that may well become your friends down the line, or at the very least be a contact point should you need assistance on a specific topic (the SQL Server Community is pretty awesome that way). It is also the best time to find those elusive speakers and ask them the questions that you have prepared. It’s a prime time to do this as the speakers are not stressed out about their presentations and are in a more relaxed and approachable state. They are much more likely to entertain your <ahem>query </ahem>, especially if you hand them a beer before you do so.

So, stay out a little longer than 6pm and make the most of a wonderful opportunity.

And finally…

I’m looking forward to catching up with some old acquaintances and learning a lot of new tricks on how to tune SSIS Packages and Scale Reporting Services Solutions. I’ll also be taking some notes on presentation styles and on what makes a speaker/session a great speaker/session, as I’m thinking getting on to the presentation bandwagon….in the hopefully not-too-distant future.

 

See you at SQLBits VI!

Setting the SQL Command Property using Expressions in SSIS 2005

Posted on by
Reply

 

OLE DB Source Adapter

The OLE DB source connection has several Access Modes:

  • Openrowset
  • Openrowset From Variable
  • SQL Command
  • SQL Command From Variable

For now, I am going to focus on the SQL Command options. Using the SQL Command Access Mode requires you to set the SqlCommand property (typing in your SQL query directly), effectively ‘hard coding’ the sql statement into the package. Using the SQL Command From Variable option allows you to set the SqlCommand property using a variable and subsequently by using an expression.

SSIS2005_DataSource_AccessMode_Properties_OLEDBSource

 

Data Reader Source

The Data Reader Source connection has only one Access Mode, and that is SQL Command. Don’t ask me why. At this level you can only set the SqlCommand property by typing in your query, or indeed, copy & paste.

SSIS2005_DataSource_AccessMode_Properties_DataReaderSource

 

Using an Expression to set the SqlCommand Property

“So then,” you ask, “how might I set the SqlCommand property for each of these Data Sources when it seems that I can’t?”. Here’s how. Let’s first concentrate on the OLE DB Source:

  1. Create a new Variable of type string and set your SQL statement
  2. Open up the OLE DB Source and select the ‘SQL Command from Variable’ option
  3. Now, select the the variable created in step 1 as the variable that contains the SQL statement

SSIS2005_DataAccessMode_OLEDBSource_Editor

At this point your OLE DB source is defined by the query that you have set in the Variable. We now need to create the expression that will define the query at runtime. We do this by navigating to the variable’s properties, setting the EvaluateAsExpression property to True and then configuring the expression in the Expression property.

Moving on to the Data Reader Source. With this adapter we only have the option of setting the SqlCommand directly. Or so I thought until recently. If you switch to the Control Flow design surface and select the Data Flow task that contains the Data Reader Source adapter, you will be able to set the SqlCommand using an expression.

  1. Select the Data Flow task to bring up its properties
  2. Click the ellipsis (…) to bring up the Expression Editor
  3. Select the [DataReader Source].[SqlCommand] Property
  4. Set your expression.

SSIS2005_DataSource_AccessMode_Properties_DataFlowTask_Expressions

SSIS2005_DataSource_AccessMode_Properties_DataFlowTask_ExpressionProperties

Using variables & expressions to set your queries enables you to centralise your queries, use configuration files to set database connections and capture the queries you are running in logging operations.