Advertisement

02.29.2008 at 09:09AM PST, ID: 23204326
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

SQL DTS Data Transformation - text to date/time problem
Tags: SQL DTS Data Transformation
I am transferring data from an AS400 to a SQL table via DTS.  The fields that contain date values on the AS400 are stored on the 400 in numeric format, for example 112106 for November 21st, 2006.  As part of the preparation to get the date field off the 400, I transform the field to a text field and rearrange the date so that it is formatted 20061121 and is not numeric.  I am wanting to send the fields to a column in a SQL table that is defined as DateTime.  I have created a DTS package, and in the transformation I am trying to set the conversion up so that the field can be converted FROM a string TO a datetime, but I am getting errors.  The transformation is Date Time String Transformation properties - where you can specify the source data format and I have selected yyyy-MM-dd, which is the format I have created coming from the 400.  The error message says "cannot parse input data string beginning at '19310712'.  19310712 is the date format I am building from the 400 and it looks right to me so I am not sure where the error comes in or why the DTS transformation cannot parse the string.
Anyone know a whole lot more than me about this?
thanks
Start your free trial to view this solution
Question Stats
Zone: Microsoft
Question Asked By: wendelina
Solution Provided By: acperkins
Participating Experts: 2
Solution Grade: A
Views: 114
Translate:
Loading Advertisement...
02.29.2008 at 09:16AM PST, ID: 21015318

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.29.2008 at 10:19AM PST, ID: 21015987

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.29.2008 at 10:44AM PST, ID: 21016248

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.29.2008 at 10:49AM PST, ID: 21016289

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
02.29.2008 at 09:16AM PST, ID: 21015318

Rank: Genius

This may help...if it doesn't, consider dropping the data from AS400 into a staging table on your server and use TSQL to mold it to how you want it.

http://msdn2.microsoft.com/en-us/library/aa215269(SQL.80).aspx
 
02.29.2008 at 10:19AM PST, ID: 21015987
Okay I think I follow what you are getting at in this comment, but I don't follow what it means to "use TSQL to mold it", so what about the option of "creating an ActiveX object"?  Is that a fairly simple thing to do?  In my mind, the date being already in YYYY MM DD format is all that should need to be done but yet it fails.  

Where or how might I obtain a sample ActiveX object so I don't have to create one from scratch?
 
02.29.2008 at 10:44AM PST, ID: 21016248

Rank: Genius

Here is the reason:  The format YYYYMMDD is not understood by VBScript as a valid date.

This is what you can do:
1. Instead of setting the Transformation to date time set it to ActiveX
2. Click on Edit.
3. Click on Properties.
4. You will see something like this:
'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
      DTSDestination("YourSQLServerColumn") = DTSSource("YourAS/400Column")
'               There may be other columns here
      Main = DTSTransformStat_OK
End Function

Change it as follows:

Option Explicit

Function Main()

DTSDestination("YourSQLServerColumn") = GetDate(DTSSource("YourAS/400Column"))

Main = DTSTransformStat_OK

End Function

'  This function will convert your date to a VBScript date data type
Function GetDate(ByVal Value)
Dim YY, MM, DD

YY = Value \ 10000
MM = (Value \ 100) Mod 100
DD = Value Mod 100

GetDate = DateSerial(YY, MM, DD)

End Function
Accepted Solution
 
02.29.2008 at 10:49AM PST, ID: 21016289

Rank: Genius

>>but I don't follow what it means to "use TSQL to mold it"<<
What chapmandew is suggesting (if I may speak for him) is to import the data as is into a column with a character data type in a staging (temporary) table.  You can then use the following simple T-SQL command to convert the format YYYYMMDD to a datetime column in the final production table:
CONVERT(datetime, YourYYYYMMDDColumn, 112)

>>Where or how might I obtain a sample ActiveX object so I don't have to create one from scratch?<<
ActiveX is just the name given in DTS to a task written in VBScript and has little to do with the COM object of the same name.
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628