March 18, 2009 at 9:17 am
I am using SSIS to import data from one table to another.
This is what I want:
Source Table
Date Column - 20090308 (int)
Destination Table
Date Column - 2009-03-08
Iv used DataConversion in SSIS but it keeps failing... Any ideas
March 18, 2009 at 9:29 am
This one is not pretty and there might be some other option is SSIS, but when converting the run_date from SQL server jobs in the msdb..sysjobhistory table, this is what I use.
SELECT SUBSTRING(CONVERT(CHAR(8),Run_date),7,2) + '-'+
SUBSTRING(CONVERT(CHAR(8),Run_date),5,2) + '-' +
SUBSTRING(CONVERT(CHAR(8),Run_date),1,4) as [Date]
FROM msdb.dbo.sysjobhistory
[font="Verdana"]Markus Bohse[/font]
March 18, 2009 at 9:59 am
Im getting the following error:
Incorrect syntax near ' ' -
March 18, 2009 at 12:26 pm
I'm using Script Component for converting from "yyyyMMdd" string (20090115) to Date type. It is more accurate and is independant from date settings.
Script example:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim provider As Globalization.CultureInfo = Globalization.CultureInfo.InvariantCulture
Dim format As String = "yyyyMMdd"
Dim errmessage As String = "place here error message/code"
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.InputDateString_IsNull = True Then
Row.OutputDateInDateType_IsNull = True
Exit Try
Else
Row.OutputDateInDateType = Date.ParseExact(Row.InputDateString, format, provider)
End If
Catch ex As Exception
Row.OutputDateInDateType_IsNull = True
Row.errmsg = Row.errmsg + errmessage
End Try
End Sub
End Class
March 19, 2009 at 2:19 am
Thanks but i know nothing about Microsoft Visual Basic .NET
March 19, 2009 at 5:16 am
To recap:
This is what I want:
Source Table
Date Column - 20090308 (int)
Convert to:
Destination Table
Date Column - 2009-03-08 (Date)
I have set up a Data flow which:
1. picks the data up from the destination table
2. Data conversion is used to change the run_date(int) to String [FT_STR]
3. Data conversion is used to change the new run_date String [FT_STR] to Date [DT_date]
4. This then inserts the data into the destination table
ERROR i get:
Data conversion failed while converting column "run_date String" (327)
to column "run_date Date" (345). The conversion returned status value 2 and
status text "The value could not be converted because of a potential loss of
data.".
Error: 0xC0209029 at Data Flow Task, Data Conversion 1 [331]: SSIS Error
Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output
column "run_date Date" (345)" failed because error code 0xC020907F
occurred, and the error row disposition on "output column "run_date Date"
(345)" specifies failure on error.
March 19, 2009 at 1:46 pm
3. Data conversion is used to change the new run_date String [FT_STR] to Date [DT_date] => For Data conversion component input string after converting from int should be like "2009/03/08" or "2009-03-08" or ... based on your date settings. There is a issue with date also, for example: "20080105" can be "2008/01/05" or "2008/05/01"... look at you date type settings in which order it try to convert by default?
You have choices:
1.Convert string from '20090308' and add separators, then put it into Data conversion component.
2.Use Derived column and use Substrings in it to create apropriate string format
or 3.Use script component.
This script is very simple. It is not nessesary to know a lot about Visual Basic .NET to implemet it.
Steps to do:
1. picks the data up from the source table
2. place "Script Component" in the data flow, when it asks script type: select "Transformation", attach source data component to script.
3. In Script component settings: Go to "Input columns" on the left panel and select column where is your int date (lets call this field "IntDateTypeDate")
4. In Script component settings: Go to "Inputs and Outputs", select "Output 0"->"Ouput Columns" and click the button below => "Add Column". Give name for new column (lets call this field "OutDateTypeDate") and set column type to "database date [DT_DBDATE]". Add one more column to handle conversion errors (lets call this field "errmsg" with string field type).
5. Go to "Script" on the left panel and click the button "Design Script..." below.
6. Copy and paste (overwite) this script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim provider As Globalization.CultureInfo = Globalization.CultureInfo.InvariantCulture
Dim format As String = "yyyyMMdd"
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.IntDateTypeDate_IsNull = True Then
Row.OutDateTypeDate_IsNull = True
Exit Try
Else
Row.OutDateTypeDate = Date.ParseExact(Row.IntDateTypeDate.ToString, format, provider)
End If
Catch ex As Exception
Row.OutDateTypeDate_IsNull = True
Row.errmsg = "place here error message/code"
End Try
End Sub
End Class
7. Save, Ok.
8. Put Conditional Split component after Script component. Join.
7. Set Conditional Split to 2 outputs with rule: bad data: ISNULL(errmsg)==FALSE and errmsg != "", default -> good data
8. Put destination and pass good data to it.
9. You may want to log bad data to other destination table.
For me it works stable and fast.
With some little changes it can be created to convert multiple coulmns and return the error message about each column.
March 20, 2009 at 4:12 am
thanks for your time, I seem to be getting an error on the SCRIPT:
Error 30456: 'IntDataTypeDate_isNull' is not a member of the ScriptComponent_035fc3ff0ed01a2264ac......
March 20, 2009 at 9:52 am
select convert(datetime, convert(varchar, fecha)) from table
that is what i use.
March 20, 2009 at 3:11 pm
Check the following:
Is this field selected in "Input columns" tab of the script component input column list?
Is your input int type field name is "IntDataTypeDate"? If it have different name, change it in script to your field name.
Row.IntDataTypeDate_IsNull = > Row.YourInputFieldName_IsNull
And
Row.IntDateTypeDate.ToString = > Row.YourInputFieldName.ToString
March 20, 2009 at 3:26 pm
Note:
If your field name is like:
Your_Input_Field_Name or your_input_field_name
then in script it will be
YourInputFieldName or yourinputfieldname
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply