How to run SSIS package as a SQL Scheduled Job?

Hello:
I have a SSIS package that will import data from one DB to another. I would
like to set it up as a nightly job. When I did, it faied within couple
seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
that user is the SQL service account that SQL Agent uses.
Message
Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
(C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
Sec Tables from TRSDev to TRSDev0203 Connection manager
"SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is
available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
Description: "Login failed for user 'TRSNT\dev02-sql01'.". End Error
Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
method call to the connection manager "SourceConnectionOLEDB" failed with
error code 0xC0202009. There may be error messages posted before this with
more information on why the ... The package execution fa... The step
failed.
Am I missing something here?
Thank you in advavnce for your responses.
Chai> When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
Did you add that Windows account to the SQL Server you try to access?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> Hello:
> I have a SSIS package that will import data from one DB to another. I would
> like to set it up as a nightly job. When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
> Message
> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
> (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
> Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
> Sec Tables from TRSDev to TRSDev0203 Connection manager
> "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR.
> An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is
> available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
> Description: "Login failed for user 'TRSNT\dev02-sql01'.". End Error
> Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
> Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> method call to the connection manager "SourceConnectionOLEDB" failed with
> error code 0xC0202009. There may be error messages posted before this with
> more information on why the ... The package execution fa... The step
> failed.
>
> Am I missing something here?
> Thank you in advavnce for your responses.
>
> Chai
>|||Tibor:
Yes, I tried what you suggested and still received the same error.
Chai
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
> Did you add that Windows account to the SQL Server you try to access?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message
> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" Description: SSIS Error Code
>> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E4D Description: "Login failed for user
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai|||Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Tibor:
> Yes, I tried what you suggested and still received the same error.
>
> Chai
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
>> Did you add that Windows account to the SQL Server you try to access?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Chai" <chai@.trs.state.il.us> wrote in message
>> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" Description: SSIS Error Code
>> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E4D Description: "Login failed for user
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai
>|||Is the SQL server agent start-up account a network account?
Especially when jobs are running across the network, use a network account
to start the SQL Server agent services.
"Tibor Karaszi" wrote:
> Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> > Tibor:
> >
> > Yes, I tried what you suggested and still received the same error.
> >
> >
> > Chai
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
> >> When I did, it faied within couple seconds & the error saying that Login
> >> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
> >> account that SQL Agent uses.
> >>
> >> Did you add that Windows account to the SQL Server you try to access?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Chai" <chai@.trs.state.il.us> wrote in message
> >> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> >> Hello:
> >>
> >> I have a SSIS package that will import data from one DB to another. I
> >> would like to set it up as a nightly job. When I did, it faied within
> >> couple seconds & the error saying that Login failed for user
> >> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
> >> uses.
> >>
> >> Message
> >> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
> >> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
> >> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
> >> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
> >> manager "SourceConnectionOLEDB" Description: SSIS Error Code
> >> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
> >> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> >> Hresult: 0x80040E4D Description: "Login failed for user
> >> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
> >> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
> >> Description: SSIS Error Code
> >> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> AcquireConnection method call to the connection manager
> >> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
> >> error messages posted before this with more information on why the ...
> >> The package execution fa... The step failed.
> >>
> >>
> >> Am I missing something here?
> >>
> >> Thank you in advavnce for your responses.
> >>
> >>
> >> Chai
> >
> >sql

how to run ssis in sql 2005

I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?? Integration Services isn't included with any edition of SQL Server 2005 Express, as far as I am aware. See http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx for further information. See the Integration and Interoperability section of the following URL, http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx, for more specific information. Andrew Watt [MVP] <vhrao@.discussions.microsoft.com> wrote in message news:fc88e4bb-cb03-4ba7-8d14-e19bd3a2fd39@.discussions.microsoft.com...I have installed SQL 2005 Express edition both advanced features and toolkit. I have to import a sql 2000 database to sql 2005. I cannot see access to ssis in either Business Intelligence development studio or sql server management studio express. Can you tell me how access and run ssis?|||SSIS is not part of SQL Express, it is included in higher editions.

How to reverse a reverse order by?

Hi,
using the statement
SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
I get the last 10 rows in the Import table as expected, but how to I reverse
these last 10 rows?
MortenUse an outer query which has an ORDER BY in the other direction.
SELECT ...
FROM
(
SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
) AS i
ORDER BY rowID ASC
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Morten Wennevik" <Morten.Wennevik@.email.adr> wrote in message
news:op.sz2pzfp2g1d8xu@.tr023.bouvet.no...
> Hi,
> using the statement
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> I get the last 10 rows in the Import table as expected, but how to I rever
se these last 10 rows?
> Morten|||Hi Morten,
hope I undertstand you right to reorder the queried rows before.
SELECT
* FROM
(
SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
) SubQuery
Order by RowID
So you want the hightest 10 Rows, but those in an ascending order,
right ?
HTH, Jens Suessmeyer.|||Well, I tried this before, but I get an error near the keyword 'ORDER'
SELECT * FROM
(
SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
)
ORDER BY RowID ASC
Morten
On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi <tibor_please.no.email_kar
aszi@.hotmail.nomail.com> wrote:

> Use an outer query which has an ORDER BY in the other direction.
> SELECT ...
> FROM
> (
> SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> ) AS i
> ORDER BY rowID ASC|||It accepts the query when I add AS i after (), but it still does not ascend
the RowIDs
On Fri, 11 Nov 2005 12:38:16 +0100, Morten Wennevik <Morten.Wennevik@.email.a
dr> wrote:

> Well, I tried this before, but I get an error near the keyword 'ORDER'
> SELECT * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> )
> ORDER BY RowID ASC
> Morten
>
> On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi <tibor_please.no.email_k
araszi@.hotmail.nomail.com> wrote:
>
>|||Yes, given RowIDs from 1-499 I want 490-499
On Fri, 11 Nov 2005 12:37:11 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> hope I undertstand you right to reorder the queried rows before.
> SELECT
> * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> ) SubQuery
> Order by RowID
> So you want the hightest 10 Rows, but those in an ascending order,
> right ?
> HTH, Jens Suessmeyer.
>|||You need to name the derived (inner) table, see the AS clause after the clos
ing bracket.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Morten Wennevik" <Morten.Wennevik@.email.adr> wrote in message
news:op.sz2ql2d7g1d8xu@.tr023.bouvet.no...
> Well, I tried this before, but I get an error near the keyword 'ORDER'
> SELECT * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> )
> ORDER BY RowID ASC
> Morten
>
> On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
>|||Using your suggestion the RowIDs still got from 499-490, which is the opposi
te of what I want
On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wennevik@.email.a
dr> wrote:

> Yes, given RowIDs from 1-499 I want 490-499
>
> On Fri, 11 Nov 2005 12:37:11 +0100, Jens <Jens@.sqlserver2005.de> wrote:
>
>|||Ok, I'm baffled, selecting * ignores the last reverse ordering, but selectin
g just RowID will give me 490-499
?
On Fri, 11 Nov 2005 12:47:07 +0100, Morten Wennevik <Morten.Wennevik@.email.a
dr> wrote:

> Using your suggestion the RowIDs still got from 499-490, which is the oppo
site of what I want
> On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wennevik@.email
.adr> wrote:
>
>