Link to home
Start Free TrialLog in
Avatar of Navicerts
NavicertsFlag for United States of America

asked on

SSIS As/400 > SQL Server 2005

Hello,

I am new with SQL Server 2005.

I am trying to transfer data from an As400 to SQL Server 2005.  I can create a package in "SQL Server Business Intelligence Development Studio" and it runs OK but I have problems when I try to create a scheduled job.

I think the problem is that it is trying to use the credentials of my SQL Server Agent when logging into the As400 (it's not the same username or password).  I have tried to pass the UN and PW into the SSIS package and I have also tried to create a proxy account on SQL Server Agent with no luck.

When I try to create the proxy account I first create the credentials with my As400 UN and PW but when I try to apply that credential to a proxt I get an error message that says....

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for ProxyAccount 'test'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+ProxyAccount&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Proxy "xxxxxx" is not a valid Windows user. (Microsoft SQL Server, Error: 14529)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=14529&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


When I try to save the UN and PW information into the SSIS package by changing the "ProtectionLevel" property in the SSIS package I get a error message that says.....

TITLE: Microsoft Visual Studio
------------------------------

Failure saving package.

------------------------------
ADDITIONAL INFORMATION:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
 (Package)

------------------------------

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
 (Package)

------------------------------
BUTTONS:

OK
------------------------------

To be honest I feel lost in all of this because I'm a developer not a sys admin, any help is appreciated.

Thank You

ASKER CERTIFIED SOLUTION
Avatar of Steve Hogg
Steve Hogg
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Navicerts

ASKER

Hello,

Thank you for your response.  I am trying to do as you instructed but still having some problems.  I have changed the package "ProtectionProperties" to "EncryptAllWithPassword" and saved the file with no problem.  after I save the file I build it, and take the "Package.dtsx" it creates and put it into a folder on my desktop.

After this is done I create a new job for SSIS and choose "File System" as the package source > I then select the "Package.dtsx" from the folder I created on my desktop.  At this point it prompts me to enter a password and I enter in the password (for my AS400 account) and the package appears to save properly.

Once this is done I right click on the package and try to run it.  I get an error message that my password is incorrect.

Thank you for your input so far!

-Navicerts
I see, the password it is prompted you for is the password you input when you save the dtsx Package with "Encrypt All With Password" - not the AS400 password which is already embedded into your Connection in the Package.
This is the password it is prompting you for.
 

protection-level-pwd.bmp
Hmm, I'm not getting that prompt.  Trying different things now to figure out why....
From the BIDS (Business Intelligence Development Studio), select
File - Save Copy of ... As
This should give you the following screen.
Next, since you are running this on the Server in SQL Server Agent SSA, make sure the server can see the path you are saving the file to. Obviously C:\Temp would not be good because that points to your PC not the Server's C: directory. I normally use UNC paths, something like this:
\\someservername\somedirectoryname\packagename.dtsx

save-copy-as-file.bmp
OK, I see where those options are now.  I had to choose "Save Copy As..." before I saw them (and for some reason "Save Copy As...." was not an option before.

I have been putting the built Package.dtsx file in a folder on my desktop, it looks like I will need to place it into one of the folders it points to - trying this out now...
Yeah, for anyone who is reading this in the future. The Save Copy ... As is not always visible. If some types of objects are selected in the SSIS Package it removes this option. For example, if you have a variable highlighted you will not see this option in the File menu.
I can't seem to figure it out :(  Maybe the end of a Friday was not the best day to try to do something new.  I am still getting the error that my password is incorrect.  Maybe you can see something wrong in these pics?  I have tried a few different locations ( I am working on the server directly ).
problem.bmp
Just to be clear, look at my comments. When you save the file, you pick a random password unlrelated to the AS400 connection.
In the package itself, you have the connection to the AS400 saved, login and pwd.
When you schedule the job and selec the file, as in your picture, it asks you for the password. This is the "random" password you picked when you saved the dtsx file.
Send me the error message you get if all of this is correct. Thanks.

problem.bmp
You have been truly VERY helpful, I'm kinda clueless on this stuff (for the moment!).  I am out for the weekend but I wanted to award the points now.  If I have a problem again when I get back I will post a link to the second question on the end of this thread to give you the heads up.

Thank you so much!

-Navicerts
Some of the best help I have every received, I am grateful for the pics and time you took.  Amazing expert!
Success!!

It worked!  I changed two things when I came in this morning from what I was trying last Friday.

1) Changed the location to C:/TestFolder/Package
2) Changed the package password to be different from the As/400 password.

One of those things was making the package fail.  Thanks again!

-Navicerts