Link to home
Start Free TrialLog in
Avatar of TPBPIT
TPBPIT

asked on

Issue with SSIS packages mirgrated to SQL2008 R2 from SQL2005

I have several SSIS packages that I have exported out of Integration Services on SQL 2005 to flat file.  (Just FYI, we decommissioned to old SQL 2005 server a named the SQL 2008 server with the same name).  I then opened the packages with the Business Intelligence Development tool and upgraded them.  Before saving I checked the packages and they seem to be fine.  All the connections still work and the user info looks good.  I them imported them into Integration Services on the SQL 2008 server.  After that I went into the Management Console and created a new job to run the package.  I created just like the one that was on the previous server, however it will not run.  When I start at step it just fails.  The event viewer posts the below error.  I can see that it says there's a password problem, but for the life of me I can't figure out where.  I've tried every place a password is used, even in the package, but it just continues to fail.  What am I missing?

Log Name:      Application
Source:        MSSQLSERVER
Date:          8/9/2011 11:34:24 AM
Event ID:      18456
Task Category: Logon
Level:         Information
Keywords:      Classic,Audit Failure
User:          N/A
Computer:      BRSQL.domain.com
Description:
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQLSERVER" />
    <EventID Qualifiers="49152">18456</EventID>
    <Level>0</Level>
    <Task>4</Task>
    <Keywords>0x90000000000000</Keywords>
    <TimeCreated SystemTime="2011-08-09T16:34:24.000000000Z" />
    <EventRecordID>1630</EventRecordID>
    <Channel>Application</Channel>
    <Computer>BRSQL.domain.com</Computer>
    <Security />
  </System>
  <EventData>
    <Data>sa</Data>
    <Data> Reason: Password did not match that for the login provided.</Data>
    <Data> [CLIENT: &lt;local machine&gt;]</Data>
    <Binary>184800000E0000000700000042005200530051004C0041000000070000006D00610073007400650072000000</Binary>
  </EventData>
</Event>
ASKER CERTIFIED SOLUTION
Avatar of Faiga Diegel
Faiga Diegel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TPBPIT
TPBPIT

ASKER

The data source is using Windows Authentication.  Also, if I open the Integration Service console and run the package from there it executes fines.  However, if I go into the Management studio and run the Job that runs the package, it fails with the sa password error above.  Not sure what I'm missing here.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TPBPIT

ASKER

I did.  However, I rebuilt the packages from scratch and they work now.  I think the issue was in the migration and upgrade of the 2005 packages.  That being said, I'm going to reward you guys for your answer because they helped me through the thought process.