DTSDataPump task failing - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTSDataPump task failing

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-29 : 12:44:58

I am trying to dynamic-ize a DTS package that imports some DBase III tables into SQL Server using Nigel Rivett's example setting Global Variables. This is the main block of code from a VB project


objPack.LoadFromSQLServer txtServer, "sa", "", , , , , txtPackName.Text
With objPack
    .GlobalVariables(3).Value = txtFile.Text 'FileName (Source Dir)
    .GlobalVariables(2).Value = txtDatabase.Text 'DatabaseName
    .GlobalVariables(1).Value = txtServer.Text 'ServerName
    ' now set all the server connections - see below for SetServer
    SetServer .GlobalVariables(1).Value, .GlobalVariables(2).Value, .GlobalVariables(3).Value
    .LogFileName = "C:\LogMMImport.txt"
    For i = 1 To .Tasks.Count
        If InStr(LCase(.Tasks(i).CustomTaskID), "datapump") > 0 Then
            Set otsk = .Tasks(i).CustomTask
            otsk.SourceObjectName = "[" & txtDatabase.Text & "].[dbo]." & Mid(.Tasks(i).Name, InStrRev(.Tasks(i).Name, "["), InStrRev(.Tasks(i).Name, "]") - InStrRev(.Tasks(i).Name, "[") + 1)
            otsk.DestinationObjectName = "[" & txtDatabase.Text & "].[dbo]." & Mid(.Tasks(i).Name, InStrRev(.Tasks(i).Name, "["), InStrRev(.Tasks(i).Name, "]") - InStrRev(.Tasks(i).Name, "[") + 1)
        End If
    Next
    .Execute
End With

However, the CREATE TABLES from the Source directory specified seem to be OK, but the COPY DATA always fails, even if I reassign the SourceObjectName and DestinationObjectName as above. The log file doesn't give a reason.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 13:12:48

Do have the other database set up as a linked server?

Why not use INFORMATION_SCHEMAs to TRUNCATE and INSERT the data?

Why bother moving the data at all?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-29 : 13:34:32

Don't want these as linked servers. It's to do a conversion for many clients from one type of data (DBase III .dbf files) to SQL Server. Data needs to be imported in native table format, then scrubbed very thoroughly, and inserted into SQL Server tables. Then database will be detached from my server and sent to client and attached on their server.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-29 : 14:54:43

OK, I got it. SourceObjectName was not supposed to get assigned using 3 part naming convention. It should be table name only, e.g. Customers, no brackets, no 3 part.

Thumbs Up!!

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources