Solved

SSIS As/400 > SQL Server 2005

Posted on 2008-10-24
13
955 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:Navicerts
  • 7
  • 6
13 Comments
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22795799
Proxy accounts for SSIS are related to the SQL Server, not external objects like th AS400. The purpose of the proxy is to mimic the rights of a server admin for the server the SQL Server Database resides. This authority is required to execute SQL Server Agent jobs.
To connect to the AS400 you need to embed the login user and pwd in your SSIS Package as you have already done. Then, when you save the package to the Server you need to select the protection level to "Rely on server storage and roles for access control." Or if you are running the job and pointing to an SSIS Package on the File System, you need to select Encrypt all data with password. Then when you schedule the job it will ask for your password.
Hope this helps.

protection-level.bmp
0
 
LVL 7

Author Comment

by:Navicerts
ID: 22796296
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
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22796419
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.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22796485
This is the password it is prompting you for.
 

protection-level-pwd.bmp
0
 
LVL 7

Author Comment

by:Navicerts
ID: 22796701
Hmm, I'm not getting that prompt.  Trying different things now to figure out why....
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22796793
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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 7

Author Comment

by:Navicerts
ID: 22796803
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...
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22796855
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.
0
 
LVL 7

Author Comment

by:Navicerts
ID: 22797590
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
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22797807
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
0
 
LVL 7

Author Comment

by:Navicerts
ID: 22798569
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
0
 
LVL 7

Author Closing Comment

by:Navicerts
ID: 31509578
Some of the best help I have every received, I am grateful for the pics and time you took.  Amazing expert!
0
 
LVL 7

Author Comment

by:Navicerts
ID: 22810578
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
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now