Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem
|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another " Error: 4813 Expected the text length in data stream - "

After working for over a year, one of our snapshot replication
distributions now fails with the following error:
The process could not bulk copy into table '"texttab"'.
Expected the text length in data stream for bulk copy of text, ntext,
or image data.
(Source: DESTINATION_SERVER (Data source); Error number: 4813)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
The last column of the table in error is type "text". If we lower the
number of the "BCPBatchSize" in the distribution agent profile, more
rows get copied before the error occurs - but there are too many other
very large tables in the snapshot to use that as a go-around.
As suggested in a previous post, we cannot change the table structure
because it is a third-party developed product.
When we use the -UseInProcLoader option in the dist agent, it creates
another error.
We're running SQL Server 2000 8.00.760
Thanks for any ideas.
remove the text column and replicate the remaining table. Use another method
to synchronize the text column, such as using a trigger to write to an audit
table. Have DTS read this audit table at regular intervals and then update
the text column between the publisher and subscriber. In many topologies you
will find that the text/image column is highly static.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dean" <dpichotto@.cse-insurance.com> wrote in message
news:1120071435.407646.302670@.g49g2000cwa.googlegr oups.com...
> After working for over a year, one of our snapshot replication
> distributions now fails with the following error:
> The process could not bulk copy into table '"texttab"'.
> Expected the text length in data stream for bulk copy of text, ntext,
> or image data.
> (Source: DESTINATION_SERVER (Data source); Error number: 4813)
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> The last column of the table in error is type "text". If we lower the
> number of the "BCPBatchSize" in the distribution agent profile, more
> rows get copied before the error occurs - but there are too many other
> very large tables in the snapshot to use that as a go-around.
> As suggested in a previous post, we cannot change the table structure
> because it is a third-party developed product.
> When we use the -UseInProcLoader option in the dist agent, it creates
> another error.
> We're running SQL Server 2000 8.00.760
> Thanks for any ideas.
>
|||Thanks very much for your reply.
Wow - how twisted! I wonder if using ftp for the distribution would get
around this...

Analysis Services help - Dim builds, but cube fails.

If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.

Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.

I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.

Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.My first suggestion would be to examine Brett's excellent sticky (http://www.dbforums.com/t1196943.html) with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

-PatP|||My first suggestion would be to examine Brett's excellent sticky (http://www.dbforums.com/t1196943.html) with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

-PatP

Sorry. The table isn't actually called 'MY TABLE'. I was just using that as a generic reference. The real table name is 'F_LOAN'.

Pat, do you have any specifc questions I can answer? Are you pretty familiar with AS? In a nutshell, I have a dimesion that builds just fine. When I add it to the cube, the cube fails to build. I'm guessing that I'm completely missing something very basic or I've got a bug. :eek:

I've added some screen shots of the cube, the error and the dimension. A pic is worth a thousand words, right?|||A little more info...

If I remove the dimension logic that calcs the Loan Age, the cube will build. However, I don't understand how the logic could be a problem when the dim builds fine with the logic. I would expect the dimension to fail if there was a problem with the Loan Age calc logic.|||One more pic. This query is doing what I want the dimension to do.|||Well, I think I've got it solved. I know everyone was waiting with bated breath! :D

Evidently, the cube didn't like having a dim where the dim converted the date, stored as an int in the DB, to a date. Even though the dim was working just fine, the cube somehow couldn't handle it. So, I made a view based on my dim table and did the int-to-date conversion there. Then I used the view as the source for my dim. And...everything worked just fine. I guess I was just asking too much from AS.

Thanks.