| Author |
Topic |
|
kd12345
Starting Member
12 Posts |
 Posted - 2008-03-12 : 12:28:08
|
| Hi,I created a SSIS Package and now i want to run this package from SQL Agent Job. I set up the job and when i run it, it failed Job Properties:Type: SQL Server Integration Services PackageRun As: SQL Agnet Service AccountPackage Source: File SystemPackage: \\pc17917\c$\Documents and Settings\kdesai1\Desktop\SSIS\Test1\Test1\Package.dtsxError i got when i execute the job.Description: Fauiled to decrypt protected XML node "PassWord" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-03-12 10:50:54.48 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-03-12 10:50:55.07 Code: 0xC0048006 Source: Drop Table ActiveX Script Task ... The package execution fa... The step failed.I'm new to the SQL 2005 SSIS. Can you please help resolve this problem?Thanks,KD |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-12 : 12:43:02
|
| Try this:http://www.mydatabasesupport.com/forums/sqlserver-dts/182513-ssis-package-won-t-run-within-stored-procedure.html |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-12 : 23:10:37
|
| Or use windows authentication. |
|
|
|
kd12345
Starting Member
12 Posts |
Posted - 2008-03-13 : 10:50:55
|
| I'm still not able to run it from Job. I have created SSIS from my local pc and connecting to the database server using connection manager. Creatorname for this project is my name like domain_name\mylogin. Do i need to create SSIS from the database server level? There is some security that i'm messing up.Also, can someone tell me what ProtectionLevel i should change from EncryptSensitiveWithUserKey?Thanks,KD |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-13 : 13:38:33
|
| Did you check Microsoft articles i sent you reagarding this? |
|
|
|
kd12345
Starting Member
12 Posts |
Posted - 2008-03-13 : 15:27:14
|
| Yes. I read the article. I changed the ProtectionLevel to DontSaveSensitive, EncryptAllwithUserKey but didnt work. I also build the project. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-13 : 23:10:37
|
| Did you try connect to sql server with windows authentication? |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-03-14 : 09:31:35
|
| Try this:Import SSIS:- Save package as SQL Server- ProtectionLevel to DontSaveSensitiveSchedule job:- General > Package Source: SQL ServerMine works now after days of messing around with SSIS Stored Packages (permission issue). |
|
|
|
kd12345
Starting Member
12 Posts |
Posted - 2008-03-14 : 10:51:04
|
| Thanks for the suggestions. Can you let me know that when you save a package to SQL Server what Authenication type did you selected? also what Protection level did you selected?Thansk a lot. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-14 : 22:44:19
|
| Try windows authentication in package connection properties, that fixed the problem on my server. |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-03-17 : 08:29:50
|
| Important keys:1) Create a Windows Account2) Make account "sysadmin"3) Make account Job Owner4) Steps > Run As... = SQL Agent Service Account5) Steps > Log on to the server = Windows AuthenticationIf error, post it here... good luck! |
|
|
|
igress
Starting Member
2 Posts |
Posted - 2008-11-28 : 07:13:50
|
| Here is my problem,Please note that this is targeted only for advance SSIS developers. Description:I have 13 SQL servers whose login information is maintained at a sql server database. When the SSIS package starts I iterate through those information from a FOR EACH LOOP container and dynamically create an OLEDB connection (By binding variables to expression , connection strings etc)There is a Data Flow task inside the FOR EACH LOOP, and its task is to create that dynamic connection and count the number of rows return from a select statement and save it in a table as[ServerName],[RowCount]To make sure that the For each loop continues even after a server login failure (which in terms cause data flow task to fail) so that SSIS package will try to connect to next server from the next iteration I have correctly set PROPOGATE, MAX ERROR COUNT, ForceExecutionResult at appropriate locations. (As explained in many articles on the net). Everything works perfect when runs under VS 2008. (I mean For each loop continues even when there is a login failure to those dynamic connection)Ever thing works perfect when the package is executed directly from SQL server Package folder. (I mean For each loop continues even when there is a login failure to those dynamic connection)Everything works perfect when Agent Job runs. (Note that I have correctly set all the credentials and service accounts. Job Step’s “Run as” is correctly set with right credentials) But…….Suppose at least one login fails for those 13 SQL servers during For each loop iteration the entire Agent Job fails. The intended behavior is failing only the Data Flow task inside for each loop and continues from the next server. If the logins succeed for those 13 SQL servers even the Agent Jobs runs perfectly.Plat form:SQL server 2008 2008 BIDS.net 3.5 Please help as this is a kind of urgent project |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2009-10-05 : 09:36:30
|
| Thanks all . . . just helped fix a problem That the suppliers couldn't solve.-- RegardsTony The DBA |
|
|
|
matju
Starting Member
3 Posts |
Posted - 2010-10-04 : 07:48:50
|
| You can find solution here:http://biblog.pl/?p=94 |
|
|
|
|
SSIS package fails from SQL Agent Job