Simple SSIS package problems (1 Viewer)

[grunners22]

Hi

I've recently started using SSIS packages in 2008 as we have had to upgrade from SQL 2000 where I implemented several DTS packages. I have created a simple SSIS package that simply copies one table from one sql server to another. This package runs fine until I save it, then I just cannot get it to work. It is saved to the file system, yet when I execute it it fails to open the connection strings.

I have edited it in VS 2008 and the connection strings look ok, but I'm really struggling to figure out why it's failing.

Any help much appreciated!

 

[SQL_Hell]

How are your executing it from the file system? in a job?

 

[grunners22]

Well first of all I've been trying to simply right click the SSIS in Integration Services in the object explorer and then clicking Ruin Package, which is where it is erroring.

Is creating a job the way to do it?

 

[grunners22]

I've created a job and tried to run it that way, but again it's failing on the login to the destination database. The password appears to be there though in VS 2008.

 

[SQL_Hell]

Yeah a job is the best way to execute it, that way you can use the job history to see when it ran, how long it took ect.

So is the connection string using windows authentication or sql server?

 

[grunners22]

The connection string uses SQL authentication. When I'm in the connection properties in Visual Studio the test connection works fine.

In Connection Manager, I've ticked 'save my password' but the field stays blank when I go back in. However it doesn't appear blank in the 'properties' window on the right of the screen.

 

[SQL_Hell]

Ok, I am going to try and replicate the behaviour here, all the SSIS packages I have created use windows authentication.

I will get back to you in due course

 

[SQL_Hell]

When you save the SSIS package to file sytem

Set the Protection level property to EncryptSensitiveWithPassword

 

[grunners22]

Darn it! I thought I'd already tried that and it had said that the option wasn't available.

Anyway that worked a treat, many thanks!

I need to read up a bit on SSIS

Thanks again

 

[grunners22]

Just another slight issue I'm having now.

When I add the package as a job in XP on my machine, there are no problems - however, I've since created a package on a Server 2003 machine, and when I try to add a step into the job, the type 'SQL Server Integration Services Package' doesn't appear in the list.

Any ideas?

 

[SQL_Hell]

What versions are each sql server?

What service pack level are they?

On the server 2003 box are you sure Intergration services has been installed? it is a seperate installation than sql server

 

[grunners22]

It's definitely installed - I am connected to it in my object explorer.

In the Execute Package Utility it's version 2.0 on both platforms.

 

[grunners22]

The only thing that I can see that is different across the two platforms is that in object explorer, the XP (working) instance is SQL Server 10.0.1600, whereas the 2003 instance is SQL Server 8.00.2039. Could this be the cause of the problem?

 

[grunners22]

The sql instance on 2003 was originally in sql 2000, which I assume is why I can't add Integration Services.

I'll either try and add a new server instance or I'll start from scratch with new install.

 

[SQL_Hell]

Yeah sounds like something has screwed up in the installation, try uninstalling / reinstalling integration services first.

If you don't have any joy with that, then try a full reinstall and a registry clean (which is most likely your problem, screwed up registry)

 

[Lisa101]

The information you have provided is new to me. Thank's for it.
Lisa11
New Jobs