Execute SQL Task speed

I've created a SSIS package, in a sql 2005 instance, that uses an Execute SQL Task" to call a stored proc as its last step. When run from BIDS, the last step takes about 2 to 3 minutes, consistently. When I run the exact same query from Management Studio (either via exec <spname> or by copying the sp's t-sql code into a query window) it consistently takes about 1 minute. I've run sevral test and these number are quite reproducible.

Any ideas to account for the "slowness" of the Execute SQL Task?

TIA,

Barkingdog

Hi, are you running your package in debug mode?

Try run the package without Visual Studio.

John Bocachica - Colombia

www.iquos-bi.com

|||

The dropdown box at the top of BIDs says "Development"

Here is what I have found. My package runs three control tasks. When I run all three, the Exec SQL tasks takes about 2 minutes to run but when I execute ONLY the Exec SQL task that task runs in about 1 minute!

I saved the package (in BIDS) to a .dtsx file and ran it. The whole process took about 2.5 miniutes which tells me that the Exec SQL task still took about 2 minutes.

barkingdog

|||

What are the other tasks doing? Do they use the same database connection? Are there transactions involved?

|||

The first task truncates a table called Contact. The second task imports a CSV file into a table (uses a SQL Server Destination. Does a straight copy of the data; no transformations. The file imported is on the sql 2005 server and database I'm importing into). The third task (Exec SQL Task) applies various UPDATE statements to the table populated in step 2. Steps 2 and 3 use the same sql connection.

II don't know how to tell if all the tasks belong to the same transaction. I set up three control flows in the same pane but they are not contained in any container object, if that helps at all.)

TIA,

Barkingdog

Execute SQL Task Error

Hi:

I am getting the following error message while trying to run a Execute SQL task with Variables in BIDS. My connection type is ADO.NET . My Variables defined are

Varout and Varin. Both are String type Variables. @.varout has a value set to "Category" and @.varin has a value set to "Test Category". I am using the expression

" Select * into " + @.[User::VarOut] + " FROM " + @.[User::Varin]

The expression eveluates correctly. The error I get when i run the package is:

Package Validation Error. Failed to lock Variable "Select * into TestCategory from category" 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 the execution of package and the variable is not there. The Variable name may have changed or the Variable is not being created.

Can anyone please tell me what I am doing wrong or where do I need to look at?.

Thank you

AK

Can anyone please please give me a solution. This is really urgent. I found a KB describing a issue related to Script task but not sure if it is applicable to this issue. It involves applying a hot fix. Anyone from MS please comment.|||this link might help: http://msdn2.microsoft.com/en-us/library/ms141003.aspx|||

Thanks Duane. That Article helped. I was selecting the SQLSourcetype as Variable and that was throwing errors. I changed it and my package runs like a charm!!!.

Thanks again.

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....

Execute Package Logon Failed for User....

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

I suffered a similar problem with ado .net connection. I changed ProtectionLevel property to ServerStorage and it ran fine.

BTW, what about the log?

|||

I guess i can try that, but i like the ease of deployment having the packages file based.

What log?

|||log for that dtsx. By default ssis packages haven't. You can create one from Logging menu option|||Gotcha! thanks for that tip! Is there a package level security i can use for file based packages that is basically no security? We are not concerned about encrypting the sensitive information for this package.|||

OK I logged it to event viewer. It looks like the userid i am using to logon to the sourceconnection (SQL server native client) is also the userid that the package is failing to connect to the local (CLIENT) machine. Why is it trying to logon onto the local PC? Does the user that logs onto this local server USERID1 have to be the same as the userid we are using to logon to the SQL server database USERID2? That doesnt make sense? or does it?

|||Thanks for your help. I was able to figure this out. I was using Package configuration and in it i had selected to override the userid for the sourceconnection (SQL native client). After I took that out, and made the sourceconnection use windows authentication now i am able to execute the package. BTW i also added my local user to the SQL database users. Thanks! I am learning....