Solved

"Invalid Time Format" Stored Procedure in SSIS

Posted on 2008-09-29
4
2,027 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Quer 4 23
Parse this column 6 27
.NET Enums [Flags] & Bitwise Design Question 6 29
Stored Procedure needs owner to execute 5 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

821 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