Solved

"Invalid Time Format" Stored Procedure in SSIS

Posted on 2008-09-29
4
1,996 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
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 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