Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Monday, June 24, 2024 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Data & Imports Forums - SSIS/DTS
Forum to discuss using SQL SSIS or DTS to perform SalesLogix imports. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to SSIS/DTS | New ThreadView:  Search:  
 Author  Thread: SSIS packages scheduled in SQL Agent
Leon Gort
Posts: 127
 
SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 03 Feb 11 9:07 PM
fiogf49gjkf0d

I am moving our SLX 7.5.3 from SQL 2000 to 2005 (actually upgrading from 7.2.1 at the same time on new servers) which includes several DTS packages I am trying to convert to SSIS some of which use the SLX provider. The new SQL server is a 64 bit 2008 R2 box.


I've converted them and they actually execute fine in Integration services. I then used the configuration file method to schedule them in SQL agent, however, I cannot get them to run. If I use native SQL connections, they work. As soon as I try use the SLX provider for the connections I get an error (see below). I want to use the provider so it creates the ID's.


Appreciate if someone could point me in the right direction


Thx - Leon


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: 2011-02-04 13:54:06.78     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 


 **UPDATE** Also tried saving directly to server for access and roles but still generates an error when running - "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SERVERNAME.SLX_PROD75.admin" failed with error code 0xC0202009"

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 06 Feb 11 12:30 PM
fiogf49gjkf0d

Regarding your first error - that's a standard SSIS thing which probably arises because you have got the Protection Level of the package set to EncryptSensitiveWithUserKey.


If you change it to EncryptSensitiveWithPassword (and check the on-line help to find out why and what the implications are) you'll get past this error. I'm guessing that your native SQL connections were 'trusted' and therefore did not require sensitive information to be stored in the package.


Regarding error #2 - try copying the 'Advanced Properties' of the SLX OLEDB Connection string to the 'Advanced Properties' property of the SLX OLEDB connection manager.

[Reply][Quote]
Leon Gort
Posts: 127
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 06 Feb 11 3:32 PM
fiogf49gjkf0d

Thanks Phil - took me a while to sort the package protection out but I finally got there. I believe my issue is related to trying to run this on a 64 bit server after reading a few MS articles (http://msdn.microsoft.com/en-us/library/ms141766.aspx)


What I ended up doing is installing the DTS support package which is in the SQL 2005 feature pack and will run them as DTS packages in the agent until SLX supports 64 bit (hopefully by the time we move to SQL 2008!)

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 06 Feb 11 3:41 PM
fiogf49gjkf0d

You do have the option of running the packages in 32-bit mode on your 64-bit server. Post back if you are interested and I will provide details.


Phil

[Reply][Quote]
Leon Gort
Posts: 127
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 06 Feb 11 3:50 PM
fiogf49gjkf0d

I thought I could too but the 32 bit runtime checkbox is not available in the step setup on my install

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 07 Feb 11 2:03 AM
fiogf49gjkf0d

Just to check, have a look in the file system for


C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe


(SSIS 2008 - similar path, I imagine, in 2005)


This is the 32-bit version of the executable which runs SSIS packages.


If it's there, check here for details and syntax.


 

[Reply][Quote]
Ryan Farley
Posts: 2265
slxdeveloper.com Site Administrator
Top 10 forum poster: 2265 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 07 Feb 11 4:50 PM
fiogf49gjkf0d

Test reply (sorry Embarassed)

[Reply][Quote]
Leon Gort
Posts: 127
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 07 Feb 11 5:00 PM
fiogf49gjkf0d

It's there but I don't think the /x86 option is available in SQL 2005 - standard edition at least anyway. The 32 bit runtime check box is not there and if I add the parameter manually to the command line it fails with a "parameter not valid" error


Thx


L

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 08 Feb 11 2:29 AM
fiogf49gjkf0d

Here's something I copied from technet that may be of interest - note the ungainly way that you specifically have to specify a command line with the path to the 32-bit dtexec:





[Reply][Quote]
Leon Gort
Posts: 127
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 08 Feb 11 3:49 PM
fiogf49gjkf0d

This definitely looks like the key - thanks for all your help Phil. Wonder if Sage has any plans for moving to 64 bit anytime soon. 


At least my SSIS knowledge has increased a hell of lot this week!


Cheers - Leon

[Reply][Quote]
Phil Parkin
Posts: 819
Top 10 forum poster: 819 posts
 
Re: SSIS packages scheduled in SQL AgentYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 09 Feb 11 2:03 AM
fiogf49gjkf0d

My pleasure Leon - post back if you have any other questions and I'll do my best. Gives me something to do while waiting for SLX Web deployments Tongue out

[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 6/24/2024 9:40:15 PM