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:

=================================================================
DISCLAIMER
=================================================================
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.

=================================================================
BIG PICTURE
=================================================================
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.

===============================================================
SIMPLE WAY TO EDIT EXISTING PACKAGE
===============================================================
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!

Application Express

Leave a comment

I had time for fiddling around with Oracle Application Express (APEX) and could justify it as part of Oracle 11g product evaluation. Some notes follow.

When you use the database creation assistant with 11g, it generates a script called apex.sql, which in turn executes $ORACLE_HOME/apex/catapx.sql. That seemed to work OK, except there is a fair amount of configuration afterward. And Oracle Text really needs to be installed in order for apex help files to display correctly.

So here’s my list of stuff to do if you want to install Application Express.
1. Install Oracle Text first. $ORACLE_HOME/ctx/admin/catctx.sql. Check that all objects in dba_objects are valid and that components in DBA_REGISTRY are valid.
2. Run $ORACLE_HOME/apex/apexins.sql. Review the log file and make sure the entry in DBA_REGISTRY is valid. For older versions, you may need to run $ORACLE_HOME/spex/catapex.sql.
3. Review and then execute the steps in the Oracle Application Express documentation. You want the Application Express Installation Guide. It explains quite a bit, including how you can run either the Oracle http web server (Apache) or the web server that’s inside the database called embedded PL/SQL gateway. Pick which web server you want and follow the appropriate instructions. Be sure to remember the password you supply when running $ORACLE_HOME/apex/apxconf.sql. You’ll need it to do admin work with APEX.
4. Something not mentioned anywhere that I saw was the listener.ora change you need to ensure that embedded pl/sql gateway works. Adding this in the DESCRIPTION_LIST worked for me:
(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Thanks Tim Hall of http://www.oracle-base.com for pointing this out on your web site!
5. At point I was able to log in using http://%5Bhostname%5D:8080/apex/apex_admin, using user ADMIN and the password supplied in step 3 above!

Transactional DDL

Leave a comment

I found this thread on comp.databases.oracle.server interesting.

It’s a discussion of whether or not Oracle should support the ability to rollback a DDL statement. I never considered this to be a possibility or even something that I’d need. But I like the comparison amongst database products from different vendors. Such comparisons can help one understand better the general problems that RDBMS tools solve. One thing that came up in the thread was whether SQL Server supports transactional DDL. I had to verify for myself, and sure enough it does. Here’s a sample sqlcmd session:

1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> begin transaction
2> create table foo (f1 varchar(10))
3> go
1> insert into foo values (‘harry’)
2> go

(1 rows affected)
1> select * from foo
2> go
f1
———-
harry

(1 rows affected)
1> rollback
2> go
1> select * from foo
2> go
Msg 208, Level 16, State 1, Server USEOMAPD357, Line 1
Invalid object name ‘foo’.
1> exit

Oracle VM Server, Interrupted

Leave a comment

I’ve been wanting to get a virtual server running at home. But which vm server? I have two desktops at present. One is the general work machine for my wife and me and is running Windows Server 2003. I have a GB of RAM on this and can do SQL Server boondoggles when needed. My other machine now has 4GB.

I decided to try out Oracle VM Server. After looking around a bit, I learned that Oracle VM Server has two parts, the VM Server and the VM Manager. They need to get installed on separate machines. Apparently the VM Manager provides a web based GUI with which one can fiddle around with VMs running on the server. A little more digging and I found documentation which appeared to indicate that you could install a VM from the command line directly on the VM Server. Good for me, cause I don’t have a machine at home just now on which top VM Manager.

So…I installed Oracle VM Server. That went fine more or less, after creating a static ip for it. I downloaded a pre-packaged Oracle VM template with 11g on it from Oracle.com. I started unzipping it and….OUT OF DISK SPACE. My 40 GB drive was not sufficient for the unzipped VM. So I’ll need to purchase a larger drive for this machine before proceeding further.