none
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
    Thursday, August 27, 2009 4:28 PM

Answers

  • 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
    Thursday, August 27, 2009 5:26 PM

All replies

  • Check the data base.
    Is there alerady a table named "Query".
    Hope this helps !! Please close the threads once answered - Sudeep
    Thursday, August 27, 2009 5:18 PM
  • 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
    Thursday, August 27, 2009 5:19 PM
  • 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
    Thursday, August 27, 2009 5:26 PM
  • 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
    Thursday, August 27, 2009 5:26 PM
  • 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
    Thursday, August 27, 2009 5:31 PM
  • 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
    Thursday, August 27, 2009 5:36 PM
  • 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.
    Friday, August 28, 2009 1:44 PM
  • 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
    Friday, August 28, 2009 1:58 PM
  • 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
    Friday, August 28, 2009 2:00 PM
  • 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
    Friday, August 28, 2009 2:02 PM
  • That sounds like a good idea thanks, how would I go about using that variable with the Excel Connection Manager?
    Friday, August 28, 2009 2:39 PM
  • 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 @ SNikkhah@Live.ca
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    • Proposed as answer by Nik - Shahriar Nikkhah Friday, August 28, 2009 2:55 PM
    • Marked as answer by markwt Friday, August 28, 2009 3:21 PM
    • Unmarked as answer by markwt Friday, August 28, 2009 3:29 PM
    Friday, August 28, 2009 2:52 PM
  • 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
    Friday, August 28, 2009 3:24 PM
  • 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
    Friday, August 28, 2009 3:33 PM