title: Retrieve data from SQL Server 2005 with SQL Server 2008 SSIS Data Flow via procedure with parameter in data source
body: <p>I was trying to retrieve data from a procedure on SQL Server 2005 and populate a table on SQL Server 2008. Packet is on SQL Server 2008. I have a SQL Task which I use to get the date. The result set is set to a variable called <code>MeasurementPeriodid</code>, an int32 data type. In my data flow in an OLEDB source I have the SQL command: <code>exec [dbo].[MIS_usp_Core_Fill_Arranegement] ?</code>. On the Parameters tab, <code>@MSR_PRD_ID</code> is mapped to <code>User::MeasurementPeriodid</code>.</p>
  
  <p>The procedure accepts MSR_PRD_ID INT. In the procedure the result set is returned from table variable. Columns in this table, which correspond to columns in destination (and which is date data type), are a datetime data type.</p>
  
  <p>When I tried to preview I get this error:</p>
  
  <blockquote>
    <p>Error at Arrangement [OLE DB Source <a href="https://i.stack.imgur.com/cieIy.png" rel="nofollow noreferrer">1</a>]: SSIS Error Code
    DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code:
    0x80004005. An OLE DB record is available.  Source: "Microsoft SQL
    Server Native Client 10.0"  Hresult: 0x80004005  Description:
    "Conversion failed when converting datetime from character string.".</p>
    
    <p>Error at Arrangement [OLE DB Source <a href="https://i.stack.imgur.com/cieIy.png" rel="nofollow noreferrer">1</a>]: Unable to retrieve column
    information from the data source. Make sure your target table in the
    database is available.</p>
  </blockquote>
  
  <p>(Microsoft Visual Studio)</p>
  
  <p>When I execute the procedure from SSMS it works fine. I tried to comment out datetime columns in the query which returns a result set from table variable in the procedure, but the error is the same.</p>
  
  <p><a href="https://i.stack.imgur.com/cieIy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cieIy.png" alt="enter image description here"></a></p>
  
  <p>Procedure (not all, just begining and end):</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[MIS_usp_Core_Fill_Arranegement] 
  @MSR_PRD_ID INT
  AS
  /**
  TestSQL:
  EXEC [dbo].[MIS_usp_Core_Fill_Arranegement] @MSR_PRD_ID = 20160831
  
  **/
  BEGIN
  DECLARE @lclval nvarchar(20),       
      @datum varchar(15),
      @imalacid int
  
  SET NOCOUNT ON;
  SET FMTONLY OFF;
  
  DECLARE  @Arrangement  TABLE
  (
      [MeasurementPeriodId] datetime,
      [ArrangementId] bigint,
      [ArrangementUniqueId] nvarchar(100),
      [CustomerId] bigint,
      [CustomerUniqueId] nvarchar(100),
      [ArrangementProductId] int, -- MAPIRANJE
      [OrganizationUnitId] bigint,
      [OrganizationUnitUniqueId] [nvarchar](100),
      [NonPerformingLoanFlag] bit,
      [ActivationDate] datetime,
      [ArrangementTypeUniqueId] nvarchar(100),
      [LastRenewalDate] datetime,
      [NumberOfIssues] int, -- BROJ IZDATIH KARTICA
      [ExpirationDate] datetime, -- EST_END_DT
      [CancelationDate] datetime, -- END_DT
      [CurrencyId] int,
      [CurrencyTypeId] int,
      [TermTypeId] int
  )
  
  select --[MeasurementPeriodId],
      [ArrangementId],
      [ArrangementUniqueId],
      [CustomerId],
      [CustomerUniqueId],
      [ArrangementProductId], -- MAPIRANJE
      [OrganizationUnitId],
      [OrganizationUnitUniqueId],
      [NonPerformingLoanFlag],
      --[ActivationDate],
      [ArrangementTypeUniqueId],
      --[LastRenewalDate],
      [NumberOfIssues], -- BROJ IZDATIH KARTICA
      --[ExpirationDate], -- EST_END_DT
      --[CancelationDate], -- END_DT
      [CurrencyId],
      [CurrencyTypeId],
      [TermTypeId]
  from @Arrangement
  </code></pre>