SSIS: Import Export Table Wizard

The new wizard is opaque.  Just by looking at the package, you can barely figure out what is going on. The “Transfer Task” has no designer. The file connections can’t be viewed without firing up notepad.  To get the list of tables included, you have to open and search TableSchema.xml You can’t see the progress of the table copies.  And finally, the UI isn’t friendly.  On my first attempt I ended up with my project in the TEMP folder, files had seemingly randomized names.

Grr, makes me want to go back to bcp and INSERT INTO

SSIS Trick

BIDS doesn’t sort package names in the Packages folder. To make them sort, exclude all from project and then re-add existing item. DO NOT re-add existing project, because that will make a copy of the project instead of adding it as would be intuitive. (This add-as-a-copy behavior only makes sense if you are getting a package from the SQL msdb store)

UI Options For SSIS

1) No user interface.

2) BIDS as the user interface.  Very flashy, dramatically reduces performance.

3) Windows forms through scripting task

The first technique I’ve used involved:

Creating a windows form in VS2005.  Add a public property for communicating with the calling script.  Copy the code for the partial class and the code behind class to a single class, put it in the Scripting window.  Add a reference to System.Drawing.  Add import statements for System.Windows.Forms & System.Drawing.  Make initialize components visible.
New up the form class, call intialize components, and .Show()

Add a Do/While loop, with a System.Windows.Forms.Application.DoEvents()  keep the script from exiting and closing the form and to let the window respond to events while polling the public property on the form.

Some day I should rewrite this entry and make it more clear.

SSIS Packages and Shared Configurations

I have a lot of packages, not all have the same set of connection objects. When I try to let them share the same connection file, I get error messages that say the package might be corrupt because the config file mentions a connection that isn’t in the package. This is getting very aggravating, I’m seriously considering falling back to handwritten configuration code that would read from an .ini.

If a shared config file has 20 configuration items in it, then you have to create 20 objects in every package that uses the config file, even if that particular package uses only 2 of the configurations. And if there were two packages that had the same 20 components, then it probably would be a good candidate for merging into a single package.

Another anti-pattern would be creating a configuration file for each package.  Since these, say 20 config files mostly have the same information (the names of our 3 favorite servers), this is a lot of duplicate code.

SSIS Notes

Executing a stored procedure in the SQL Task is less painful if you use the ADO.NET provider.

Make sure you understand that a ‘data source’ is an XML file outside of your project, while
Environment variables are portable (unless there is a name clash), but you might not have
rights to set the enivornment variable.

Packages don’t like sharing package configuration files if you are setting properties of objects that don’t exist in all packages. Lets say you have 6 packages, each needs to have the names of 2 servers out of 3. The 2nd package complains about the 3rd one existing.

Changing a connection manager is brutal. With my current technique, it means deleting all the OLE DB Destinations and sources, remapping, etc.

SSIS’s ‘Fast Load’, ‘INSERT BULK’ and Bcp

SSIS has an option called ‘Fast Load’. That means using the bcp application to move data to SQL without issuing INSERT commands, bypassing the relational DB engine. It is dramatically faster, but invalidates subsequent transaction log backups.

insert bulk [dbo].[Table Name]([columnn name] datatype) with (TABLOCK,CHECK_CONSTRAINTS)

SQL profiler shows the above syntax, which according to Books Online doesn’t exist. It is just SQL profiler’s way of showing what is essentially a binary event, not a T-SQL event.

Setting up Integrated Security for SQL Server Linked Servers

Impersonation is passing kerberos (windows/intregrated/SSIS) creditials from application to application on machine. Accounts need to be trusted for impersonation. Most linked servers are not on the same box, so you have to use delegation. Delegation only works on Windows 2000, Windows 2003. Windows 2003 can do constrained delegation, but constrained delegation only works when there are no old windows OS’s on the network.

Linked servers are good. Linked servers with the SA account impersonating all users attempting to use the link is bad. Using a linked server with a SQL user account with read only rights is better security, but too limited for copying tables between servers. Using SQL security in general is a bad idea, at least on SQL 2000.

The better idea is to use SQL Delegation.

1) The SQL service account must be trusted for delegation (and confusingly, not explicitly untrusted for delegation). The enterprise administrator for the domain (not the subdomain administrator) must set an account to be trusted for delegation.

2) The computer *does not need* to be trusted for delegation. The SQL2000 BOL state otherwise. SQL2005 documentation agrees the computer does not need to be trusted for delegation.

3) You need to download setSpn.exe, which is not available by default. You will need the full subdomain and domain name

setspn -A MSSQLSvc/ sql4) You have to have TCP/IP running. If you are using a funky port ,you need to modify the above.

5) When you set up your link, in enterprise manager, check the ‘impersonate user’ option.

After I set this up, I discovered that the delegation didn’t work on any of the Windows 2000 boxes, but it did work on Windows 2003. This is even after we tried various random tricks, such as variations on the setspn.exe syntax, restarting servers, variations on the machine and service account name in the setspn command, granting the machine ‘trusted for delegation’ Still, at the end of it all, only Windows 2003 was able to do delegation. Me thinks that Windows 2000 delegation is broken. At a certain level of difficulty, it is not a difficult feature for power users, it is merely a broken feature.

SSIS Beta: Exciting Features & Woes

SSIS Beta is a dramatic improvement over DTS. However, the beta is a bit frustrating to work with. I’ve been working with converted packages and they crash the designer left and right.

Here are some random observations:

Data transformations that are more complicated that column copies are not converted, but are set up as embedded DTS2000 packages.
You can now rename everything, and you will probably want to. All my data sources were called OLEDB Source/Destination instead of the name of the connection manager. Steps, which were always given garbage names (eg. DTSTask_DTSDataPumpTask_1), couldn’t be easily changed in DTS2000, but can be changed in SSIS. The Package Explorer is your friend when it comes to renaming the mess of generic names you get in a typical DTS or converted DTS package.
I ended up with connection managers named “My Server copy copy copy copy” not to be confused with the connection named “My Server copy copy copy copy copy”
The most exciting feature is configuration files, but using them to set server names is not immediately obvious. I guess I’ll have to RTFM.

I think if the destination text file is missing, the DTS2000 conversion process fails. But that is just a guess.

You will want to remove passwords from your DTS2000 packages before attempting a conversion (requires saving as different name and deleting old DTS package)

Many DTS package conversions failed inexplicably.
CR-LF for TSQL code in SQL tasks fails to convert (turns into little boxes)

There are UI irritations:
Everytime you change the package name, that package explorer loses focus, so you have to use the mouse to get focus again.
I’m constantly being warned about “extremely long text” which makes no sense. (And where did the text annotations go?)
Boxes can’t be automatically expanded to hold the label, so most labels are visually truncated, resizing is tedious.

Scripting Irritations
You can’t just access any .NET assemby. This makes it hard to do automation of MS-Office, for example the Excel connection manager can’t really do everything you might want to do with an excel spreadsheet, so automation would help. (Tip: when you need to create an excel spreadsheet, copy a pre-existing blank)

Things to like about SSIS:

Filesystem component (don’t need to write code to rename the file you just imported)
Visual Studio for Applications (It’s a real programming language in a real IDE! (inside another real IDE!))
Configuration file support (Both shops I’ve worked in found it so hard to move DTS packages that development was done on the production server)
SMTP email (outlook is not for scripting!)
Separation between data flow and flow of execution in the designer
Halt and resume (beats modifying code each time you need to resume or debug)

Beta is still a bit flaky, I hope it stabilizes in RTM.