CODEWITHSUNDEEP
sql-serverssis
Doodle
Doodle

Reputation: 479

SSIS SQL Task not returning string date

I am trying to run the below procedure from SSIS using SQL Task.

ALTER PROCEDURE [dbo].[sp_Previous_Load_Dt_Tm]
AS
BEGIN

SELECT Extract_Dt_Tm =  ISNULL(CONVERT(VARCHAR(20), MAX([Previous_Load_Date_Time]),120),'')
FROM [Test_table] WITH (NOLOCK)

END

I am returning as below

enter image description here

When I use the variable type as String in SSIS :

enter image description here

If I update the variable type to date it gives me the value:

enter image description here

Question ) I am not sure why its returning a date when I am converting the date to varchar in the procedure.

Upvotes: 0

Views: 111

Answers (2)

Doodle
Doodle

Reputation: 479

I just removed and re added the variable. And it worked!!!! Not sure what the issue was.

Upvotes: 0

Chris Mack
Chris Mack

Reputation: 5208

Is this happening at run time?

If you change the variable type to datetime at design time, and the string returned from the stored procedure is written to it during execution, this would most likely be due to SSIS performing an implicit conversion, i.e. successfully parsing a datetime value from the string.

I imagine the images you posted are at design time, with the string not having been written yet, and the datetime having defaulted to the current date and time?

Upvotes: 1

Related Questions