Running as Job in SQL Server 2005

[dpk136]

How do you set the finished built project up to run as a job in SQL Server. I have it set up, but i keep on getting errors for it. When i run it in Visual Studio, it works...

David Kuhn
------------------

http://www.davidpkuhn.com

 

[mrdenny]

What error are you getting?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpk136]

Executed as user: XXX\XXX. The package execution failed. NOTE: The step was retried the requested number of times(1) without succeeding. The step failed.

That is it. No other reason for it. That user has full access to SQL Server.


David Kuhn
------------------

http://www.davidpkuhn.com

 

[mrdenny]

What about the packages log?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[manmaria]

FYI,To run as a job you need to bring the package in to the server. Have you done this?

HTH

 

[dpk136]

How do you get the package log? How do you bring the package into the server?



David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

I figured out the package logging. One error that stands out when i run it as a job is "Cannot drop the table 'xxx' because it does not exist or you do not have permission.

This table does exist, so this must be a permissions issue. how would i need to change permissions for this to work.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

Is there a specific username in the Connection manager or in a variable somewhere? If so, go into SSMS and look for that name under Security->Logins to check its permissions.

Odd issue that I'm running into (similar to yours) is that the SQL Logins we're using are all listed under the Database->Users and have permissions. They are not listed under Security. I can't get the packages to work with those logins and I can't add the logins under Security because "login already exists" (or some such error).

I had to create a new login under Security and use that in my Connection Manager before my package worked. I haven't had a chance to figure out what the problem is yet, but as soon as I do, I'll post it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

Doesn't anyone know the answer to this?

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

Patience please, David. SSIS is a new product and we're all still learning it.

I can't tell from your response whether you're ignoring my suggestion or if you've already tried it. Other people may not suggest additional ideas if they're not sure you've tried the stuff previously suggested. Why don't you give us a little more information...

What user are you running the package as? What permissions/roles does this user have in the database? What schemas are you using (if anything other than the default DBO)?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

I'm running as a windows user. This user has full control in the db. and i'm using the default dbo schema


David Kuhn
------------------

http://www.davidpkuhn.com

 

[manmaria]

Dropping the table is your first step in the package? or are you able to do anything before this step? Just for curiosity can you add a simple SQL task before this (like selecting a getdate() from a table) step and run? See if that step is successful.

 

[dpk136]

I ran it through to test your theory...dropping a table works, but when it is trying to recreate the table it fails. What would the permissions be that would cause this to stop happening.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[manmaria]

If you have delete privileges then I would assume you have create privileges. I think it is not the privileges issue.

So, you are able to drop the table now but not able to create? Hmm, what are the steps involved in your package?

 

[dpk136]

I did the SSIS Import and Export wizard and it Drops the table, creates the table in the new db, and then it transfers the data.

When i run it in the programming window, it works...it is only in the running as a job that it messes up

David Kuhn
------------------

http://www.davidpkuhn.com

 

[manmaria]

it" drops table, what is it? Import/export wizard?
I am sorry for asking more questions. Are you exporting data into a flat file and dropping the table and then creating the table? Is that right?

 

[dpk136]

Ok...what is happening is that SSIS drops the table in SQL Server, it(SSIS) then recreates this table based on what fields are coming from the Progress database. After that, SSIS is supposed to copy the data over.

I found out that this is where the problem is happening, in the tranfering of data. The creation of the table works, just not transferring the data.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

i have no idea what could be wrong. My boss is getting pissed and i'm frustrated. This piece is making this project overdue. If anyone has ANY ideas, even ones that seem like they wouldn't work, i'm open to suggestions.

Please Help

David Kuhn
------------------

http://www.davidpkuhn.com

 

[manmaria]

Cool Down. You are going into the wrong directions and coming back. That is good. We started with privileges then dropping the table now in transfering the data. So right information will give us the right idea. I was there with that kind of frustration before. So you are not the firstone.

Now, what is the error in transforming the data? It shouldn't be a programattic error since it was ran in interatice mode. Right?

 

[dpk136]

In the Log File Viewer i get "Executed as user: PBXREPORT\SYSTEM. The package execution failed. The step failed

in the log i get

System.Data.Odbc.OdbcException: ERROR [IM003] Specified driver could not be loaded due to system error  126 (MERANT 3.60 32-BIT Progress SQL92 v9.1D).
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

and after that i get

component "Source - Query" (1) failed validation and returned error code 0x80131937.

David Kuhn
------------------

http://www.davidpkuhn.com