Pre-execute error in simple import/export

I am trying to copy data from a SQL Server 2000 DB to SQL Server 2005 DB using the import/export wizard in SQL Server Management Studio. The two databases are not identical with different table names and different columns but I thought I had set up all the right mappings and had set the 'Enable identity insert' option. When I ran the wizard it errored at the Pre-execute phase. I simplified the wizard down to one table and only as couple of varchar columns and this also errored in the same way. The error report is detailed below.

For reference the SQL2000(ent. edn) DB is on a windows 2000 server and the SQL2005(dev. edn) DB and the management studio are both on my WinXPSP2 workstation.

Could somebody explain why these errors have occured and more importantly how to rectify the problem?

Many thanks,
Michael.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)

Messages

Warning 0x80047076: Data Flow Task: The output column "DateAdd" (23) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

Warning 0x80047076: Data Flow Task: The output column "DateChg" (26) on output "OLE DB Source Output" (11) and component "Source - tccNewsArticles" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)

... NOTE: I have removed the rest of the warnings as they were the same as above (many of them).

- Pre-execute (Error)

Messages

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - Nrs_NewsArticles" (112) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

- Executing (Success)

- Copying to [NereusV2_1].[dbo].[Nrs_NewsArticles] (Stopped)

- Post-execute (Stopped)

- Cleanup (Success)

Messages

Information 0x4004300b: Data Flow Task: "component "Destination - Nrs_NewsArticles" (112)" wrote 0 rows.
(SQL Server Import and Export Wizard)

Michael,

this is most likely a truncation problem. Could you check the sizes of used source and destination columns?

Thanks.

Possible use or import a MySQL database in SQL Server 2005?

Hi all,
I am developing a web application and the back end is MySQL database.

Now I want to shift all the data to SQL Server 2005.

Is there any method to do it?

Thanks

Tomy

Hi,

You can try to use SQL Server Integration Services to do the import/export. Please check the following link for more information

http://msdn2.microsoft.com/en-us/library/ms141026.aspx

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Possible DTS File Import Bug

Env: SQL Server 2000 on in WIN NT 5.x
Job: import mutiple flat files into several tables daily.
Catch: one or two of the several flat files might be empty.

First thought/test:
Use [first row as fields] option for the import process.
Problem, DTS can't complete (as a package).

As an alternative, I could probably detect if a file is empty then
decide what to do with it, with VB activeX, it might be feasible,
question, VB has a command for "FileExist", how about "FileLen" or the
like for determining the length of a file?

TIA.Hi

See http://www.sqldts.com/default.aspx?292 and
http://www.sqldts.com/default.aspx?246

John

"NickName" <dadada@.rock.com> wrote in message
news:1102971903.347486.145350@.f14g2000cwb.googlegr oups.com...
> Env: SQL Server 2000 on in WIN NT 5.x
> Job: import mutiple flat files into several tables daily.
> Catch: one or two of the several flat files might be empty.
> First thought/test:
> Use [first row as fields] option for the import process.
> Problem, DTS can't complete (as a package).
> As an alternative, I could probably detect if a file is empty then
> decide what to do with it, with VB activeX, it might be feasible,
> question, VB has a command for "FileExist", how about "FileLen" or the
> like for determining the length of a file?
> TIA.|||Very helpful. Thank you.|||Very helpful. Thank you. However, activeX problem, error obj "string
C:\myDir\file1.csv" required, the code seems to be correct.

' File Size
' check file size if empty | 0 quit

Option Explicit

Function Main()

Dim oFSO
Dim oFile
Dim sSourceFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set sSourceFile = "C:\myDir\file1.csv"

Set sSourceFileV = sSourceFile.Value

Set oFile = oFSO.GetFile(sSourceFileV)

If oFile.Size > 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
End Function|||Never mind about the activeX error, the objFile.Value attribute was
unnecessary. But the "connectors" does not seem to have an option to
connect this ActiveX script with a package and making sure run the
ActiveX script first, instead of last.

TIA.|||Hi

You can use workflow to govern the order of the steps, if you had one or
more files to process then you can use the looping example
http://www.sqldts.com/default.aspx?246, although I would expect a three way
split for in the shouldILoop procedure or a second comparison step to cater
for files with size, files with no size and no files to process.

In your code sSourceFileV is not needed, use
Set oFile = oFSO.GetFile(sSourceFile)

John

"NickName" <dadada@.rock.com> wrote in message
news:1103038306.437005.86870@.f14g2000cwb.googlegro ups.com...
> Very helpful. Thank you. However, activeX problem, error obj "string
> C:\myDir\file1.csv" required, the code seems to be correct.
> ' File Size
> ' check file size if empty | 0 quit
> Option Explicit
> Function Main()
> Dim oFSO
> Dim oFile
> Dim sSourceFile
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> Set sSourceFile = "C:\myDir\file1.csv"
> Set sSourceFileV = sSourceFile.Value
> Set oFile = oFSO.GetFile(sSourceFileV)
> If oFile.Size > 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> ' Clean Up
> Set oFile = Nothing
> Set oFSO = Nothing
> End Function|||Hi

You can use workflow to determine the order of execution.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!