problem with SSIS package

Hi there,

I have a weird problem with SSIS package. Package is deployed and save on SQLServer2k5. It runs no problem if I start from Integration services. It fails every time when it is scheduled as a job.

The error message is: The package execution failed.The step failed.

I will appreciate any advice.

Thanks a lot.

When it's run from a job it executes under the account sql agent is running as. Are you using a data source that is sql authentication? If so you need to set the protection level when you deploy the package to something other than encrypt with user key or else it probably won't work when run as a job. There's a couple of options but if you just want it to work set it to rely on server.|||

Thank you Brent,

Unfortunately I use only Windows security. But I will try to set protection any way.

Cheers.

Michael

|||Make sure the account sql agent is running under has access to the db's you're trying to connect to. If that's a security concern, you can add a proxy account to execute the job under.|||

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

|||

ruk_walled wrote:

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

this might help: http://support.microsoft.com/kb/918760/en-us|||

Brent,

I am having the same problem. I have thirteen SSIS packages and each one connects to an Oracle Database that uses SQL Authentication. Could you elaborate on the options you mentioned? And how would I set it to rely on the server, server Storage? (if so, doesn't work for me)

Thanks.

|||

Binh Cao wrote:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

Not sure if that was for me Binh, but that also did not work. My problem persists.

|||

What is the exact error you are receiving? It only happens when using SQL Agent?

Rafael

|||

I am receiving an 'AcquireConnection Error' that it cannot connect to the Oracle db. And yes, it only occurs when I try to schedule a job using SQL Agent, it runs in BIDS just fine.

Exact Error:

OnError,EESDCSDS522, [domain]\[login],Data Flow Task 1,{DF1DCB8E-74E7-4C5D-A639-7BAED849AAD7},{72E82E9B-7620-424E-A4E3-70E601497B54},8/10/2006 10:31:08 AM,8/10/2006 10:31:08 AM,-1071611876,0x,The AcquireConnection method call to the connection manager "[Remote Oracle Database]" failed with error code 0xC0202009.

EESDCSDS522 is the local db

I have taken out my domain\login and the name of the Remote Oracle DB for security reasons.

|||

_Phil_,

Is the box where you are running the package a 64-bit machine? SQL Agent will run the 64-bit version of DTSexec and all your 32-bit drivers won't be available for the package. The Work around is to schedule as command line that will call the 32-bit version of dtsexec.

Other thing to check is the credentials of the account used to run the SQL agent service; make sure it has the required permissions.

I hope this helps.

|||

Dear Rafael Salas,

This is the message we are getting when checking the job run history.

Message
The job failed. The Job was invoked by Schedule 4 (Package packageName). The last step to run was step 1 (Package packageName).

and on expanding it shows:

Executed as user: Servername\SYSTEM. The package execution failed. The step failed.

|||

Still facing the same problem.

The system account as well as the account specified to run the agen also fails.

thanks,

|||Try running DTEXEC from the command line under the account that you are attempting to use. That should give you a better error message.

Problem with SSIS and auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-GI have not used the "auto-increment" function but the only time I had to increment a value was when the field was a key in my db. I set the key as an Identity key and so the db takes care of the incrementing. Is this something you are able to do? Just a thought!sql

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

sql

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.

Problem with Slowly Changing Dimension-transformation

Hi,

I have a problem with the SCD-transformation in SSIS. I have a variable that holds the batchid for the current batch and I want to add this variable to the datapipline in the Data Flow Task.

This is done by using a Derived Column, so far so good. The problem occurs in the Slowly Changing Dimension transformation where I do som evaluations of changed columns BUT I don′t want to do any evaluation of the batchid-variable because then all historical batchid will be updated.

I only want to update the batchid for row that have changed in the current batch.

Is it possible to do this in any way without adding the Derived Column after the SCD transformation?

Thank for any help!!

Patrick

I am not sure I fully understand your scenario, anyway SCD by itself does not evaluate/update data, it is actually a change detection transform, meaning it detects changes on the incoming rows by comparing them with those in the dimension, and then routes them to various outputs accordingly.

From what I see you'll need derived column after SCD (e.g. to hook with SCD's ChangingAttributeOutput & HistoricalAttributeOutput), so as to update your batchID only on changed rows, it is also to add this new col info into pipeline.

let me know if I do not answer your question.

wenyang

|||When setting up the Slowly Changing dimension just don't choose set the batchIDs in the incoming and dest objects... then it won't do a lookup on them.sql

Problem with Sleep function in SSIS

Hi,

I have a script task in SSIS which uses a sleep command. sleep(600000) which waits for 10 minutes. This works fine when i execute the package through GUI. But when i execute the same with Commandline, it does not work. Is there some thing which I have not set. I have also tried the Windows API call for sleep still it behaves the same way.

Any help?

Thanks in advance

Srividya

I don't know any reason why it would not work in command line. Are you getting any error?|||did you try using the system.timers.timer class?

Problem with Simple ScriptTask

I am new to SSIS 2005. we have a text file with large amount of data. we need to transform it to a database table. Before truncating and loading the table every time the package runs, we want to check with a column(date) in db with file creation time. So for that I created two variables(A,B(value:default N)). And in the data flow, using oledb source got the column value from db and then I passed it to script component. In that script component I take the input as the value from db and checked it with file creation time if its less than the file creation time(date) then make the other variable (B) as Y else N.
Now In the control flow I have dataflow and sql task so using prescendense constraint, I kept as constraint and expression and make the expression as @.B == "Y" to make the flow from dataflow to sql script.
Dont know whats wrong but I think the variable value of B is not changing or not coming to control flow from data flow.
Please help me over here.
Thanks,I really don't understand what you're trying to do here but it sounds as though at some point you are setting the value of a variable from within a data-flow script component.
Why are you doing this? I can't think of many situations where it would be a good idea.

-Jamie|||I am a bit confused as to what you want to do here. If you want to

check a file date then you would not be doing this in the pipeline. It

would be in the Control flow.

Here is what I presume you are wanting please correct me if I am wrong

Check a File date in a DB table with the date of the file at which you

are looking

Based on what is returned you carry on with the package (Data Flow Task)

or not.

Is that right?

Here is how you would probably solve this one

ExecuteSQL task to get the date from the DB Table

Script Task to get the date on the file in question

Same script task compare your two dates

Set an external variable to a value.

Have a conditional precedence constraint that executes the Data Flow

task based on whether the variable value returned is good AND the Script

task succeeds.

Is that it?

Allan

"PavaniPolu@.discussions.microsoft.com"

wrote in message

news:17d9c02f-2e8c-46c1-b1bc-ee650b8d6b5b@.discussions.microsoft.com:

> I am new to SSIS 2005. we have a text file with large amount of data. we

> need to transform it to a database table. Before truncating and loading

> the table every time the package runs, we want to check with a

> column(date) in db with file creation time. So for that I created two

> variables(A,B(value:default N)). And in the data flow, using oledb

> source got the column value from db and then I passed it to script

> component. In that script component I take the input as the value from

> db and checked it with file creation time if its less than the file

> creation time(date) then make the other variable (B) as Y else N.

> Now In the control flow I have dataflow and sql task so using

> prescendense constraint, I kept as constraint and expression and make

> the expression as @.B == "Y" to make the flow from dataflow to sql

> script.

> Dont know whats wrong but I think the variable value of B is not

> changing or not coming to control flow from data flow.

> Please help me over here.

> Thanks,sql

Problem with recurring execution of SSIS Package

Hi All,

I have created a SSIS package which calls child packages internally. In other words there is hierarchy of packages. I am using For Loop Container with certain check conditions to execute whole set of packages repeatedly. I have to execute this set for almost 5000 times. But my problem is this set fails after every 50 and sometimes 55 cycles. Can Anybody let me know how to get solution for such a problem?

Regards,

Prash

Is there any error in the output or in your logging destination of choice ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem With Quotes in @[System::ErrorDescription] Variable

I am using an Execute T-SQL Task as a part of an OnError event Handler in my SSIS Package. When occurs an error, using the Expressions-feature, my Execute T-SQL task builds an Insert Statement to insert the @.System::ErrorDescription into a table.

"
INSERT INTO [ErrorDB].[dbo].[ISErrors]
([EventType]
,[PackageName]
,[TaskName]
,[DateDone]
,[Status]
,[Host]
,[ErrorCode]
,[ErrorDescription]
,[Comments])
VALUES
( 'OnError'
, '"+ @.[System::PackageName] + "'
, '"+ @.[System::SourceName] + "'
,getdate()
,'Failed'
,'" + @.[System::MachineName] + "'
, null
, '" + @.[System::ErrorDescription] + "'
,null
)

"

When I run the task ( not the package, only the task) everything is ok ( since the ErrorDescription variable is empty)

But when an error occurs in my package, then the T-SQL task fails giving the following error

[Execute SQL Task] Error: Executing the query " INSERT INTO [LogDB].[dbo].[ISFullMaintenanceErrors] ([EventType] ,[PackageName] ,[TaskName] ,[DateDone] ,[Status] ,[Host] ,[ErrorCode] ,[ErrorDescription] ,[Comments]) VALUES ( 'OnError' , 'Package' , 'TrialTempEx' ,getdate() ,'Failed' ,'SCYLLA' , null , @.[System::ErrorDescription] ,null ) " failed with the following error: "Must declare the scalar variable "@.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I realized that the problem is that the @.[System::ErrorDescription] contains quotes ( " ' ) and this is the reason that the insert statement fails. I tried the replace function but there was no solution

Any help would be appreciated

For this very reason I advocate using the parameter support in the Exec SQL Task, over expressions for this type of statement. This is basically a SQL injection attack, albeit benign, but by using a parameterised statement, you can protect yourself from this. The other issue you may hit is with long descriptions, you could exceed the 4000 character limit for an expression result.|||

Darren and I don't exactly see eye-to-eye on this one but I'll concede he makes a good, if slightly dramatic, point about SQL Injection

If you do want to carry on using expressions then you can just wrap the variable in a REPLACE() function.

-Jamie

Problem with package.Execute passing variables

I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.

When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.

Any ideas?

- C# -

Reference to Microsoft.SQLServer.ManagedDTS

using Microsoft.SqlServer.Dts.Runtime;

Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);

A copy of the error returned.

Source: Bulk Insert Task

Description: The result of the expression "@.[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

|||What is DestinationTableName?|||

DestinationTableName is a Property in the Bulk Insert Task Expression List.

I am trying to pass a variable from C# to SSIS that will be used in an expression (in this case, to tell the Bulk Insert where to write the data to).

my C# code where I set the variable looks like...
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();

I altered my code above to match this logic and I am getting proper returns on my variables which tells me SSIS is receiving the proper data but is not handleing it correctly.

Variables vars = DTSPack.Variables;
vars["Var1"].Value = var1.ToString();
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();

|||

BMcDowell wrote:

A copy of the error returned.

Source: Bulk Insert Task

Description: The result of the expression "@.[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

I have not used bulk insert task; but the error suggests that 'DestinationTableName' property cannot be override via expression...It may be that the problem?

|||I'm in the same position as Rafael here, but I do see that you can assign expressions to properties in the Bulk Insert Task... At least the GUI lets you use expressions for properties of the Bulk Insert task.

Are you sure the variables are scoped correctly? Have you captured the TableName variable in SSIS to ensure that you are getting the correct results?

I wouldn't think it would matter, but normally when I select a table from a drop down box, I get just the table name. When I selected a table in the Bulk Insert task, I got the fully qualified name for the table... Would that matter? So, "database.dbo.table_name" is what showed up.

Phil|||

The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions? I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.

I

|||

BMcDowell wrote:

The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions? I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.

I

There isn't an option to my knowledge, and this could be a bug. I'll have to test on my own when I get a chance later today.|||

We had the same problem when executing from a ASP.net app. The issue appears to be that SSIS only evaluates expressions when the package is initially loaded and the package never really unloads from the IIS server until it bounces. The only way that I was able to get around it was to change the web app to kick off the package execution on a new thread. That causes the package to be reloaded each time so the expressions are rebuilt correctly.

Hope that helps.

Harry

|||

Thank you Harry, Interesting approach but makes a lot of sense.I am not very familiar with threading.I did some quick research on the web and have a few ideas.I will post back the results once I figure out how to accomplish this task.

|||

DTSPack.Dispose(); did the trick.

Here is an example of the code for future reference. Thank you to everybody who helped me brainstorm on this. I feel like such a rookie at times.

Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\TestBulkInsert.dtsx", null);
DTSPack.ImportConfigurationFile("d:\\SSISPackages\\TestBulkInsert.dtsConfig");
Variables vars = DTSPack.Variables;
vars["SSISVar1"].Value = CSharpVar1.ToString();
vars["SSISVar2"].Value = CSharpVar2;
vars["SSISVar3"].Value = 100;
vars["SSISTableName"].Value = "SQLDataBase."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();
DTSPack.Dispose();

|||I spoke to soon. I have the package executing in a loop. The loop works now (on first run) however on subsequent executions I am running into the same issue. Time to continue threding research.|||Just a thought, maybe you could try creating a new application domain - loading and executing the package in the new app domain and then destroying the app domain afterwards?|||

I also tried Dispose and saw the same results. Works the first time only. I could not get the package to actually unload until the thread completed. The app domain idea should accomplish the same thing, but I believe it would be more work especially if you are running under the IIS context. Running under a new thread is pretty simple and there are a lot of examples on the web, but if you can't find one let me know and I would be happy to post one.

Harry

|||Try setting the RaiseChangedEvent property of each variable to TRUE. This cause and event to be fired that will force the recalculation of all expressions that depend on this variable whenever the variable value changes