SSIS: Execute Sql task with stored procedure and insert into table

• Sql_20

  SSC Veteran

  Points: 232

  More actions

  October 3, 2008 at 9:33 am

  #193641

  Hi All

  We have a Execute sql task which will insert into table from the execute stored procedure. We have the snapshotdate arguement for which we are trying to pass datetime variable generated from other execute sql task as lastruntime. But procedure didnt accept the parameter with error ' cannot change varchar to datetime'. So we changed the datetime to varchar in the Execute sql task which gives lastruntime and added a new varchar variable for datetime. Now when we try to map it for the argument with the below format, its not running..

  declare @d varchar(50);

  set @d = ?;

  insert into table

  (col1,

  col2)])

  exec SP

  @p_CallingUserName = 'y'

  , @p_SnapshotDateTime = @d

  , @p_ScenarioNameList = ' x'

  , @p_CurrencyName = '$'

  , @p_DateTimeAdjustmentMinutes = 780

  ;

  " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  parameter mapping we are passing varchardate variable

  please help to resolve this.

• sruthireddy21

  SSC Journeyman

  Points: 82

  More actions

  October 21, 2008 at 9:21 am

  #887666

  Hi I am having the same situation ,same worke for other models of ETL...did you get any solution

• sruthireddy21

  SSC Journeyman

  Points: 82

  More actions

  October 21, 2008 at 9:21 am

  #887667

  I have also tried to change the format of date..may be you should try that