27 Apr

SSIS: Package Deployment

These are the steps for deploying a package from development to a production server.

Step 1: Create a configuration file.

These are actually created at run time and as such are optional.  It’s here because Microsoft has in here.

Step 2: Create a deployment utility.

A deployment utility is a folder that contains the files that you will need to run the package.

The following steps create the utility:

  1. Make sure that the project has been converted to the package deployment model.
  2. Right-click on the package and select properties.
  3. Select the Deployment tab.
  4. Set AllowConfigurationChanges to True.
  5. Set CreateDeploymentUtility to True.
  6. Click OK to close.
  7. Right-click the project and select Build.

http://msdn.microsoft.com/en-us/library/ms137952.aspx

Step 3: Copy packages to the target computer.

Exactly what it sounds like.

Step 4: Run the “Package Installation Wizard” on the target computer.

Perform the following steps:

  1. Open the folder.
  2. Double-click the manifest file (.SSISDeploymentManifest) to start the process.
  3. Follow the steps.

Yeah, this is kind of light on detail.

http://msdn.microsoft.com/en-us/library/ms141693.aspx

The main MSDN for package deployment:

http://msdn.microsoft.com/en-us/library/ms137592.aspx

22 Apr

MaxConcurrentExecutables

There are two kinds of spurs, my friend. Those that come in by the door; those that come in by the window.
Tuco – The Good, the Bad, and the Ugly (1966)

Once again, something that popped up in the MeasureUp practice test, that I missed, and so, a post.

The MaxConcurrentExecutables is set by right-clicking the Control Flow and selecting properties.  The following are valid values for this property:

  • -1: Sets the maximum numbers of concurrently running executables to the number of processors, plus 2.
  • 1 or greater: The number of threads that a package can execute.

Any value of 0 or less, except -1, will result in an error.

What I missed wasn’t that it set the number of threads, rather, that -1 is equal to the number of processors plus 2.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx

08 Apr

SSIS: Executing A Project With T-SQL

This should be the last of the Execution series.

SSIS: Executing A Project With SQL Agent

SSIS: Deploying A Project And Executing It In SSMS

SSIS: Parameters With Expressions (it got messy)

So, here’s the deal.  You can execute a package with T-SQL.  It’s a bit messy, but doable.  We’ll be using the same set up and package as we had in those earlier links.  For me that means a folder at C:\testData, a folder at C:\testData\backup and a file called junk.txt in C:\testData.

packageinfoSince there isn’t much to do except write some code, I mean, copy some code, so here goes,

DECLARE @execution_id BIGINT;
DECLARE @logging_level INT = 1;

EXEC catalog.create_execution
@folder_name = N'ParameterTest',
@project_name = N'Project Deployment Demo',
@package_name = N'myFileMove.dtsx',
@execution_id = @execution_id OUTPUT;

EXEC catalog.set_execution_parameter_value
@execution_id = @execution_id,
@object_type = 50,
@parameter_name = N'LOGGING_LEVEL',
@parameter_value = @logging_level;

EXEC catalog.start_execution
@execution_id;

I think this is clean enough that I don’t need to explain it.  You can read more at the links below.  One parameter value to keep in mind for create_execution is the @use32bitruntime parameter.  If you set it to 1 the package will run in 32-bit mode, otherwise, it will run in 64bit mode.  It is optional.

I really hope this isn’t something else to memorize for the 70-463.  There are a handful of other parameters, that are optional, and it would be easy to forget them.  Especially since I’m not going to memorize them in the first place.

You can read more at these links.

catalog.create_execution

catalog.set_execution_parameter_value

06 Apr

SSIS: Deploying A Project And Executing It In SSMS

This is a continuation of yesterday’s article.  I’m going to convert that package to a project and run it from SSMS.

Also, just to be clear, it’s very possible that I have missed a step, or three, and that something in your setup may not match mine.  Just keep in mind, that largely, I’m writing these as test preparation, not as education.  So, read this with that understanding.

So, open up the project, which hopefully you saved.  Because we’ll be working with the name, and the package lets clean those up.

Step 1: Rename the package and project.

  • Project Name: Project Deployment Demo
  • Package: myFileMove.dtsx
Can't convert to a package.

Can’t convert to a package.

Step 2: Convert the deployment model to project.

It’s possible that you already have it set for project, but, if you don’t do the following.

  • Project > Convert to Project Deployment Model.
  • It should go smoothly with a couple of Next clicks.

If you try to convert the project to a package you’ll get the message on the right.  Essentially it’s telling you that you can’t use parameters in a package.

Step 3: Deploy

deploy2For this you right-click on the package name “Project Deployment Demo” and click Deploy.  The following should happen:

  • Welcome screen, hit Next.
  • The project validates.
    • Server name: localhost or whatever you are using.
    • Path: I hit the ellipse, and created a new folder and called it ParameterTest.  The path looked like this /SSISDB/ParameterTest/Project Deployment Demo.
  • Click Next, Deploy and Close.

And you’ve deployed a project.

Step 4: Run the project from SSMS.

Object Explorer

Object Explorer

Running a project in SSMS is easy.  Just do the following:

  • Navigate to the “Information Services Catalog”.  It should be near the bottom of the object explorer.
  • Open it and navigate to SSISDB > ParameterTest >Projects > Project Deployment Demo > Packages > myFileMove.dtsx.
  • Right-click on myFileMove.dtsx and select Execute.
  • Click OK to accept the parameters.
  • Yes, you want a report.
  • One of two things will happen, most likely an error.  In the package it to move the file.  This means that if you have run the package there will not be a file to move and the package will error.  Recreate the file, per the instructions in yesterday’s post (for me that simply meant adding junk.txt to C:\testdata) and run it again.  The package should and the report should be green.

The report you received provides basic execution information.  The most important part for a small package like that is that you see green.

Step 5: Modify parameters.

Success!

Success!

Before we do that, redo the file creation process in the last part of step 4.  For me, that meant putting junk.txt back in C:\testdata.  Now, lets change a parameter at run-time.

  • Navigate to the “Information Services Catalog”.  It should be near the bottom of the object explorer.
  • Open it and navigate to SSISDB > ParameterTest >Projects > Project Deployment Demo > Packages > myFileMove.dtsx.
  • Right-click on myFileMove.dtsx and select Execute.
  • When the Execute package window opens, click the parameter tab (it should be open) and click the ellipse for Operation and change the value to 0.  0 sets the File System Task to copy.
  • Click OK twice, and accept the report.
  • The report should be green.

When I navigated to C:\testData junk.txt was still there.  In the subfolder C:\testData\backup I also had a junk.txt so the package successfully copied the file.

And that is one way to use parameters to run a project.

27 Mar

SSIS: Package and Project Security Properties.

These are more, or less, the same thing, in that, they work the same way with the same settings.  The following settings apply at both the package and the project level.

Project: Right-click Project > Properties > Common

Package: Right-click Designer > Properties

ProtectionLevel

  • DontSaveSensitive: Sensitive information is not saved.  If a different user opens the package / project they must enter this information.
  • EncryptAllWithPassword: Encrypts the entire package or project with a password.  The package / project can not be accessed or run without providing the password.
  • EncryptAllWithUserKey: Encrypts the package with a key based on the user’s profile.  Only this user can use or run the project / package.
  • EncryptSensitiveWithPassword: Encrypts only sensitive data with a password.  This package or project can be accessed in the designer without a password but sensitive information must be reentered.  The package cannot be run without the password.
  • EncryptSensitiveWithUserKey: This is the default setting.  Encrypt sensitive information with a key based on the user’s profile.  The package or project can be accessed in the design but sensitive information must be reentered.  The package cannot be run without the password.

The only real different between the package and project security properties is that the UI is slightly different.  Consistency is the hobgoblin of small minds in the SSIS interface.

You can read more here.

http://technet.microsoft.com/en-us/library/ms141747%28v=sql.90%29.aspx