advicce needed, which task to choose in DTS package

Hi guys, I need to bits of advice.

I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script

The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.

many thanks

SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?

If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.

HTH!

|||

Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?

many thanks

|||

I think you'd need a DTS package with the following flow:

SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task

Your T-SQL Task could be an SQL stored procedure which did something like the following:

Code Snippet

CREATE PROC AddRecords
AS

UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2

FROM DstTable Dst

INNER JOIN HoldingTable Hld

ON Dst.Col4 = Hld.Col4

INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld

WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)

DELETE
FROM HoldingTable

Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.

You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.

HTH!

|||

Thanks for that.

advicce needed, which task to choose in DTS package

Hi guys, I need to bits of advice.

I get data from several tables in database A and export it into database B. In this instance both databases are SQL I use a DDQ task to do this with VB script to provide the logic to insert the record if it dosnt exist and update the record if it does. What I would like to know is if there is a more effcient task that can perform this function, something that dosnt require the vb script

The 2nd piece of advice I'd like to know is concerning getting data from an oracle database and importing it into a sql database through the use of csv files. This process very slow (up to 8 hours). Is there way of using some kind of DTS package to speed things up? Or is that not possible.

many thanks

SQL2005 Integration Services would be great for this scenario. You can add a lookup task and handle your control of flow all in the one package. If you're using SQL2K, could you perhaps dump all the data into a holding table and then run a simple INSERT statement to put in the new values?

If you're taking data from Oracle, you could just create a DTS/SSIS package to query Oracle direct and pump the data into your SQL Server. I'd have thought this would be the most efficient way.

HTH!

|||

Thanks for that. In the case of using a datapump in SQL2k, do yuo have an example of some tsql I could use to insert new records and update existing ones?

many thanks

|||

I think you'd need a DTS package with the following flow:

SQLConn1.SrcTable -> DDQ -> SQLConn2.HoldingTable -> T-SQL Task

Your T-SQL Task could be an SQL stored procedure which did something like the following:

Code Snippet

CREATE PROC AddRecords
AS

UPDATE DstTable
SET Col1 = Hld.Col1, Col2 = Hld.Col2

FROM DstTable Dst

INNER JOIN HoldingTable Hld

ON Dst.Col4 = Hld.Col4

INSERT INTO DstTable
SELECT *
FROM HoldingTable Hld

WHERE NOT EXISTS (SELECT * FROM DstTable Dst WHERE Dst.Col4 = Hld.Col4)

DELETE
FROM HoldingTable

Of course, this is quite simplified. You'll need to change your JOIN and EXISTS clause to include all the columns which define a record as being unique.

You may also want to break this stored procedure up into 3 T-SQL tasks which you could run after the DDQ.

HTH!

|||

Thanks for that.

AdventureWorks Sample Database

Hi guys, I was trying to download the latest version of AdventureWorks database from codeplex website and I got the following error massage during the installation.

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 611 and earlier. A downgrade path is not supported.Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Does anybody has idea on this

Thx

Never mind. I got the answer from another post. Here is the link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2183902&SiteID=1&mode=1

peace

ADSI hetrogenous queries

Hi guys
I think I'm going insane, as I'm having a problem with something I've never
had issues with before: I'm moving a database to a new server, but the
destination server also requires the ADSI distributed server to be configured.
So far as I remember it, this is what I thought I needed to do:
1. Create the linked server definition and password:
EXEC sp_addlinkedserver 'ADSI', 'Directory Services', 'ADSDSOOBJECT',
'dsadatasource'
go
EXEC sp_addlinkedsrvlogin ADSI, 'false', NULL, 'domain\account', 'password'
go
2. Execute a query such as:
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://DC=SOME,DC=DOMAIN,DC=COM>;(&(objectClass=user)(sAMAccountName=jbloggs));ADsPath;subTree')
When I run this on my original sever (I even ran the link creation steps
creating an ADSI2 connection for test purposes) the query returns the
expected row. When I run it on the new machine, I get the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'ADSDSOOBJECT'.
OLE DB error trace [OLE/DB Provider 'ADSDSOOBJECT' ICommandText::Execute
returned 0x80040e22].
The above two steps are how I successfully configured the original server.
I thought this may have been some form of COM issue, but when I compared the
configuration of both machine (assuming I haven't missed anything obvious)
they looked the same.
The domain account I'm using for the directory authentication is the same
one as the MSSSQLSERVICE service runs under, which in addition is a local
administrator.
I'm not really expecting an answer on this as it seems terribly cryptic, but
if you have any suggestions they'd be most welcome :)
Cheers
Lain> Hi guys
> I think I'm going insane, as I'm having a problem with something I've
never
> had issues with before: I'm moving a database to a new server, but the
> destination server also requires the ADSI distributed server to be
configured.
> So far as I remember it, this is what I thought I needed to do:
> 1. Create the linked server definition and password:
> EXEC sp_addlinkedserver 'ADSI', 'Directory Services', 'ADSDSOOBJECT',
> 'dsadatasource'
> go
> EXEC sp_addlinkedsrvlogin ADSI, 'false', NULL, 'domain\account',
'password'
> go
> 2. Execute a query such as:
> SELECT *
> FROM OPENQUERY(ADSI,
>
'<LDAP://DC=SOME,DC=DOMAIN,DC=COM>;(&(objectClass=user)(sAMAccountName=jblog
gs));ADsPath;subTree')
> When I run this on my original sever (I even ran the link creation steps
> creating an ADSI2 connection for test purposes) the query returns the
> expected row. When I run it on the new machine, I get the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'ADSDSOOBJECT'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOOBJECT' ICommandText::Execute
> returned 0x80040e22].
> The above two steps are how I successfully configured the original server.
> I thought this may have been some form of COM issue, but when I compared
the
> configuration of both machine (assuming I haven't missed anything
obvious)
> they looked the same.
> The domain account I'm using for the directory authentication is the same
> one as the MSSSQLSERVICE service runs under, which in addition is a local
> administrator.
> I'm not really expecting an answer on this as it seems terribly cryptic,
but
> if you have any suggestions they'd be most welcome :)
> Cheers
> Lain
--
Check your OLE DB provider options and make sure "AllowInProcess" is turned
on:
HKLM Software\Microsoft\MSSQLServer\Providers\ADSDSOObject -
AllowInProcess = 1
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Eric. Perfecto! :)