Solved

"Invalid Time Format" Stored Procedure in SSIS

Posted on 2008-09-29
4
2,082 Views
Last Modified: 2013-11-10
Hi all,

    thanks for visiting my thread. I have EXEC sp_loaddata ?,? statement in my execute sql task. I am using the parameter mapping to provide the variables.

 

variables:           datatype                  value

StartDate           datetime                 2008-09-10 19:00:00

EndDate             datetime                2008-09-10 20:00:00

 

Paramenter Mapping:        direction          datatype                 ParameterName      ParameterSize

User::StartDate                  Input              DBTIMESTAMP        0                               -1

User::EndDate                   Input              DBTIMESTAMP        1                               -1            

 

Im using DBTIMESTAMP because i didnt find DATETIME in the drop down list. (any ideas why datetime is not included??)

 

when i removed the paramenter mapping and directly execute the stored procedure..it is working fine

 

EXEC sp_loaddata '2008-09-10 19:00:00', '2008-09-10 20:00:00'

 

when i include the paramenters im getting the error "Invalid time format" . possible failure reasons: problems with the query, "ResultSet" property not set correctly, paramenters not set correctly, or connection not established correctly

 

what could be the problem?? thanks
0
Comment
Question by:k_rasuri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Assisted Solution

by:omic_admin
omic_admin earned 100 total points
ID: 22601677
date and time format was always a huge issue in SSIS for me. You did not mention what format the import is coming from, so that would need to be resolved even before you try and have SSIS import that information. Just because it's "mapping", doesn't mean that it is doing it correctly-it is only doing what it thinks is correct.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22617232
Dear Friend,
You have the DATE format! Check attached image
Regards!

Pedro
www.pedrocgd.blogspot.com
SQLTask2.JPG
SQLTask1.JPG
0
 
LVL 8

Author Comment

by:k_rasuri
ID: 22618100
thanks pedro for the reply and attachments..but DATE will return only the date part right..not the time. In my case i want both date and time. When i set the mapping to DBTIMESTAMP some how its is not mapping properly and what...im getting the error 'invalid time format'


Thanks
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 150 total points
ID: 22618349
no.. use DATE and open SQL Profiler to see which parameter goes to SQL
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question