Password error in SSIS

Question

Password error in SSIS

pete.olding

SSC Journeyman

Points: 84
More actions
October 22, 2015 at 8:26 am

#312438

Hi all,
Trying to set up new SSIS packages on a SQL 2005 machine to replace all my DTS packages on a previous SQL box. The packages are mostly transferring data from spreadsheets and flat files to one of two SQL boxes. One of which is on the network the other in a DNZ. Created the packages in Visual studio OK and they run fine. Copied the packages to our network SQL box and trying to set up jobs for them to run automatically. However, one thing they do not like is the SQL tables have users and passwords associated with them and I get the runtime error that the packages can't log into the tables. I think this is caused by the packages not storing the passwords. Can anyone suggest a way around this?
MTIA.
Peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

pete.olding (10/22/2015)
Hi all,
Trying to set up new SSIS packages on a SQL 2005 machine to replace all my DTS packages on a previous SQL box. The packages are mostly transferring data from spreadsheets and flat files to one of two SQL boxes. One of which is on the network the other in a DNZ. Created the packages in Visual studio OK and they run fine. Copied the packages to our network SQL box and trying to set up jobs for them to run automatically. However, one thing they do not like is the SQL tables have users and passwords associated with them and I get the runtime error that the packages can't log into the tables. I think this is caused by the packages not storing the passwords. Can anyone suggest a way around this?
MTIA.
Peter

Can you explain what " packages can't log into the tables" means please?

Also, out of interest, why did you choose not to use SSIS 2012/2014 as your new platform, rather than something already 10 years out of date?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pete.olding SSC Journeyman Points: 84 More actions · Answer 2

We've had the SQL server 2005 installed for a while. The only reason we are moving the DTS packages across is because we are decommissing the old SQL server. The DTS packages are the last thing to go from that server.

When I run that package it returns the error 'Login failed for user xxxx'

thanks

Peter

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

If you wanted to stay in the past, you could have followed this: https://www.virtualobjectives.com.au/sqlserver/dts_sql2005.htm

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246956 More actions · Answer 4

pete.olding (10/22/2015)
We've had the SQL server 2005 installed for a while. The only reason we are moving the DTS packages across is because we are decommissing the old SQL server. The DTS packages are the last thing to go from that server.
When I run that package it returns the error 'Login failed for user xxxx'
thanks
Peter

This is an error which happens while connecting to a SQL instance? Can you not use a trusted connection instead? Much easier to manage and more secure too.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 5

There is a property in the package that will allow you to save sensitive with password. Use that. Or better yet, use SSIS configs in a table.

HTH

[font="Courier New"]ZenDada[/font]