USING SQL 2005 - In my package - I am trying to pass a SSIS user variable ("tmp_date") of DateTime Data Type as input parameter to SQL Task ("Convert Date"). I am getting error while assiging the value of input parameter ("tmp_date") to a local db variable (@tmp_date) declared in the sql task. Variable declaration: -------------------------- Name : tmp_date Scope : package1 Datatype : DateTime Value: 08/21/2009 Parameter mapping in sql task: ------------------------------------ Variable name : User::tmp_date Direction : Input DataType : DATE or DBTIME Parameter = 0 Execute SQL Task ("Convert Date"):- ------------------------------------------ declare @tmp_date smalldatetime -- (tried with datetime also) set @tmp_date = ? ------------------------------------------------------------------ If I run same package in SQL 2008. It run successfully. ------------------------------------------------------------------ ERROR MESSAGE: SSIS package "Package1.dtsx" starting. Error: 0xC002F210 at Convert Date, Execute SQL Task: Executing the query "declare @tmp_date datetime set @tmp_date = ? " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Convert Date Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package1.dtsx" finished: Failure. Already refered - (Similar Issue) http://social.msdn.microsoft.com/forums/en-us/sqlintegrationservices/thread/3DCE05D7-1651-4336-AFA2-30BBD6157C23 ---------------------------

Hello, You can Declare a variable in SSIS , lets say you added a variable in SSIS MyDate type Datetime in Execute SQL Task Declare @MYDate1 datetime set @MYDate1=? In mapping you added the SSIS variable MyDate, in Type Declare LONG. It should work fine. I just gave a try. Thanks

Thanks Shez211 for your reply. Got same error message with Input Variable declared as LONG in mapping and as DateTime in SSIS User variable list. ------------------------------------------------------------------------------ ERROR MESSAGE: SSIS package "Package1.dtsx" starting. Error: 0xC002F210 at Convert Date, Execute SQL Task: Executing the query "DECLARE @ExecDate DATETIME SET @ExecDate=?" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Convert Date SSIS package "Package1.dtsx" finished: Success.

Hello Abhi035, I again checked in SSIS 2005 and it is working fine for me. 1--Declared SSIS variable myDatetime Datetime 2--Execute sql task, made a connection to data base, in Query Declare @dt datetime set @dt=? in mapping User::MyDatetime , input,LONG,0,-1 Worked fine for me. Did you set REsults SET property? If you did to single line or something , set to None. Let me ask you , even you pass this value to the variable you declared in Execute sql task, How you are using this variable?

Shez211, Same steps was followed for creating the sql task which you have defined in your comment. - ResultSet is set to None. I can enter only four inputs (Variable Name, Direction, Data Type and Parameter Name) in parameter mapping screen but in your comment you have defined 5 inputs. Last input "-1" is extra in your inputs. I have tried same steps on two system. Version used in one of the system for developing this package is below. Microsoft Windows Professional XP, Version 2002, SP3 Microsoft Visual Studio 2005, Version 8.0.50727.42 (RTM.050727-4200) Microsoft.Net Framework Version 2.0.50727 SP2 Microsoft SQL Server Integration Services Designer Version 9.00.1399.00

Hello Abhi, The fifth one is Parameter size. I have tested package SSIS 2005 and SSIS 2008 on XP machine as well on Win2k3. Would you like to explain what you will like to do with Declared variable in T-SQL because that can't be use further in Package. It is just specific to T-SQL Task. Maybe there would be an alternative approach to your problem , Please let us know what you want to achieve? Thanks

Thanks Shez211, Same package is working for me also in SSIS 2008 on XP but not with SSIS 2005. In this task I want to convert the date into YYYYMMDD format. I want to use the declared variable in the convert statement to convert the date into String format. Yes, I can use alternate approach for the expected result - like convert date using Script task. But I want to resolve this issue(passing date type parameter to sql task) - I might need to pass the data type parameter to SQL Task in future development. Thanks

In this task I want to convert the date into YYYYMMDD format. I want to use the declared variable in the convert statement to convert the date into String format. That is the best choice. The ISO dateformat is recognized at any language settings.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

In this task I want to convert the date into YYYYMMDD format. I want to use the declared variable in the convert statement to convert the date into String format. That is the best choice. The ISO dateformat is recognized at any language settings.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

5th column was available after installing the service pack 3 and this issue was resolved after sp3 installation without any code changes. Thanks,

Hi Abhi, I'm in the same boat are you are in , can you give me some direction on how this is resolved, im new to SSIS and if you can give me step by step details please passing datetime as parameters (StartDate,endDate)

This was resolved after installing the service pack 3. Make sure you have five columns in Parameter Mapping in Execute SQL Task Editor. Before installing the service pack i have having 4 columns.