vb dts to ssis

[mflancour]

what is the easiest way to migrate a dts to ssis? IS it possible to simply past the code into a script task? Keep in mind my vb and ssis experience is pretty limited (I'm working with a dts created by another coder)

 

[MDXer]

In my opinion the best way is to rebuild it from scratch that way you ensure you are utilizing SSIS and it's features as much as possible.

If the DTS was developed by another employee then ask a few questions so you fully understand everything being done to the data. If it is a former employee then spend some times making sure you know excatly what is happening in the process.

Read up a bit on DTS so you know what the different tasks do and when is best to use each.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mflancour]

Thanks Paul. So there is no way to simply paste the code that exists?

 

[paulkeys]

If it's just SQL code being executed, then both DTS and SSIS have a similar task that can do this.

For other tasks, e.g. Data Flow etc, it would be better to rebuild the package as mdxer suggests

 

[mflancour]

So, does ssis have some option like dts did where you can do everything using vb code?

 

[katbear]

Hi mflancour,

I have spent the past month doing exactly what you are trying to do, NEVER having used DTS or SSIS before in my life. However, I have used jobs extensively in both 2000 and 2005, and for some reason it all felt very homey to me (complex, but familiar I'd guess you'd say).

Here is what you're gonna need to do:

1.) Open your packages in DTS, then save them as Structured Storage Files to the 2005 machine

2.) Next, in BIDS (Business Intelligence Development Studio), you need to MIGRATE the DTS packages using the DTS Migration Wizard. You right click in SSIS Packages in Solution Explorer, then select Migrate DTS 2000 package. Follow the screens.

3.) If all goes well, you will now have a new SSIS package with a .dtsx extension on it.

4.) Because you have ActiveX scripts in your package, the Migration Wizard will "wrap" them up in Execute DTS 2000 Tasks. At some point, you're going to have to translate the ActiveX script to VB.Net using a Script Task/Script Component.

5.) Also, in order to DO anything with the Execute DTS 2000 Task, you need to download and install DTS Designer Components add-in from the MSDN website. Once you have that installed on your 2005 machine, you can actually see what's going on INSIDE the Execute DTS 2000 Task.

Good luck. You do have a learning curve ahead of you. and you're gonna have to pick up a little VB.Net. It helps if you have a VB.Net developer handy that can get you started.

Pick up a copy of Kirk Haselden's SSIS book. The first chapter is on migrating DTS packages to SSIS. This is how I figured it out.

Hopefully your packages aren't over the top complex. I think you can save A LOT of time using the Migration Wizard, not write from scratch. For one thing, you will learn what the equivalent SSIS tasks are because the wizard does this for you on parts of the package. I would have been clueless without the migration wizard!! Now, I write packages from scratch, no problem. But why reinvent the wheel at the beginning?

 

[katbear]

Oh yes, one more thing, if you're migrating packages, BE SURE TO CHANGE YOUR CONNECTION MANAGERS!

This is why you really need to download the DTS Designer Components, so that you can change the connection managers INSIDE the Execute DTS 2000 Package Task.

 

[mflancour]

Hey katbear. Excuse my ignorance, but in step 2, right clicking in solution explorer does not give me a list to pick from. Do I need to start a new project first? If so, what kind of project?

 

[mflancour]

ok, figured that part out...now, how do I attach the dtsx thing to a new database?

 

[mflancour]

Used

http://msdn2.microsoft.com/en-us/library/ms137565.aspx

to figure out how to save it to a server. then created a job using a step with a type of sql server integration services package.

problem is id errors out right away.

After running the migration the package that is generated is just one item, an activex script task. Inside is all the code that was in the dts. Not sure what do do now.

 

[paulkeys]

First thing to check if you're getting errors when scheduled on the server is Package Protection Level.

There are many schools of thought on this - but my favourite method is to set the protection level to encrypt all data with password, and then use the password rather than user credentials to ensure the package is working.

There is lots of stuff on the net (and prob this forum) if memory serves me correctly

 

[mflancour]

It's a test server on which I am admin, so it can't be a security issue.

 

[katbear]

It's hard to know what's going on without being able to see it, but:

1.) Did you download and install the DTS Designer Components on your 2005 machine? You can't edit or even SEE the ActiveX scripts without it, as they are wrapped up inside the DTS 2000 Package Task.

2.) Where are your connection managers pointing?
Are you using windows or sql server logins in your connection managers? Do you have an equivalanet login set up on the 2005 machine?

3.) When you saved your pkg to SQL Server, did you set it to use "SQL Server" security?

 

[mflancour]

1) ya, I can see the script by going into the objects properties then script

2) sorry, not sure what a connection manager is
Using windows for everything that asked me.

3) Windows

 

[katbear]

2) There should be a Connection Manager tab at the bottom of your window in BIDS.

Connection managers define the source/destination and login information for your tasks.

For example, if you have DTS 2000 Package Task, open it up. Right now it's probably still pointing to the old 2000 machine where the old DTS package was before. It's very likely you are running queries, etc on the wrong server.

Read up on Connection Managers, they are critical to the successful funtioning of your package.

 

[mflancour]

Hmmm, well that is where I think thins must differ between DTS and ssis. In the DTS package all the connections are defigned in the vb script.

Function Main()

	OpenDBConnection

	' load configuration
	DATESTART = Now

	' Get Settings from DB
	LINKEDSERVER = GetSettings("db1.db.main")
	DATABASENAME_CAMPAIGNBUFFER = GetSettings("db1.server.buffer.db")
	LISTSERVER_ID = GetSettings("db2.server.id")
	LIST_MONITOR = GetSettings("db1.db.main.monitor")

 

[katbear]

I see, you probably need to keep those connections then unless you eliminate the ActiveX script task altogether using built-in Data Flow objects.

 

[mflancour]

so..... how do I get it to work?

 

[katbear]

Assuming this code is "wrapped" inside a DTS 2000 Package Task (is it??)

then you should just be able to execute the DTS 2000 Package task, as is.

Then at some point, you will probably want to re-write the ActiveX using a Script Task, but in the meanwhile you are ok to just run the old DTS package.

As far as why it might not be working, that is a more complex problem the details of which I have no idea because you haven't given any details thus far.