Post reply

SSIS Execute SQL Task

  • July 18, 2012 at 6:05 am

    #264012

    Hi,

    I'm trying to create a package which executes all *.sql files from a specified folder with for each file enumerator and File Connection.

    The problem is that I'm getting errors on scripts when trying to execute such a simple query as UPDATE on nvarchar(max) column. I can't find any explained issues (i.e. size limitation) when using file connection, but I'm sure that there is a problem with nvarchar(max) and the script is cut off before being sent to sql server.

    Any help would be very appreciated...

    Testing environment:

    MS SQL Server 2012

    Data Tools 2012

    Regards,

    Nikolay

  • July 18, 2012 at 12:32 pm

    #1514449

    Kindly post some logs. How are you so sure that the value is truncated?

    Will check the logs and let you know.

    - Lokesh

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 19, 2012 at 2:43 am

    #1514675

    Here is the log generated from MSDT in debug mode:

    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.

    I think the query is truncated simply because when I manually change the SET Content value on the Update clause to 'aaaaa' it succeeds..

    query simple:

    SET NOCOUNT ON

    GO

    DECLARE @NodeID BIGINT= 111111

    UPDATE Table SET Content = N''

    WHERE NodeID=@NodeID

    SET NOCOUNT OFF

    GO

    /*

    Column "Content" is nvarchar(max) and the updated value has the following characteristics:

    characters (without blanks): 278729

    words: 15120

    lines: 3432

    */

    Thanks,

    Nikolay

  • July 19, 2012 at 2:51 am

    #1514680

    I would ask something different ..

    How can I preview the statement which is executed by the "Execute SQL Task" directly from the File Connection associated with the task?

    Thanks,

    Nikolay

  • July 19, 2012 at 6:05 am

    #1514778

    Hey all,

    the cause of this issue looks like the "$(" and "($" in the text value... it works when replaced with space between them...

    Can someone explain it to me please ??

    Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

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