SSIS SQL Server 2008 R2 - storing datetime into table after passing to stored procedure

Question

I have a variable called StartDateTime, data type DateTime, expression @[System::StartTime]. In an Execute TSQL Task, I call a stored procedure with an input parameter of type DateTime and map the variable @[User::StartDateTime] to parameter data type DBTIMESTAMP.

I get the error

invalid time format

and do not understand why.

ALTER PROCEDURE [dbo].[spSSISInsControl]
    @SourceTableName VARCHAR(100),
    @PackageName VARCHAR(100),
    @DateProcessed DATETIME,
    @RowsTotal INT,
    @RowsLoaded INT,
    @RowsNoMatch INT,
    @RowsDeleted INT = 0,
    @SourceFileName VARCHAR(100) = NULL

Does the stored procedure server have the same local settings as the SSIS server? Please post the stored procedure code. — Tab Alleman, Mar 25 '16 at 13:28
OLE DB, all settings are the same - I'm trying to run locally right now thru SSIS debug. it does not get to the point where it calls the SP. If I knew how to post a screen shot, I would include more information. — Maa421s, Mar 25 '16 at 14:51

score 2 · Accepted Answer · answered Mar 25 '16 at 15:21

In your question, you specifically mention the data type of DBTIMESTAMP, that's not what you want. Instead, specify DATE. I know, sounds like it's just the date part but this is the hell of SSIS's three different type systems.

Setup

Reducing your problem down to the fewest moving parts, I created the following stored procedure.

CREATE PROCEDURE 
    dbo.so_36208937 
(  
    @StartDateTime datetime = '2013-01-01'
)
AS
BEGIN
    SELECT @StartDateTime AS StartDateTime;
END
GO

My package is very basic

I have the execute sql tasks configured like this

Since I have the same proc running twice, I put it into a Variable called QueryProcOleOdbc with a value of EXECUTE dbo.so_36208937 ?; As you are using an OLE DB connection manager, we use the ? to specify the place where a parameter goes.

I run the Execute SQL Task twice, once with a static date value and once with a date value that is based on @[System::StartTime] Both work fine.

Biml

Biml, the Business Intelligence Markup Language, is a way of describing SSIS packages using XML. The free addon, bids helper converts biml to SSIS packages.

Fix the third line to have the connection string point to a valid location.

This biml describes an SSIS package that has two variables: StartDateTime and StaticStartDateTime. Both are of data type DateTime. The former has an Expression set to StartTime, the latter is static.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
  </Connections>
  <Packages>
    <Package Name="so_36208937" ConstraintMode="Linear">
      <Variables>
        <Variable DataType="DateTime" Name="StartDateTime" EvaluateAsExpression="true">@[System::StartTime]</Variable>
        <Variable DataType="DateTime" Name="StaticStartDateTime">2016-03-24 13:14:15.678</Variable>
        <Variable DataType="String" Name="QueryProcOleOdbc">EXECUTE dbo.so_36208937 ?;</Variable>
        <Variable DataType="DateTime" Name="result">2015-01-01<![CDATA[]]></Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL 
          ConnectionName="CM_OLE" 
          Name="SQL Use static variable">
          <VariableInput VariableName="User.QueryProcOleOdbc" />
          <Parameters>
            <Parameter DataType="DateTime" VariableName="User.StaticStartDateTime" Name="0" />
          </Parameters>
        </ExecuteSQL>

        <ExecuteSQL
          ConnectionName="CM_OLE"
          Name="SQL Use dynamic value">
          <VariableInput VariableName="User.QueryProcOleOdbc" />
          <Parameters>
            <Parameter DataType="DateTime" VariableName="User.StartDateTime" Name="0" />
          </Parameters>
        </ExecuteSQL>

      </Tasks>
    </Package>
  </Packages>
</Biml>

I should have gone to the biml class at SQL Saturday... The SSIS DATE type was key to solving my issue. SSIS has way too many data types and the SQL to SSIS conversion tables never mentioned SSIS DATE as being a valid mapping to SQL DATETIME. — Maa421s, Mar 25 '16 at 16:36

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

data type datetime is not same as timestamp. I think that is your problem. Here's a link to more info: link

edited May 23 '17 at 12:07

Community

1
1

answered Mar 24 '16 at 20:16

NonProgrammer

1,337
2
23
53

SSIS DBTIMESTAMP type translates to SQL Server DATETIME and SMALLDATETIME. I am not using SQL TIMESTAMP for anything. – Maa421s Mar 24 '16 at 20:24

SSIS SQL Server 2008 R2 - storing datetime into table after passing to stored procedure

2 Answers2

Setup

Biml