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

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

pass datetime variable into execute sql task which has stored procedure

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

Hi all

I have a stored procedure something like this exec sp_name startdate, enddate. I have to run this in an ssis package.

The startdate and enddate values are loaded from a sql query, so i have declared variables (startdate,enddate) and got those values from the query using evaluateasexpression. Now, when i call the stored procedure in execute sql task it fails as the datatype from the variable is string and the stored procedure parameter (startdate,enddate) are datetime. Please help me.

The following are my settings in execute sql task editor

SQL statement : EXEC sp_name ?,?

parameter mapping

variablename: user::startdate

Direction: Input

data Type: Date

Parameter name : 0

Parameter size : -1


View Complete Post

More Related Resource Links

Can I pass a value for a output variable in a stored procedure in ADO.NET?

I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

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.


Datetime filter value pass to BCS stored procedure parameter


I have data coming from one of the stored procedures that has startdate and enddate as parameters.I have created a business data webpart tht accepts this ECT.When I give the start date and end date in this format----> 2010-02-02 00:00:00Z,I can able to get the data from the database.

               As this is not a feasible approach,I have used a two datetime filters that has calendar control attached to it,so that user can use this to pick the start date and end date.Now,my issue here is when I choose the startdate and enddate,ECT is not accepting the format of the date that these filters are passing.I have changed the filter descriptor LOBDateTimeMode to "Local" in the external content type,but still I am receiving the same error.Does any one has any solution for this??

Execute Stored Procedure via Execute SQL Task


Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:

 EXEC dbo.DimBuild ?,?

 And in the parameter mapping, I added two date user variables, both with parameter name '?'


I also tried

Execute system stored procedure

This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

how to pass dataset from one stored procedure to another stored procedure

I'm wondering how a dataset returned by a stored procedure can be passed to another stored procedure.     mark it as answer if it answered your question :)

Stored Procedure utilizing table variable

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

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




Send NULL value to a stored procedure variable


Hi all. I want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure? Cry

With Recompile and Execute as clause in SQL 2005 Stored Procedure



I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.

Stored Procedure, when to use Output paramemter vs Return variable


When would you use an output parameter vs a return variable, or vice versa? In the following simple example, I can achieve the samething using either one.

Using output parameter

create proc dbo.TestOutput (@InValue int, @OutValue int output)
set @OutValue = @InValue

declare @x int
exec TestOutput @InValue = 3, @OutValue = @x output
select @x


Using return variable:

create proc dbo.TestReturn (@InValue int)
return @InValue

declare @x int
exec @x = TestReturn @InValue = 3
select @x


As you can see, they both do the samething. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?

Pass list to stored procedure



I'm looking to pass a list into a stored procedure, stored as List<myClass>.  I've passed a table in before but I'm not sure how to pass a list - anyone help?

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