DTS Conversion to SSIS (Part 4 of 4)
by Brian Knight
All About Options
We have lots of additional options, as well. One of the options that I want to really bring out to your point here is under "Other Options" here, this protection level property. This protection level property is not a DTS Exchange feature. This is a feature of SSIS. By default, the default option is the “encrypt-sensitive with user key.” Every new package you create will have that option turned on. What that means is any kind of sensitive data in your package – usually it's the connection strings, passwords, all those kind of things – will be encrypted with this user key. This user key means my user key on this laptop on my login only. If you logged into my laptop, tried to open the package up and I had that option on, all that data would be wiped clean and you would no longer be able to see that option. Encrypt-sensitive user key will not allow you to see my sensitive data.
Unfortunately, that's not a good option when I go to deploy to production, right? When I deploy to production now, it's using my user key to run that in production and all the sensitive data will be wiped clean. This is a number one issue I see in the forums on MSDN. It's usually people that don't understand that when I deploy this to production, it's using the user key of SQL Server Agent now, and that package will fail to connect. If you're using Windows authentication, however, that's not a problem. There's no sensitive data with Windows authentication.
Additionally, what I like to do is I like to encrypt-all or encrypt-sensitive with a password instead. One of these two options is a really good option for you. You basically assign a password. It'll prompt you for a password, you type it in, and now you can see all the data. That's a great option, the encrypt-all password. Keep in mind, if you lose that password, you're done. You cannot open that package. It's great for job security. [Laughing.]
All right. I'm going to turn this off for the time being. Let's do the defaults. I'll hit "next."
I'm going to start the migration while I chat with you. One of the things it’s going to take a little while to do as I do this, is we're going to see errors and warnings. The reason why we're seeing errors and warnings? Because of this validate package check box in the top right. Oop, I need the password right here. With this validate option in the top right – I’ll cancel that – it's going to validate to make sure every package is going to run in production. What’s missing in production? That's why I'm seeing these errors. There's something missing to make that package work. It's going to migrate the package, but it's either going to give me a warning or an error. The package will migrate, even with an error like this. But there's something missing from getting that to actually run in production.
As it's finishing here, as soon as it's done, it's going to pop open a little log here, let me know what happened, and as you see, I got two warnings. Not a problem, though. I can actually look right now and see oh, the reason why I got the error, I'm missing a DSN. I've got an ODBC DSN that's missing. This is why you typically want to run this program from the server that's going to be doing all the connections.
You can also see under validation summary here some other things that are missing. The pubs database. Well, I haven't seen the pubs database in five, ten years. That’s missing from here. That's why I got some of the warnings I received. Those kind of things are all available to you.
You'll also see here, if I can scroll down, some ActiveX scripts. I can see that we actually read the code for us and this Help document on the right here is contextual, based on what we saw in those packages. If I hit they hyperlink right here, when I select that ActiveX script warning, it lets me know how to migrate that ActiveX script. This is the old ActiveX script code. Here's what we should do now to fix that problem. It’s very contextual help based on what kind of problems you experienced.
And as you can see that's a loop right here. How we do a looping construct? Here's the old way of doing it. Here's the new of doing it. It’s a very, very simple code now to do all that.
After I'm done there, I can hit the "view packages" button, and we're ready to look at the packages that actually came out of this. What it's doing right now is it's creating a project – I think I hit view packages, there we go – it's creating a project. It's loading the project open right now. Ok, there we go. And I have some problems here, I'm opening it with the wrong tool here. OK. There we go. It's the first time I've opened this environment up. I think this is actually opening '05, instead of '08. I think it's my mistake. That's part of my problem right now.
In a second, the package will open – that's what's happening right now. That's OK.
We'll kind of go through this, this way. As you can see, the package opened and I have the environment all set. I have a package I should be able to hopefully – unfortunately I can't run because I think I'm in the '05 environment. Yes, I'm in Visual Studio 2005. That's why it's giving me such grief right now.
Let me open up this project in '08 instead. Let me go to Properties, find the file. I think I actually put the file somewhere else. I think the put the package file under here. C SIS packages. Let me open up the package. OK. I'm going to open up this one right here. Looks like I've got something here working. Looks like I'm definitely in '08 now. That's good. All right. That was my problem the first time.
In '08, if I open up the same ODBC package you saw before, you’ll see this is the one that errored out before. It's working, but because the OBDC driver that's not installed, I'm getting the error there. No problem. If I run the package, I'll get the same error you saw in the log before. It's basically an ODBC error saying, “Hey, you don't have the MySQL driver installed.”
If I go over to this package right here, we can see in this one that this is the exact same package I showed you before inside the DTS designer. You should be able to run the package and we'll see the data flow and this whole script right here.
All that works, the data flows all work beautifully. We added code here for things like the OLE DB source. We added code to convert, to do some data conversions here. We've also added rows counts. Those row counts come into play later in the event handlers. You can see the event handlers. If an error occurs, we write all the error log in here. This is all the code that we actually created for you to do all that work for you.
Lastly, you can see under configuration files right here, package configurations, that we actually externalized all the connections. As you migrate from development to QA to Prod, you just.
Completing The Migration
Lastly, let's hop back over to our slides and we'll complete the migration now.
The most important piece that you guys probably care about is ActiveX scripts. How do I migrate the ActiveX scripts? The good thing about this is the tools that we've mentioned so far can do a good chunk of those for us. The tasks that you have in SSIS will replace most of the ActiveX scripts for you. For example, the file system object, that's replaced with a file system task. The mail object, that's replaced with a Sim/Mil task. Lastly, the ADO object will act as an execute SQL task. A lot of the reasons you would do DTS ActiveX scripts for have now gone away.
Let me show you what I mean. Let's actually convert one of those now. This ActiveX script package that we had before, let me open that up. As you can see it's a loop construct right here. Let's kind of move this to the side here. What we're going to do is in my toolbox, I'm going to drag over a Foreach loop container. I'm going to disconnect this, bring this guy into the container. What this container does for you is it basically does everything that ActiveX script did before inside this loop.
I'm going to delete these guys right here, I no longer need them. I'm going to double click on the Foreach loop container and I'm going to say that I want to loop over a set of files. The files I'm going to loop over are going to be in a C:\, I'll just call it projects, whatever. We'll loop over all my *.CSV files. Get the fully qualified file name and put that into a variable. The variable I'm going to put them into is my gb full file name. Hit "OK" and I'm done. That loop is now done! All that logic I had written in my ActiveX script task, spent hours and days, or all that logic that I had inherited from some other poor DBA, I can now easily document and see now.
Next, this flat file connection manager down here, I really need to tie that connection manager to the variable that we just set in this loop. I'll right-click on it. Over here in Properties you'll see the expression page right here. I'll hit the "expression" button and I'm going to set the connection string to be equal to the variable that you saw before. That'll be equal to the gb file location – or gb full file name, excuse me. Hit "OK." Hit "OK" again.
By doing that, I hit the connection manager, tied it based on the expression. You'll see the expression now right here. Now every time I process that file it will loop and point to a different file name.
The last thing I can see we're doing here is we're defining some global variables. We're trying to see if a file exists, like a header file exists. What I can do is I can write some script task code to do that. It's really simple, it’s like eight lines of code. If you're not a coder you could right-click and part of DTS Exchange ships the tool called BI Express, which is for brand new packages.
I'm going to hit "ad snippet." This snippet is going to tell me – it has a whole bunch of pre-canned code for me. I want to see if the file exists. I'll hit "next." My input file is going to be a variable. I can tie it to a connection string, or whatever. I'll tie mine to a variable, and the variable’s called the "full file name." I'm trying to see if this file exists. Good enough.
And then next, where am I going to store your response from that? I'm going to create a brand new variable called "file exist flag." I'll hit "create variable." Hit "OK." And I'm all done.
When I hit "next" and "start" it's going to actually create that script task code for me, without any kind of coding at all needed. Now I've got all this code done, I'll tie these together, and I only want to run this loop if that one header file exists.
I'll double click on this green line, again, it was called the variable. I hit right-click and say "variables." You'll see the variable was called "file exist flag." If that's equal to true, then do that. Otherwise do something else.
I'm going to double-click on this green line. I'm going to set the constraint to "expression and constraint." I ultimately want to evaluate to see if this variable, with the @ sign’s needed there, is equal to “true.” It's equal to “true,” then I'm done.
Now the ActiveX script is now converted. We have a beautiful package! We need to change just one more, of course, but we have other script snippets that can do that. You can actually import your own script snippets by the way and share them with your colleagues. These scripts can be used over and over and over again. They're basically a shared library for yourself. There's other ones that do Internet, downloading files, all the stuff that we found that most people did in SSIS are now being done like zipping and unzipping, calling a SSIS package program automatically, all those things that you would do in ActiveX script are pretty much done in these script snippets. And it saves you hours and hours and hours of time.
Wrapping Up
Ok, let's hop back over to our slides one more time. In that demo what I showed you was how to convert ActiveX script easily. Now, if you have any questions – on this deck, I showed you how to convert your packages using the Migration Wizard, using a tool called DTS Exchange, where you can profile your packages for free and convert a number of packages for free as well. I also showed you exactly how to convert the ActiveX scripts and some of the differences between DTS and SSIS.
If you have any questions, please free to e-mail me at bknight@pragmaticworks.com. You can also see my blogs at www.blogs.pragmaticworks.com. Or follow me on Twitter at BrianKnight.
I hope you enjoyed this presentation by the SSWUG Conference and I hope to see you later.
Thanks a lot.
This article taken from the session presentation at the SSWUG.ORG virtual conference. Please be sure to see the other parts of this article, if any, linked at the top of this article. For additional information about the SSWUG.ORG virtual conference and expos, plase see http://www.sswug.org/uvc
