SSIS Cheatsheet

1 Comment

I infrequently use SSIS, but once in awhile I need to edit a package. This is harder than it was with SQL Server 2000, in which one could crank up Enterprise Manager, go to the target instance, drill down to a DTS package and start editing away, with changes saved directly to the database.

Here’s my newly created cheat sheet:

SSIS has lots of functionality and this is just a very bare bones description of opening an existing package. Note that this does not discuss deploying multiple packages all at once (deployment wizard) or details on editing and debugging SSIS packages. Nor does it weigh the merits of
storing SSIS packages on the file system vs in the database vs SSIS store.

SSIS Packages are eventually get stored and executed within SQL Server. They can be stored on the file system or within the SQL Server database, typically in MSDB.

SSIS Packages get edited in Visual Studio. To do that, you must get a copy of the SSIS package on to the file system. Several ways to do that. From within Visual Studio, you can create a new SSIS package, or you can choose Project – Add existing package and get one directly from SQL Server or a file system location. Or you can go to SQL Server Management Studio and export there first and then go into Visual Studio.

When done editing in Visual Studio, one must deploy to SQL Server. There are a few ways to do that. One can use the deployment wizard, or import the package using SQL Server Management Studio’s import package, or use the command line dtutil tool. There is also a dtexec for commandline execution of SSIS packages.

Once deployed, SSIS packages can then be run in SQL Server via jobs managed by SQL Server Agent.

Step 1: Start Visual Studio and Make a New Project
File – New Project
Integration Services Project
Use Location to create directory structure meaningful for your project and team’s coding conventions.

Step 2: Add an Existing Package to the Project
Project – Add and Existing Package
In the Add Copy of Existing Package dialog:
Package Location is SQL Server
Server (local) if you are logged on to the SQL Server machine. If working from laptop, identify the SQL Server instance and include port if necessary
(e.g. USEOMAPD357,1113)
Authentication: Windows
Package Path: Click on . and locate package.
Then click OK.
Nothing will change on screen, but a file will be on the file system containing the package which you can open in the next step.

Step 3: Open the package for editing.
File – Open
locate the newly exported package.

Step 4: Edit the package.
Use Visual Studio as needed to edit.
You can test the package as needed.
You will find the SSIS programming metaphor is roughly similar to DTS and other Microsoft visual editing tools.
Save changes to package and they will update the file on the operating system.

Step 5: Deploy the package
Start SQL Server Management Studio.
Connect to Integration Services
Click on Stored Packages
Click on MSDB
Right Click and choose Import Package.
Package Location will be File System
Use Package Path to identify the file you edited in Step 4 above.
Click on Package Name and provide a name or accept the existing one.
Make protection level Encrypt Sensitive Data with User Key.

Suffering from Enlarged Oracle Homes?

Leave a comment

Are you a diligent DBA who applies quarterly CPU patches whenever possible? Are you noticing how the $ORACLE_HOME directory gets larger and larger over time? That’s ’cause the opatch utility creates backup files needed for rolling back patches in a directory called $ORACLE_HOME/.patch_storage. Opatch will not eliminate old files here when you apply patches. However, starting in Oracle 10.2.0.x and higher, there is now a switch for opatch: opatch util cleanup. This will remove backup files from older patches that are no longer needed. Search on metalink for more details. This cleared up 2GB for me yesterday!