.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

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

Posted By:      Posted Date: September 04, 2010    Points: 0   Category :Sql Server
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

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

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

Merge Join: Full Outer Join - keep key values in case of no-match

I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Passing multi values to another multi value parameter

Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

Wierd case of: Msg 213, Column name or number of supplied values does not match table definition

Hi, I'm working on several triggers (that happen after insert or update) in order to log the changes in a different table. They all follow a similar syntax and are working fine, except for this one... I've reduced the next code to the minimum that gives an error, so we can safely assume the other parts of the trigger are working fine. INSERT INTO [Adt].[WardUnitStayLog] SELECT t.* FROM [Adt].[WardUnitStay] t INNER JOIN inserted i ON i.[Id] = t.[Id]; I've used this same syntax (but on different tables) in other triggers, and these are working perfectly fine. The above query provides the next error: Column name or number of supplied values does not match table definition. I've checked both tables for differences in the columns, but to no avail... (I've checked them manually and by outerjoining the information_schema.columns) (I've also checked the order in wich these columns are defined, they match over the two tables) These are the creation scripts for the tables: CREATE TABLE [Adt].[WardUnitStay] ( [Id] [dbo].[Id] IDENTITY(1,1) NOT NULL, [UnifiedUnitStayId] [dbo].[Id] NOT NULL, [WardCd] [dbo].[Cd] NOT NULL, [_FirstAtTm] [dbo].[Dtm] NOT NULL, [_IsReservation] BIT NOT NULL, [_LastAtTm] [dbo].[Dtm] NOT NULL, [_LastBedCd] [dbo].[Cd] NULL, [_LastPhysicianUid]

Populate a Data Flow Task's variables with values from a .dtsConfig file?

I have an SSIS Project to import exchange rates from an XML file into SQL. The project works when I have certain values hard-coded into variables, such as the URL of the XML File, proxy username and passwords etc. I decided to put in a .dtsConfig file, and have it update the values of the variables in the data flow task. So, one of my SSIS Packages in this solution is called ECBDailyRates.dtsx. It contains one control flow, which is a data flow task. The data flow task is a script task which does some XPATH on an XML file, and then provides outputs to an OLE DB Destination, which in turn puts the data into a table in SQL. That much worked before I put in the dtsConfig file. There are a few variables declared inside the data flow task, such as; PricesXMLUriDaily, String ProxyAddress, String ProxyAddressPort, Int32 etc. etc. These are now being updated from the dtsConfig, which is below. It was my understanding that SSIS would run through the config, and update any of the variables as required, and then run my data tasks. However when SSIS runs my script task, none of the variables have been populated, and so it falls over... as it were :) <?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="...." GeneratedFromPackageName="dim_Institution" GeneratedFromPackageID="

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--

How to override the style values in a sharepoint workflow task form


I've created a aspx task form, and it's working as planned.  However, the window that is popping up when the task form fires is too small, and I would like to set the initial height/width to my own values.  How do I override the standard sharepoint css/style values so that I can control the size of the task window?


Rocky Sanders

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




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