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>
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
TPBPIT

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Faiga Diegel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
8080_Diver

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck