GKane


I am trying to show my employer the benefits of using SQL SERVER (2005) vs. MS Access. My problem is I can't get past a something I believe to be simple.

I created a project in Business Solutions (I tried multiple times) - The idea of the project is to download a flat file from FTP, then pull the records into a table. I can get this to work without issue *IF* I execute this process by hand.

However, I would like to execute the project via a stored procedure. I am using

exec master..xp_cmdshell 'dtexec /F C:\ssis\new_package.dtsx'

My last version of the package just has the FTP download in it (I was trying to break it down to small steps) - when executing I get an error

" Description: Failed to decrypt protected XML node "DTS:Property" 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..."

I'm not sure if that error is important or not (I guess every error is important). However, the one that bothers me the most is

" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed"

I know the password I set in the FTP function is correct, because I can run it by hand. Can someone please offer me some direction

Thank you very much.

Greg Kane




Re: New to SSIS and can't seem to get started off right.

Phil Brammer


First and foremost, the two products are not comparable... MS Access does not have a database engine. It's just glorified Excel. You shouldn't even be having that conversation with your boss. With that said...

I believe you'll have to set the package security protection level to "DontSaveSensitive." Only YOU can run the package with the default configuration, and when it's promoted to the server, it doesn't work. Some of the other guys here can help you with that.






Re: New to SSIS and can't seem to get started off right.

Rafael Salas

I think the error has to do with the protection level property of the package. If you are using the default value 'EncryptSensitiveWithUserKey' means only the author of the package can execute it. There are several methods to solve that issue. An issue one is change that property to DonSaveSensitive and the use package configurations to set the connection string of the connection managers and or other properties with sensitive information.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Search for package configurations' and you will get a lot of helpful links

 

 







Re: New to SSIS and can't seem to get started off right.

GKane

Thank you Phil. It's scary how much we use access to do tasks and I'm trying to get away (not to far) from it.



Re: New to SSIS and can't seem to get started off right.

GKane

Thank you Rafael - I'll give that shot.