.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

using dynamic connections in Execute SQL Task

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

I want to use an Execute SQL Task to read data from a table in a database on server1 and copy that data to server2. The servers are not linked. I set the connection information for server1 in the task properties but how do I dynamically incorporate server2 into the insert as select statement? I have tried the following where @TO_SERVER is a passed in variable containing the name of server2. But it didn't work:

SET @nsql = 'INSERT INTO [' + @TO_SERVER + '].myDB..myRegionTable 
SELECT region_id,REGION_desc,create_date
FROM differentDB.dbo.myRegionTable a
WHERE a.region_id+a.region_desc+CONVERT(varchar,a.create_date,9)
 (SELECT b.region_id+b.region_desc+ CONVERT(varchar,b.create_date,9)
 FROM [' + @TO_SERVER + '].myDB..myRegionTable b
 WHERE a.region_id+a.region_desc+CONVERT(varchar,a.create_date,9)=
  b.region_id+b.region_desc+ CONVERT(varchar,b.create_date,9)

exec sp_executesql @nsql

Both the source and destination are SQL Server 2008 R2 databases. I don't want to use the dataflow task because I only want to insert into the tables where the values aren't already there.

I have a connection in Connection Managers for both server1 and server2 but do not know

View Complete Post

More Related Resource Links

Execute Package Task password issue

I have a parent and child package. Both are stored on the file system and both are password protected. My problem is that when I call the child package from the parent using execute package task, I get prompted for the child package's password. Now the PackagePassword property of the Execute Package Task is supposed to take care of that. But for some reason the password stored in the Execute Package Task is not getting passed to the child package.  I've re-entered the PackPassword property several times, but it did not help.  I even tried loading the password from a config file, but that did not make any difference.  Can anyone help?

Execute Process task - On Error (i want to override the error message)

Hi, In my package I am calling one exe to copy the files from local folders to SFTP. I am using a execute process task and I am passing the parameters like (address, username, password,....). The exe and syntax are given below. SFTPCopier.exe <<machinename>> <<username>> <<password> <<local directory path>> The problem is when the exe is failing to copy the files to SFTP due to some reason, the SSIS package is outputting the entire command in the log information (including the username & password). I dont want to write the password in the log information. I want to remove that password from the log information when an error occured in the execute process task. Can some one please tell me the way to override the message? Can I put a on error handler for that execute process task and use a script task to modify the "ErrorDescription" variable? Will that works? Thanks in advance.  

execute sql task vs data flow task

My manager has asked me to use linked server to connect to ORACLE database rather then oledb   my source db is ORACLE and my Distination is SQL server 2005 ( in 64 bit  windows server) I am planning to write this query in an execute SQL task, INSERT  dbo.MV_MAX (PERIOD                                 ,PRODUCTGROUPID                                 ,PRODUCTID                                 ,SUBPRODUCTID                                 ,HLBER_ID                                 ,BE_ID                                 ,ENTITYID                             &n

How to get values from a multi row set in case of Execute SQL task?

Hi All, I'm testing the Execute SQL task, when I set the ResultSet to Full row set an try to retrieve those set into a variable, the package runs well but I' m not able to extract what is inside the record. If I try to debug the package to explore the data. All that I recieve is _ComObject as type, System._ComObject as value, I know (if I'm not wrong) that this _ComObject is the box that contains the rows though the values the question is how to extract those variables. For example, I can say how to enumerate the results sets using a ForEach container? Thank you The complexity resides in the simplicity

update statement which i can use in execute sql task.

I am having a table in the following manner.I am having  yearmonth till 202012 and from 201005 to 202012 I am having activeflag,tablename1,tablename2,tablename3,tablename4 as zero. This table needs to be update before runing the package.Before I load data for tablename1 for 201005 I need to update that 201005 for tablename1 to 1 and activeflag to 1 and  I need to update the 201002 tablename1 to zero.and when I load data for 201006 I need to update first that active flag =1 for 201006 and tablename1 to 1 for 201006 and then Update 201003 tablename1 to zero.i.e in tablename1 at a given time i need to see only 3 (yearmonth)=1 and update the first non zero record to zero.similary i need to update other tables also.  yearmonth                      Activeflag           tablename 1       tablename2         tablename 3   tablename4 201001                             0                 &nb

SSIS Execute Process Task Give "Access is Denied" error but it is not related to access control.

Hello All - I have a very weired SSIS problem. I am using BIDS in two machines - Windows 7 Professional Edition x64 bit (development workstation) and Windows 2003 Standard Edition x32 bit SP2 (server) . The visual studio version on both the machines are Version 2.0.50727 SP2. I found that my package's ExecuteProcessTask fails with "Access is denied" error in Windows2003 but runs ok in Windows 7 dev box. I then tried to debug the package in the Windows 2003 environment itself. The package failed in debug mode too with the same message. After looking for a while I found that when I shorten the Argument length of the failing ExecuteProcessTask the task did seem to work. So in Windows2003 environment if my argument exceeds 1846 characters the ExecuteProcessTask fails with message "Access is Denied". If the argument string is less than 1846 it works ok. In Windows 7 environment there is no problem at all. It does not seem like access problem but I may be wrong. Has anyone seen this? I will appreciate any reply. Thanks, Niben  

Variables in execute sql task

Hi Guys, I'm working with Variables in the SSIS for the first time. I managed to get The Minimum Date (Variable StartDate) and The Maximum Date (Variable EndDate) from the Source Database Table. (Pic below the 1st Execute SQL Task | Get Start Date And End Date Parameters) Then I want to delete records from the Destination Database Table based on the StartDate and EndDate Variable carried over from the 1st Execute SQL Task. I debugged the package on the 2nd Execute SQL Task and I watched the variables carry over the date value that I expected. (please note the AS DateTime inside the Enter SQL Query Dialog Box should not be there I fixed this on my end) Once the debugger step into the 2nd Execute SQL Task everything run fine but the delete doesn't happen on the database! I appreciate any help on this.         Thank you  John

Execute SQL Task with XML resultset not return properly

Hello, I have a table with one column as XML data type, and it stored XML content from a XML file. In SSIS, I used execute SQL Task, and select the XML column from the table, and set resultset as XML. I then defined a variable XMLList as string, and I used this XMLList variable as resultset. When I run the package, I saw from watch window that this XMLLIst variable not returned the proper value from the table column, but with something like <ROOT><?MSSQLError HResult=\"0x80004005\" etc. Could anyone give me advice on what is wrong with my settings for resultset? Any suggestion will be appreciate!  

Implementing Transaction in SSIS Package - [Execute SQL Task] Error: Failed to acquire connection "<

I have a simple SSIS package with three "Execute SQL Tasks". I am using ADO.Net Connection to execute SPs on a DB server. When I execute this package It works fine. So far so good. Now, I need to implement transation on this package. And problem starts now onwards. When I try to execute package after setting TransationOption = Required for the Sequence container which contains all the tasks, I get following error. [Execute SQL Task] Error: Failed to acquire connection "NYCDB0008.Export". Connection may not be configured correctly or you may not have the right permissions on this connection. "NYCDB0008.Export" is the name of the ADO.Net connection. I have been hunting for any solution but all in vain. I have tried changing all DTC settings on the dev as well as Database server. Please respond if anyone has any solution. Thanks! Anand

SSIS Execute Process Task Problems

I have a command to decrypt a file that I can run from the command line and it works beautifully.  However, when I stuff it into an execute process task, it errors out every time or does nothing.   Here is the command I can run from the command line:   gpg -d --passphrase-fd 0 < c:\Dip_Fif.WUZ -o c:\Someoutputfile.in1  -r "KeyName" c:\Someinputfile.in1.pgp     I've pointed the execute process task object to the gpg.exe executable on my system and am stuffing the remainder in the arguments line.  I have also tried changing around all the timeout settings and sucess values.  I have found I can change the success value to 2 and it will show up as being green when complete, but the file doesn't decrypt.  It just in turn will throw an error on the next piece because the required file is not there.   I will probably end up writing a script to get this to work and use a script task but I really want to know why this will not work.   --Thanks--

Send multiple parameters SSIS execute process task



We are using Execute Process Task in SSIS package to execute a batch file to house keep the generated flat files which are older than a week from the archive folder.

The same batch file is used to delete the some temp files generated in the process.

As the Archive folder and temp folder paths are configurable, we are trying to pass the folder paths as parameters to batch file.

I used Expressions tab of Execute process Task Editor to map the variable to Arguments property. This is working fine. but I need to pass one more variable as an Argument.

Can some one help me how to achieve this. Thanks in advance.


Sreekanth ABS



Error on INSERT INTO (using Parameter) within Execute SQL task



I'm sure there is a simple solution to this, however to save my banging my head against a brick wall: -

Within the "Execute SQL Task Editor" I have the following; -

Parameter Mapping Tab; -

Variable Name; - System::PackageName,
Directtion; - Input,
Data Type; - NVARCHAR,
Parameter Name; - 0,
Parameter Size; - 100

General Tab; -

SQLStatement; - INSERT INTO dbo.CustomSSISLog  (System::PackageName)     VALUES (?)

When I click on the "Parse Query" button I get the following error; - "

The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.


The table exists as follows; -

USE [ErrorLog]
CREATE TABLE [dbo].[CustomSSISLog](
	[CustomSSISLogKey] [int] IDENTITY(1,1) NOT NULL,
	[PackageName] [nvarchar](100) NULL

Any help would be greatly appreciated.

Kind Regards,


If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

Calling Oracle procedure from SSIS 'Execute SQl task' is not working


Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN  PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing  'execute BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance




Execute SQL task and Save Package Configuration wizard


Hello Experts,

I am a great fan of using Package configuration in SSIS to ease migration. I am stumped when it comes to sql execute task. We have few execute sql task that requires selecting/updating across mutliple databases in the query. Currently, I am using 4 part name(server.database.schema.table) in query. Doesn't this actualy against theme of using package configuration in SSIS. If I hard code server and database name, I will have to edit each SSIS package post migration to production.

What options do I have? Can you please help this rookie?




error code:0xC0024104 source: Reporting Task for subplan- Description: the execute method on the tas


I am getting following error when running maintenance plan under schedule job. I am getting this error for maintenance cleanup task.  The job is not failing all the time but alternate weeks. I created same maintenance plan on test server(same configuration as PROD) and Maintenance cleanup task related job failed for first time and running fine from there. I don't know why it is causing failed. I checked maintenance plan history and it show successful each time. even i checked manually it deleted all the report files related to maintenance plan. History Cleanup task and Maintenance Cleanup task should not be created on same Maintenance Plan?....I know SQL Server 2005 had some problem with maintenance plan creation before SP2.

Real time progress bar with dynamic task state


I'm trying to create custom control which displays real-time state (progress bar, or percent value) during time-consuming task (no animated GIF, or static text message).

This scenarion can't be done with standard UpdateProgress control, because it displays only static progress information.

This custom control can be then used as follows:

  1. Aspx page should contain this custom progress control and a button.
  2. When user clicks button, page executes postback to the server.
  3. Server in button click handler starts performing some time-consuming task.
  4. Client installs timer with interval is set to 2000 ms.
  5. In client timer event handler should be request to the server for progress refresh.
  6. If server receives request from progress control, it checks actual task progress state, and performs client control refresh.
  7. When server terminates executing the task, it hides progress control on the client.

How can be this scenarion accomplished? How can I create such custom control?

thanks in advance

SQL Command Or Stored Procedure in Execute SQL Task


Hi all,

Which is a better way to do in Execute SQL Task : Direct SQL input or Create a stored proc in database and then use that.

In my opinion, Stored Procedure is a better and recommended way for following reasons:

  • Cached execution plan
  • More secure
  • Centralized code
  • Code reuse

Please let me know if this is not the case with SSIS Execute SQL Task.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend