none
Bypass syntax error for ADO.NET connection SQL Task?

    Question

  • I have a SQL Task in my SSIS Package, that executes against an ADO.NET connection. The SQL task keeps giving me a syntax error. Now, I'm sure the command structure is correct, as it works when I take out the variables and execute from SQL Workbench (it's a PostgreSQL db). So, I suspect one of two things:

    1) SSIS is not liking the syntax of my command, which may work fine if I could fire it against the PostgreSQL db?

    2) my variable is not getting set properly?

    The SQL Task attempts to do essentially this (some private info taken out...):

    DECLARE @TableName nvarchar(max)
    DECLARE @CopyCommand nvarchar(max)
    SET @CopyCommand = 'copy ' + @TableName + ' from '//server/folder/' + @TableName + '.csv'' NULL AS ''\0'' delimiter ''|'';'
    EXEC (@CopyCommand);

    Being this is an ADO.NET, I understand that the @TableName should be the same name as the package parameter being passed in, rather than setting it as a '?'.

    Of course, the error in the message box doesn't show the exact location of the "syntax" error, so I'm not sure what I'm looking at...

    The error I receive is:

    Error 0xC002F210 at Execte SQL Task, Execute SQL Task: Executing the query "DECLARE @TableName nvarchar(max)

    DECLARE @CopyComm..." failed with the following error: "syntax error at or near "@"". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Really need help here...please. Is there a way I can get SSIS to be more informative about the error, or tell it to execute the command anyway (not sure that's viable/smart), or is there something I'm doing wrong in the script?

    mpleaf

    Monday, February 04, 2013 7:29 PM

Answers

  • The ADO.NET used the @<Variable Name> notation.

    But I think the issue is in the inability to parse the SQL on the SSIS side - Execute SQL Task.

    To work around this issue consider setting the SQL text portion thru a package variable (set to evaluate as an expression) or file instead.

    This way you do not have to map the variables, too.


    Arthur My Blog

    • Marked as answer by mpleaf Tuesday, February 05, 2013 5:39 PM
    Monday, February 04, 2013 7:47 PM
  • Hi,

    1. did you make the variable package scoped?

    2. did you set variable property evaluate as expression to true?


    Thanks, hsbal

    • Marked as answer by mpleaf Tuesday, February 05, 2013 5:38 PM
    Tuesday, February 05, 2013 3:19 AM

All replies

  • a. There's an extra apostrophe right before \\server

    It should be

    'copy ' + @TableName + ' from //server/folder/' + @TableName + '.csv'' NULL AS ''\0'' delimiter ''|'';'

    b. Where is your PostgreSQL database running? Windows? UNIX?

    If it runs on Windows and tries to import from Windows, you should use the \ "backslash", not the / "slash".

    If it runs on some UNIX box, then it won't be able to access Windows shares like that, you'll need an SMB mount point, something like /mnt/myshare/myfolder

    c. Check that your table name doesn't contain any spaces


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 04, 2013 7:44 PM
  • The ADO.NET used the @<Variable Name> notation.

    But I think the issue is in the inability to parse the SQL on the SSIS side - Execute SQL Task.

    To work around this issue consider setting the SQL text portion thru a package variable (set to evaluate as an expression) or file instead.

    This way you do not have to map the variables, too.


    Arthur My Blog

    • Marked as answer by mpleaf Tuesday, February 05, 2013 5:39 PM
    Monday, February 04, 2013 7:47 PM
  • Hi there, thanks for trying to help!

    a. actually, it is a double apostrophe to escape the fact I need an apostrophe in the file dir/name, which ends after the .csv. If I put in SSIS, and print the command, it appears correctly.

    b. The command structure is correct, as I said, it executes correctly if I take out the variables and execute the command within SQL Workbench.

    c. No spaces. Table name is "ADJUSTS".

    Monday, February 04, 2013 8:54 PM
  • Hi,

    On your Execute SQL task, what is the value of the following parameter

    BypassPrepare

    Please, try setting it to true (so SSIS doesn't try to parse your command, just sends the command "as is" to your DB engine)


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, February 04, 2013 8:57 PM
  • Arthur, I think you've nailed it on the head. I think the SSIS is stopping it as not "valid" SQL command (parsing), although it is valid for the ADO.NET connection. Do you have a link or can you offer a "quick" tutorial for doing this as you suggest? Not sure how I would do this, to include the variable values, without mapping them?

    Thanks in advance!

    mpleaf

    Monday, February 04, 2013 9:03 PM
  • Just double checked, BypassPrepare is set to True already. :( I've also set DelayValidation to True.

    Monday, February 04, 2013 9:06 PM
  • It is basically seen here (the link above has no images):


    Arthur My Blog

    Monday, February 04, 2013 9:12 PM
  • If I follow...you are suggesting putting the entire command into an expression in a SQL Task, and then have another SQL Task follow that, to execute?

    mpleaf

    Monday, February 04, 2013 9:22 PM
  • Yes, the entire SQL command to be placed into a variable which is set to evaluate as an expression.

    And then yes, set the Execute SQL task consume it


    Arthur My Blog

    Monday, February 04, 2013 9:24 PM
  • Two tasks or one? I was thinking you were saying:

    1) SQL task to build the entire statement as a variable

    2) SQL task to execute the "variable" statement.

    Or, can those both be done in one task?

    mpleaf

    Monday, February 04, 2013 9:28 PM
  • Okay, I got the task to evaluate the expression, that builds the statement just fine! Whew! Thanks for that! Now, the next issue, is while trying to debug and watch this query build/execute, I am getting an error:

    Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "copy ADJUSTS from '//server/folder/ADJUSTS.csv' NULL AS '\0' delimiter '|';" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    The command itself looks good! Might save me from using the EXEC portion, but I can always add that in again if needed. Right now, I can't get the command written to the variable?

    mpleaf

    Monday, February 04, 2013 10:30 PM
  • I took out my breakpoints in case they were causing issues, and no change. It "seems" like the SQL task is complaining that it can't read the variable value while the same task is creating it?

    mpleaf

    Monday, February 04, 2013 11:44 PM
  • Hi,

    1. did you make the variable package scoped?

    2. did you set variable property evaluate as expression to true?


    Thanks, hsbal

    • Marked as answer by mpleaf Tuesday, February 05, 2013 5:38 PM
    Tuesday, February 05, 2013 3:19 AM
  • Thanks for jumping in!

    1) I verified that the variables are all scoped to the entire package. At first, they were scoped to the container, but I changed all of that yesterday, so that all of my variables are scoped to the entire package.

    2) I had not done this. For the life of me, I struggled with where this setting was. I just now opened the properties window and changed this to "True", and ensured that the expression was the expression to build the query. Odd to me that you can't do that when building the expression within the SQL task, but anyway...

    Retesting...

    Tuesday, February 05, 2013 5:28 PM
  • Okay, I can now see that the variable has a value in it that matches what the actual SQL statement should be, but I am still getting the error message that the variable can't be locked for read access. Maybe I should remove from the SQL task the expression builder, as it seems to be building outside now...

    Just tried that, and IT WORKED! Taking the build of the query outside of the task, and having it in the variable options themselves solved this problem "the rest of the way", and my project is working beautifully!

    Thanks so much Arthur, AND Harry!

    mpleaf

    Tuesday, February 05, 2013 5:38 PM
  • Two tasks or one? I was thinking you were saying:

    1) SQL task to build the entire statement as a variable

    2) SQL task to execute the "variable" statement.

    Or, can those both be done in one task?

    mpleaf

    You can place all the SQL [commands] into one variable and execute. (re #1)

    You prepare the package variable and then it gets consumed by the Execute SQL Task.


    Arthur My Blog

    Tuesday, February 05, 2013 5:39 PM
  • Are you using this variable in multiple places? You then may need for each.


    Arthur My Blog

    Tuesday, February 05, 2013 5:44 PM
  • I have two variables that I use in multiple places, but this particular one is only used in one container, to execute the copy within a foreach loop. In any case, all my variables being scoped to the entire package, seems to be working just great.

    mpleaf

    Tuesday, February 05, 2013 6:36 PM