Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
earthdog
Starting Member
3 Posts |
 Posted - 2009-08-10 : 11:49:18
|
| Hi!We are starting a BI project with MS SQL 2008 + Cognos 8.4..There are 3 remote AS/400 DB2s as sources for the Data Warehouse.the problem is that the speed of the transfer is not acceptable for us as it is now. We are are building SSIS packages for the transfer. from the local AS400 the speed is ok using OLE DB provider for DB2. But from the other systems it is not.My questions is now what alternate way to choose in order to get the data from the remote systems?I am open to any ideas. I understand that it is not SQL2008s' fault but our VPN connectivity speed, nevertheless i must try to find alternate solution that is professional of course....Elias |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-10 : 11:58:43
|
| I have a similar issue. My solution was to use the native AS/400 export tools to export data to text files, zip them up, and then copy them to a local SQL Server where I reverse the process. I don't use SSIS though, it's all command-line utilities. |
 |
|
|
earthdog
Starting Member
3 Posts |
Posted - 2009-08-10 : 12:35:36
|
quote:
Originally posted by robvolk
I have a similar issue. My solution was to use the native AS/400 export tools to export data to text files, zip them up, and then copy them to a local SQL Server where I reverse the process. I don't use SSIS though, it's all command-line utilities.
It sounds very nice to me..i dont mind getting "dirty" with as/400, can you elaborate on how you did it?I mean how did you automate it?I can think of a proccess like this:-Export the data automatically. In csv?-Zip them-Transfer the zip files (ftp?)-use SSIS packages locally from zipped files to input them to my SQL2008how can i reach it? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-10 : 15:33:13
|
| I have the iSeries tools, if you look in the help file you should find details on the RTOPCB utility. This will export data from an AS/400 to a PC file. It's similar to the bcp utility in SQL Server. There's also a Windows utility called "Data Transfer from iSeries Server" that has a GUI you can use to set up the transfer config file (similar to bcp format files).Once you use the GUI to create the DTF and FDF transfer files, you can use RTOPCB to connect to the AS/400 and download the data to a file. From there, you can use any command-line zip utility (I use 7zip) to zip them up, and then FTP or Robocopy to copy them to your destination. |
|
|
|
earthdog
Starting Member
3 Posts |
Posted - 2009-08-10 : 16:33:07
|
quote:
Originally posted by robvolk
I have the iSeries tools, if you look in the help file you should find details on the RTOPCB utility. This will export data from an AS/400 to a PC file. It's similar to the bcp utility in SQL Server. There's also a Windows utility called "Data Transfer from iSeries Server" that has a GUI you can use to set up the transfer config file (similar to bcp format files).Once you use the GUI to create the DTF and FDF transfer files, you can use RTOPCB to connect to the AS/400 and download the data to a file. From there, you can use any command-line zip utility (I use 7zip) to zip them up, and then FTP or Robocopy to copy them to your destination.
yeap, propably the solution.thank you very much, i ll try to test it in the morning.Elias |
|
|
|
|
|
|
|
|
Data Transfer low speed over VPN. What next?