RE: Microsoft Integration Services password -- MIDRANGE-L

Stored procedure on the i? I'm having trouble envisioning the process. (Also have to figure out how a stored procedure fits into our change management...)

I can comprehend accessing a database, running a query, processing results. (Select * from branches; read result set; do something for each branch.)

What I'm trying to do is dump a table from one database to another. Maybe 300,000 records today; maybe a million next month.

Can I use a result set from a query against the i database as the source of an insert into MS SQL?

I browsed through Scott's http://www.scottklement.com/presentations/External%20Databases%20from%20RPG.pdf document which was not confusing, and I do even see an example referencing localhost. I'm not sure if two different databases can be accessed at the same time, though it should be fairly easy to find out tomorrow after I get everything setup (assuming I can steal the time to do it.) If our system weren't down for backup, I'd do it now, since I can't sleep anyway. In the meantime, I'll read through the other docs on the site.
--
Sean Porterfield
________________________________________
From: midrange-l-bounces@xxxxxxxxxxxx [midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin [alan0307d@xxxxxxxxx]
Sent: Wednesday, February 20, 2013 19:41
To: Midrange Systems Technical Discussion
Subject: Re: Microsoft Integration Services password

I would definitely recommend using Scott JDBC api to call stored procedures
to extract information or update. It is running on the i and you don't have
all the intermittent stuff running with potential to fail.

On Wed, Feb 20, 2013 at 5:17 PM, Porterfield, Sean <
SPorterfield@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I've been using DTS for years on SQL Server 2005 to pull data from our IBM
i (or whatever it was called then ;) )

Now I have a new project on SQL Server 2008, so I used Integration
Services to pull the data. I automate it from a batch job when the data is
ready, calling RUNRMTCMD to DTEXEC on the Windows box.

Whenever the Windows box is rebooted, I have to log on as the user listed
in the RUNRMTCMD, start the data transfer process, and key the IBM i user
password in the normal System i Access password dialog. It then works
fine, even after logging off, until the system is rebooted again.

Does anyone know a way to make it keep the password? I typed it in the
ODBC properties when I created the IS process, but it doesn't store it.

If not, (or regardless) what would others recommend for a data transfer
process? At this time, I don't think I'm actually changing any of the
data, just using TRUNCATE TABLE and pulling in an entire *PF to replace it.

I was thinking it's probably possible and not horribly difficult to do in
PHP, but I've only ever done that for a single record update. JDBC comes
to mind as another option, though I've never installed or used it. It
seems more logical to run the entire process from the i instead of having
to bounce back and forth.
--
Sean Porterfield

This email is confidential, intended only for the named recipient(s) above
and may contain information that is privileged. If you have received this
message in error or are not the named recipient(s), please notify the
sender immediately and delete this email message from your computer as any
and all unauthorized distribution or use of this message is strictly
prohibited. Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This email is confidential, intended only for the named recipient(s) above and may contain information that is privileged. If you have received this message in error or are not the named recipient(s), please notify the sender immediately and delete this email message from your computer as any and all unauthorized distribution or use of this message is strictly prohibited. Thank you.