Post reply

DSN ODBC pwd not saved when SSIS package imported

  • October 2, 2007 at 9:41 am

    #121840

    DSN ODBC pwd not saved when SSIS package imported

    SQL Server 2005 SP2 (Windows 32bit version)

    Implemented and FULLY tested a SSIS package with BIDs.

    This package uses a Data Reader object using a ODBC data source with a stored username (uid) and password (pwd).

    These are saved with the package locally.

    I then import the package directly in to SQL Server.

    I then create a scheduled SQL Agent job to run this package daily. When I look at the Data Source tab which lists all data sources this package uses the password is not listed.

    I then ran this job and as I expected it did not run because of the missing password.

    If I modify the data source within the job it lets me but it doesn't save the password.

    The DSN in ODBC Administator actually saves the password in the registry. Why doesn't SQL Server store the encrypted password.

    I needs this to work otherwise my project will fail.

    Any ideas anyone?

    Thanks,

    Regards,

    Richard.

  • October 2, 2007 at 10:46 am

    #738274

    When you save the package to the server; modify the protection level on the package to "Rely on server storage and roles for access control". This should do the trick.

    Tommy

    Follow @sqlscribe

  • October 2, 2007 at 12:55 pm

    #738328

    That did the trick.

    Many thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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