Post reply

Receiving an error "0xC002F210 at Execute SQL Task" on SSIS step when creating a table

  • January 20, 2015 at 1:57 pm

    #302490

    Can someone please tell me if there is some server setting that will prevent the error I'm receiving? I appreciate any help you can provide me.

    This is the exact error I'm receiving when running the SSIS step in our production environment (it ran fine in test):

    Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "" failed with the following error: "". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    The step is a SQL task that contains the SQL code. It is creating 3 tables. Prior to each create is a statement that checks sys.objects for the existence of the table and drops it if it exists.

    Two of the tables are dropped and created just fine, but it's not liking this one. I suspect it's because of the '$' in the table name, but not sure. The two that are working don't contain that character. I'm just puzzled how it worked in test, but not production. I am running as administrator.

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'maint.Cleanup_Temp_OPM$') AND type in (N'U'))

    DROP TABLE maint.Cleanup_Temp_OPM$;

    CREATE TABLE maint.Cleanup_Temp_OPM$(

    SSN varchar(9) NULL,

    priid int NULL,

    [Total Pay] money NULL,

    [Beginning Date] datetime NULL,

    [Ending Date] datetime NULL,

    [Quarter Date] varchar(6) NULL,

    [Quarterly Wage] money NULL,

    [Program Start Date] datetime NULL,

    [Program End Date] datetime NULL,

    quarter1_before varchar(6) NULL,

    quarter2_before varchar(6) NULL,

    quarter3_before varchar(6) NULL,

    quarter1_after varchar(6) NULL,

    quarter2_after varchar(6) NULL,

    quarter3_after varchar(6) NULL,

    quarter4_after varchar(6) NULL,

    naics char(6) NULL);

    Thanks

  • January 20, 2015 at 2:07 pm

    #1771609

    Enclose the table name in square brackets

    .

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • January 20, 2015 at 2:31 pm

    #1771619

    Thanks so much for responding. You were right. Putting brackets around the table name took care of it! I'm still not sure how it worked in test, but it doesn't matter at this point since it works now.

    Thanks again for your help.

  • September 30, 2015 at 11:30 pm

    #1830628

    We had the same problem, worked in test and not Production. We fixed the issue but does anyone know what the root cause is?

    So we can change our test to match prod. We have checked all versions of SQL, drivers and operating system. We use SQL 2008 R2, enterprise

    thanks

  • November 29, 2016 at 4:29 am

    This was removed by the editor as SPAM

  • April 23, 2017 at 6:41 am

    This was removed by the editor as SPAM

  • May 12, 2017 at 5:03 pm

    #1942412

    I came accross this while I was looking for an answer to an issue i am having, basically I have seen that if the Execute SQL Task has a command text that includes "$(" then it fails, but if you have a space between the "$" and "(" it doesn't fail.  In my case it was within a string like SELECT '$(' so I couldn't escape it with brackets, but based on my best guess, if you would have put a space or something (in your case a bracket) between the $ at the end of the table name, and the ( to start the column list, then that is why it worked.  I'm trying to figure out how to notify MSFT - but go ahead try the SELECT '$(' and see if you get the same error I have been getting 

    SSDT 2015 - targetting SQL Server 2012 and 2016

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply