Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2417
  • Last Modified:

"Invalid Time Format" Stored Procedure in SSIS

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
k_rasuri
Asked:
k_rasuri
  • 2
2 Solutions
 
omic_adminCommented:
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
 
PedroCGDCommented:
Dear Friend,
You have the DATE format! Check attached image
Regards!

Pedro
www.pedrocgd.blogspot.com
SQLTask2.JPG
SQLTask1.JPG
0
 
k_rasuriAuthor Commented:
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
 
PedroCGDCommented:
no.. use DATE and open SQL Profiler to see which parameter goes to SQL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now