Error Scheduling in SSIS even after configuring the configuration file

1

I am having very difficult time trying to schedule a package in SSIS through Management Studio. I am using SQL Server 2008 R2. My package runs fine when executed directly from BIDS. However when I create the job and run it from Management Studio it throws various errors. When i put DontSaveSensitive the package does not run from BIDS but when I put as EmcryptSensitivedataWithKey it runs on BIDS but fails in Management Studio with error 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.

I have changed the package setting to DontSaveSensitive and modified configuration file to include password in connection string.

My XML looks like this:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="SEWA\120097" GeneratedFromPackageName="Payments2" GeneratedFromPackageID="{07F1FADD-D78D-4229-B71D-0E19692DE30C}" GeneratedDate="7/17/2016 8:26:14 AM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=CCBPROD;Password=RPTUSER;User ID=RPTUSER;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[Name]" ValueType="String"><ConfiguredValue>CCBPROD.RPTUSER</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[ServerName]" ValueType="String"><ConfiguredValue>CCBPROD</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBPROD.RPTUSER].Properties[UserName]" ValueType="String"><ConfiguredValue>RPTUSER</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=CCBTEST;Password=RPTUSER;User ID=RPTUSER;Provider=MSDAORA.1;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[Name]" ValueType="String"><ConfiguredValue>CCBTEST.RPTUSER</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[ServerName]" ValueType="String"><ConfiguredValue>CCBTEST</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[CCBTEST.RPTUSER].Properties[UserName]" ValueType="String"><ConfiguredValue>RPTUSER</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>2</ConfiguredValue></Configuration></DTSConfiguration>

Please help!!

sql-server-2008
asked on Stack Overflow Jul 17, 2016 by Dsouza • edited Jul 17, 2016 by user3378165

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0