Error: 0xC002F210 at Preparation SQL- Table `Query` already exists

Question

Hi everyone,

I've googled this but have come up with very few results and none of them seem to be relevant to my issue.

I'm using VS2008 with SQL Server 2005 and have used the import export wizard to retrieve data from a table and insert it into an excel file. Everything works fine the first time I run it but after that I get this error.

Error: 0xC002F210 at Preparation SQL Task 1, Execute SQL Task: Executing the query "CREATE TABLE `Query` (
`date` DateTime,
`firstname..." failed with the following error: "Table 'Query' already exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task 1

I'm rather new to SSIS and would appreciate any help with this.

Thanks

Answer 1

Check the data base.
Is there alerady a table named "Query".

---

Hope this helps !! Please close the threads once answered - Sudeep

Answer 2

hi,
as you mention that you are using Execute SQL Task and in that you are creating a table.
Boss, first time that table is not in database so it created that table in the database.
         second time, when you execute the package and trying to create the same table again in same datbase, so you are getting this error ="Table 'Query' already exists"

so second time onwords you need to drop the table first before executing the package. OR either add drop statement in Execute SQL Task.


Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 3

IF OBJECT_ID (N’dbo.[TableName]', N’U') IS NOT NULL
DROP Table [TABLENAME]

YOUR QUERY COMES HERE


Make this change and it will work if u want it that way.

Else just check and dont create the table again.

---

Hope this helps !! Please close the threads once answered - Sudeep

Answer 4

yes that is because the Table (or in other words excel sheet) exists in the excel file,
it's like asking can you  CREATE 2 tables with the same name in the same DB ? NO you can't

here you have the same situation, to by pass this is to rename the file each time to

@Filename + @ CurrentDate + ".xls"   ----> you wil get       FileName 2009-08-24 12:41:30.xls
use a a script task and variable to do that before the CREATE TABLE ......

PLEASE MODIFY MY CODE AND YOU"LL GET THE SAME RESULT



        Dim strDate As String

        strDate = DateTime.Now.ToString("yyyy-MM-dd-HHmmssff")

        Dim strDesFolderPath As String
        Dim strPathAndFileName As String
        Dim strTemp As String
        Dim strLen As Integer
        Dim strPointer As Integer
        '' Last "\" in the string from the right hand side of the string
        Dim LastSlashPosition As Integer
        Dim strFileName As String

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        strPathAndFileName = Dts.Variables("uVar_SourceConStr_DataSource").Value.ToString()

        strLen = strPathAndFileName.Length

        'Bad file name and extention   
        If strLen < 6 Then
            Dts.TaskResult = Dts.Results.Failure
        End If

        LastSlashPosition = strPathAndFileName.LastIndexOf("\")
        strPointer = strLen - (LastSlashPosition + 1)

        strFileName = Right(strPathAndFileName, strPointer)

        strLen = strFileName.IndexOf(".")
        strFileName = Left(strFileName, strLen)

        strDesFolderPath = Dts.Variables("uVar_BackupActiveFolder").Value.ToString()

        strTemp = strDesFolderPath + "\Orbit Move file - " + strDate + " - " + strFileName + ".xls"

        Dts.Variables("uVar_SourceFileNameRenamed").Value = strTemp
        '-------------------------------------------------------------------
        Dts.TaskResult = Dts.Results.Success

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 5

Hi Sudeep

that is an good idea , but some time i ran into problems with is , you  know excel its a pain , but with my experiance sometimes ti dosen't work
long story short , my customer was coping files that had the same sheet name (sheet bname are not tables) and when i was droping the Table there was nothing to drop but on the CREATING, i was getting a message that table exsists and job failed.
but anyways becasue of the funny things that excel dose sometimes it dosen't work

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 6

Ya ur correct, I did not see its an excel file that he is modifying.

---

Hope this helps !! Please close the threads once answered - Sudeep

Answer 7

Hi Nik,

Thanks for your input, this is what I now have in the script task.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
   

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Help, press F1.

    Public Sub Main()
        '
        ' Add your code here
        '
        Dim strDate As String

        strDate = DateTime.Now.ToString("yyyy-MM-dd-HHmmssff")

        Dim strDesFolderPath As String
        Dim strPathAndFileName As String
        Dim strTemp As String
        Dim strLen As Integer
        Dim strPointer As Integer
        '' Last "\" in the string from the right hand side of the string
        Dim LastSlashPosition As Integer
        Dim strFileName As String

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        strPathAndFileName = "C:\Users\me\Documents\data.xls"

        strLen = strPathAndFileName.Length

        'Bad file name and extention  
        If strLen < 6 Then
            Dts.TaskResult = ScriptResults.Failure
        End If

        LastSlashPosition = strPathAndFileName.LastIndexOf("\")
        strPointer = strLen - (LastSlashPosition + 1)

        strFileName = Right(strPathAndFileName, strPointer)

        strLen = strFileName.IndexOf(".")
        strFileName = Left(strFileName, strLen)

        strDesFolderPath = "C:\Users\me\Documents"

        strTemp = strDesFolderPath + "\" + strDate + " - " + strFileName + ".xls"

        Dts.Variables("NewFileName").Value = strTemp
        '-------------------------------------------------------------------
        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

I now go to the DestinationConnectionExcel and try to set the Excel filepath setting. I've tried setting it to @NewFileName and Dts.Variables["NewFileName").value but I get the following error message when trying to build.

Error    1    Validation error. Data Flow Task 1: Package1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Cannot update.  Database or object is read-only.".      Package1.dtsx    0    0   

Sorry if this is really basic but I have very little knowledge of SSIS at the moment.

Answer 8

Hi
one suggestion , save the          "C:\Users\me\Documents" in a variable and pass it to the Script and save that variable in your config xml or table , i had tyhis a ot tha the customer changed the path , so i had it dynamicly set in a variable so that they can change it when ever they want

beleive me it will help you alot

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 9

in your connection manager for the destination , what is the property DelayValidation set to ? it must be true

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 10

by the way is ("NewFileName") must be in the ReadWriteVariable Property NOT in the ReadOnlyVariable

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 11

That sounds like a good idea thanks, how would I go about using that variable with the Excel Connection Manager?

Answer 12

You wil be looping through folders to find excel files in a FLC (For Loop Container) you will be using a SCR (Script Task) in the loop to make the connection string for each Excel file
--LOOP (Files to find excel files ) 
    --- Make connection String  for each file one by one  (**)
    --- Finding all Sheets in the excel file
    ---  LOOP (Looping the sheets)
             ---   DFT transfering data to the destination (Sheet by sheet)


in the (**) you will be making the connection string and put it into a variable that looks like
e.g.  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ETL\Sample\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;"
and that variable will be used in the
Connection manager -> Properties -> Expression -> Connection string.

this is a new subject and question, please close this POST you have your answer , i'll be more that happy to answer your post you can emailme @ [e-mail]

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 13

Hi mark
can you select the SCRIPT as the answer because thats the answer to your main question , thanks
please kindly next time if you have other question altho related make a new post we just like to orgenize the posts so that other can get fast answers
thanks

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 14

Thanks you Mark for changing the answer, sorry about that

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post