SQL 2005 + SSIS - Help

[renvilo]

Hi guys,

Ok I haven't really done SSIS. I used Visual Studio 2005 to create the package and when I run it it exports data from a SQL Database into a Access Database. This works just fine. On success/fail an email is sent to me.

Every night the records in the access database will be deleted and a new export will begin. I can't really amend any data because there's no date field to use (maybe there's a work around.. not sure)

But it's fine for me as is.

Problem that I have is:

When I run the SSIS within visual studio every works fine. When I create a JOB in SQL and use my SSIS (File system or Server) then it runs and it executes 100% according to SQL.

When I check my access database nothing has been changed. No data was deleted or added. So why doesn't the SSIS work in SQL Jobs?

Thanks

 

[Jordan_Za]

I had a similar problem, It has something to do with the "Created By" security on the SSIS package.

What i did is set up a SQL Proxy in Management Studio, and i told the job to "Run As" that proxy account! and sorted!

 

[renvilo]

Just to make sure. the Job will be on my local machine's SQL (Let's say MachineA)

This job will run to get the data from an other SQL server (Say MachineB)

And then dump it in the access database on a file server.

Will the proxy still work like that?

 

[Jordan_Za]

hmmm... It should work. (I'm presuming you are a NetworkAdmin?)

If it still fails set the proxy up on both SQL Machines, but from what I remember its more about access to the SSIS Package than running aross multiple SQL Servers.

 

[renvilo]

Well MachineA is "Local" so I have full access on this machine. MachineB where I want the data from is on a different site on the network and I have limited rights on that server because it's a different company.

I did setup a proxy and doesn't seem to make any change... :-(

 

[Jordan_Za]

Can you Query MachineB from where you are?

Maybe you need to reverse your set up.
MachineB is granted Rights by MachineA and the Job is Executed at MachineB?

 

[renvilo]

I can add the job to MachineB but that's about it. If I open my SQL and do the query (that is in the SSIS) then it works just fine (without the export to Access part.)

If I put the job on MachineB and try to run it I get this:

Xeikona,Error,0,AHS-DR1,Xeikona,(Job outcome),,The job failed. Unable to determine if the owner (AG\RVilonel) of job Xeikona has server access (reason: Could not obtain information about Windows NT group/user 'AG\RVilonel'<c/> error code 0x6e. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).,00:00:01,0,0,,,,0