we have a ssis package which imports data from another server (from .csv files) and sends to a database.
it works from the ssis package (even when csv files are on another server).
it works from a ssis package when the files are on the same server as the SQL Server Agent job.
But when executed from the SQL Server Agent job and the .csv files are on the different server - the job says it was a success but when i look in the table - no data is there?
we have even set up a proxy and a credential and tried running the package through that, but still no luck.
When a job runs an SSIS package, the job will report success unless the package fails. It is possible for a component in the SSIS package to error and stop the job but not "fail" the package. In fact, I believe that is the default behavior. So that
would explain why your job is reporting success.
As to why no data is transferring, based on the description you gave, my best guess is that the package is unable to find the files on the different server. In fact, I'm not even sure that importing files that are on a different server is even possible.
You might need to first move the files to your SQL Server, and then import them. That's the way we do it.
sypa
Member
139 Points
177 Posts
ssis package run via a proxy - says success, but data is not imported across
Dec 04, 2012 04:18 PM|LINK
we have a ssis package which imports data from another server (from .csv files) and sends to a database.
it works from the ssis package (even when csv files are on another server).
it works from a ssis package when the files are on the same server as the SQL Server Agent job.
But when executed from the SQL Server Agent job and the .csv files are on the different server - the job says it was a success but when i look in the table - no data is there?
we have even set up a proxy and a credential and tried running the package through that, but still no luck.
any ideas?
thanks.
TabAlleman
All-Star
15571 Points
2700 Posts
Re: ssis package run via a proxy - says success, but data is not imported across
Dec 04, 2012 05:21 PM|LINK
When a job runs an SSIS package, the job will report success unless the package fails. It is possible for a component in the SSIS package to error and stop the job but not "fail" the package. In fact, I believe that is the default behavior. So that would explain why your job is reporting success.
As to why no data is transferring, based on the description you gave, my best guess is that the package is unable to find the files on the different server. In fact, I'm not even sure that importing files that are on a different server is even possible. You might need to first move the files to your SQL Server, and then import them. That's the way we do it.
sypa
Member
139 Points
177 Posts
Re: ssis package run via a proxy - says success, but data is not imported across
Dec 06, 2012 09:19 AM|LINK
sounds very plausible,
I will stick to using files on the same server.
just need to figure out how to import them there on a daily basis to update the files now.
thanks.