[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

"Invalid Time Format" Stored Procedure in SSIS

Posted on 2008-09-29
4
Medium Priority
?
2,261 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 400 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 600 total points
ID: 22618349
no.. use DATE and open SQL Profiler to see which parameter goes to SQL
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

656 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