Fastest Way to Transfer Data from SQL Server 7.0 to Oracle 8 Database
Trying to Do..
Transfer Data from a SQL 7 Database on NT Server via DTS job to Oracle 8iDatabase Table on another NT Server.
This Oracle 8i DB Table has no indexes.
Currently i can transfer about 8000 rows in 1 minute, i am seeking to lower this time by as much as possible
Ive also tried using ole db in dts job also, but with no significant increase in time,
Again any advice/pointers are most welcome.BCP OUT of SQL Server and then use Oracles' IMPort tool.
I guess you could get this down to about 5 seconds...|||No exp. in using Oracle's tools, but BCP is the fastest method to export rows from a database.
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Monday, March 12, 2012
Fastest way to transfer data from DB2 on host to SQL Server
Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Are these two boxes in the same location?
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533A6B9900AD9@.SQLMonster.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via SQLMonster.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Are these two boxes in the same location?
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533A6B9900AD9@.SQLMonster.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via SQLMonster.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1
Fastest way to copy tables and their indexes between servers?
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
Without fully understanding the specifics but going by what you've done so far, one other option would be to script the database objects to file, run the generated scripts on the second server then use the BCP utility BCP.EXE (bulk copy program) to copy the data over.
See http://msdn2.microsoft.com/en-us/library/aa337544.aspx for more information on how to use the BCP utility.
Regards,
Uwa.
Wednesday, March 7, 2012
Fast insert
I need to do insert a lot of data into a table and need to improve
performance. I don't think DTS is adequate since I'm not simply
copying/transforming existing data but rather computing it as I go. I think
transaction logging is an extra cost that I should avoid, but I'm already
using simple recovery mode... What are other possible optimizations?Have your appliation write each computed record to a tab delimited text file
residing on the local HD and having the same column format as the table.
Once done, bulk copy the file into the destination table. Also, it may help
to drop indexes prior to the load and re-create them afterward.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:3E700E7B-0983-4DB7-BCAF-BC862C2AEB9A@.microsoft.com...
>I need to do insert a lot of data into a table and need to improve
> performance. I don't think DTS is adequate since I'm not simply
> copying/transforming existing data but rather computing it as I go. I
> think
> transaction logging is an extra cost that I should avoid, but I'm already
> using simple recovery mode... What are other possible optimizations?
performance. I don't think DTS is adequate since I'm not simply
copying/transforming existing data but rather computing it as I go. I think
transaction logging is an extra cost that I should avoid, but I'm already
using simple recovery mode... What are other possible optimizations?Have your appliation write each computed record to a tab delimited text file
residing on the local HD and having the same column format as the table.
Once done, bulk copy the file into the destination table. Also, it may help
to drop indexes prior to the load and re-create them afterward.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:3E700E7B-0983-4DB7-BCAF-BC862C2AEB9A@.microsoft.com...
>I need to do insert a lot of data into a table and need to improve
> performance. I don't think DTS is adequate since I'm not simply
> copying/transforming existing data but rather computing it as I go. I
> think
> transaction logging is an extra cost that I should avoid, but I'm already
> using simple recovery mode... What are other possible optimizations?
Sunday, February 26, 2012
Fast BCP
Can someone tell me the conditions that should be
fulfilled for a FAST BCP to happen
when i create a DTS package and that has Transform data
Task which inserts data into a table from select query is
that done via BCP
SanjayLook in BooksOnLine under BCP - Minimally logged load.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> Can someone tell me the conditions that should be
> fulfilled for a FAST BCP to happen
> when i create a DTS package and that has Transform data
> Task which inserts data into a table from select query is
> that done via BCP
> Sanjay
>|||This is a bit confusing
It says that Target table should have 0 rows
Is this true
Also TABLOCK hint should be specified
Is this true too
Sanjay
>--Original Message--
>Look in BooksOnLine under BCP - Minimally logged load.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sanjay" <sanjayg@.hotmail.com> wrote in message
>news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
>> Can someone tell me the conditions that should be
>> fulfilled for a FAST BCP to happen
>> when i create a DTS package and that has Transform data
>> Task which inserts data into a table from select query
is
>> that done via BCP
>> Sanjay
>
>.
>|||It only needs 0 rows if there are existing indexes on the table. If the
table has indexes it must be empty to do a minimally logged load. You can
drop the indexes and recreate them afterwards though. A table lock is
needed as well. This shouldn't be an issue if your loading that many rows
in you certainly don't want other users in there.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:8be401c34577$ae5ab1f0$a401280a@.phx.gbl...
> This is a bit confusing
> It says that Target table should have 0 rows
> Is this true
> Also TABLOCK hint should be specified
> Is this true too
> Sanjay
> >--Original Message--
> >Look in BooksOnLine under BCP - Minimally logged load.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Sanjay" <sanjayg@.hotmail.com> wrote in message
> >news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> >> Can someone tell me the conditions that should be
> >> fulfilled for a FAST BCP to happen
> >>
> >> when i create a DTS package and that has Transform data
> >> Task which inserts data into a table from select query
> is
> >> that done via BCP
> >>
> >> Sanjay
> >>
> >
> >
> >.
> >
fulfilled for a FAST BCP to happen
when i create a DTS package and that has Transform data
Task which inserts data into a table from select query is
that done via BCP
SanjayLook in BooksOnLine under BCP - Minimally logged load.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> Can someone tell me the conditions that should be
> fulfilled for a FAST BCP to happen
> when i create a DTS package and that has Transform data
> Task which inserts data into a table from select query is
> that done via BCP
> Sanjay
>|||This is a bit confusing
It says that Target table should have 0 rows
Is this true
Also TABLOCK hint should be specified
Is this true too
Sanjay
>--Original Message--
>Look in BooksOnLine under BCP - Minimally logged load.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sanjay" <sanjayg@.hotmail.com> wrote in message
>news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
>> Can someone tell me the conditions that should be
>> fulfilled for a FAST BCP to happen
>> when i create a DTS package and that has Transform data
>> Task which inserts data into a table from select query
is
>> that done via BCP
>> Sanjay
>
>.
>|||It only needs 0 rows if there are existing indexes on the table. If the
table has indexes it must be empty to do a minimally logged load. You can
drop the indexes and recreate them afterwards though. A table lock is
needed as well. This shouldn't be an issue if your loading that many rows
in you certainly don't want other users in there.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:8be401c34577$ae5ab1f0$a401280a@.phx.gbl...
> This is a bit confusing
> It says that Target table should have 0 rows
> Is this true
> Also TABLOCK hint should be specified
> Is this true too
> Sanjay
> >--Original Message--
> >Look in BooksOnLine under BCP - Minimally logged load.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Sanjay" <sanjayg@.hotmail.com> wrote in message
> >news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> >> Can someone tell me the conditions that should be
> >> fulfilled for a FAST BCP to happen
> >>
> >> when i create a DTS package and that has Transform data
> >> Task which inserts data into a table from select query
> is
> >> that done via BCP
> >>
> >> Sanjay
> >>
> >
> >
> >.
> >
Friday, February 24, 2012
Failure Workflow Does Not Fire
Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure
for example when using xp_cmdshell, this will call the failure workflow, if present
declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)
the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.
Good luck
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2Are you sure the fact that no file is there will cause the step to error out?|||u need to trap errors from external programs like bat files to have the failed workflow activated. aslo not all external programs returns error code to the calling application. for bat files, u will have to set errorlevel to make the calling program understand the success/failure
for example when using xp_cmdshell, this will call the failure workflow, if present
declare @.err int
exec @.err = master..xp_cmdshell 'C:\xx.bat'
if @.err = 1
RAISERROR ('err',16,1)
the above code will not fire the failure workflow if u just execute
exec master..xp_cmdshell 'C:\xx.bat'
and even if the xx.bat is not present in folder C:\|||Thank you for your help!
cdun2|||Another option is to create an operator when you scheduled the job and an e-mail will be sent out if the job fails.
Good luck
Sunday, February 19, 2012
Failure saving package
Hello All,
I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.
After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:
TITLE: Microsoft Visual Studio
Failure saving package.
ADDITIONAL INFORMATION:
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
BUTTONS:
OK
Could someone please help me with this ?
Thanks in advance.
Thiago
Has anybody seen this issue resolved? I am experiencing the exact same issue.|||hi guys,
Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...
|||That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.
The funny thing is that a couple seem to have converted cleanly but the majority haven't.
I will be putting in a support issue with Microsoft and I will report back with any findings.
|||Everybody,
If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.
Thanks.
Regards,
Ezhilarasan Maharajan
|||This seems to have solved my problem but I have some further testing to do.
Great catch. Thanks for responding to this.
|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!Failure saving package
Hello All,
I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.
After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:
TITLE: Microsoft Visual Studio
Failure saving package.
ADDITIONAL INFORMATION:
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
BUTTONS:
OK
Could someone please help me with this ?
Thanks in advance.
Thiago
Has anybody seen this issue resolved? I am experiencing the exact same issue.|||hi guys,
Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...
|||That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.
The funny thing is that a couple seem to have converted cleanly but the majority haven't.
I will be putting in a support issue with Microsoft and I will report back with any findings.
|||Everybody,
If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.
Thanks.
Regards,
Ezhilarasan Maharajan
|||This seems to have solved my problem but I have some further testing to do.
Great catch. Thanks for responding to this.
|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!Failure saving package
Hello All,
I have a package in the SQL 2000 environment that works fine. I have migrated this DTS Package to a SSIS Package using the Visual Studio 2005.
After that, i made some changes in this package and now I'm trying to save it but I am facing with this error message below:
TITLE: Microsoft Visual Studio
Failure saving package.
ADDITIONAL INFORMATION:
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
Invalid at the top level of the document.
(Microsoft OLEDB Persistence Provider)
BUTTONS:
OK
Could someone please help me with this ?
Thanks in advance.
Thiago
Has anybody seen this issue resolved? I am experiencing the exact same issue.|||hi guys,
Probably not very helpful for you but a few months ago I suffered the same and my solution was make a DTSX from the beginning...
|||That's what I am doing at present but there are a couple of hundred of these that I need to change. If I have to build them all from scratch the project time is going to blow out considerably. The migration is not perfect (as I expected) but it does considerably speed up the conversion process.
The funny thing is that a couple seem to have converted cleanly but the majority haven't.
I will be putting in a support issue with Microsoft and I will report back with any findings.
|||Everybody,
If you have any recordset object exists/present in your migrated DTS workflow, please manually convert those variables datatype into Object (If you are not able to modify the recordset variables, please re-create the variables with Object datatype). After changing all the recordset variables, most probabily you should be able to save the migrated SSIS workflow.
Thanks.
Regards,
Ezhilarasan Maharajan
|||This seems to have solved my problem but I have some further testing to do.
Great catch. Thanks for responding to this.
|||I've finished my testing and this has defintely resolved my problem. I also updated my MS support issue with this information so hopefully a KB article and a fix in a future SP will be forthcoming.|||Thank you! It helps!
Subscribe to:
Posts (Atom)