首页 文章

SSIS中的varchar到日期格式

提问于
浏览
1

问题编辑(非常感谢billinkc!)作为提议的答案更详细地解决了这个问题

我有两个带有一个DATE列的SQL表 . 源DATE列位于varchar中,目标位于日期格式 . 原始行来自Flat File,流程如下所示 .
enter image description here

SSIS包(见下文)正在将BIRTHDATE列从一个临时表移动到另一个临时表 . 从头开始平面文件源到临时表:在此处输入图像描述

DATE(vachar)列的派生列:

(DT_STR,40,1252)((TRIM(BDAY)==“”?0:1)== 0?((DT_STR,40,1252)“19000101”):((DT_STR,40,1252)(SUBSTRING( (REPLACE(BDAY,“/”,“”)),5,4)SUBSTRING((替换(BDAY,“/”,“”)),3,2)SUBSTRING((REPLACE(BDAY,“/”,“ “)),1,2))))

第二个包正在尝试将varchar转换为DATE格式但是失败:在此处输入图像描述在此输入图像描述
enter image description here

enter image description here
第二个表中的目标格式为DATE

错误消息:[数据转换2]错误:将列“BIRTHDATE”(248)转换为列“BIRTHDATE”(6)时数据转换失败 . 转换返回状态值2和状态文本“由于潜在的数据丢失,无法转换该值 . ” .

[数据转换2]错误:SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR . “Data Conversion.Outputs [Data Conversion Output] .Columns [BIRTHDATE]”失败,因为错误代码0xC020907F发生,错误行处理“Data Conversion.Outputs [Data Conversion Output] .Columns [BIRTHDATE]”指定错误失败 . 指定组件的指定对象发生错误 . 在此之前可能会发布错误消息,其中包含有关失败的更多信息 .

我确实尝试使用Derived Column和经常提出的解决方案,但它没有用 . 最后的研究解决方案是使用数据转换任务,但是这个在下面返回一个错误 .

我错过了什么吗?谢谢你的任何分数! :)

1 回答

  • 1

    对不起,请向后看 . 您可以't do a data conversion within SSIS if you have a string date format of yyyymmddd aka ccyymmdd. If it'是一个平面文件源组件,然后您可以使用快速解析选项来转换日期 . 否则,它会陷入地区主义的可能性之中 .

    为了使数据转换组件工作,你将不得不提供一个血腥的大提示,它将分隔符放入其中 .

    这是我的Source查询 . 它从19000101到2036-11-22产生5万行

    -- Generate dates from 1900-01-01 to 2036-11-22
    -- sans dashes
    SELECT
        CONVERT(char(8), dateadd(day, D.number, '1900-01-01'), 112) As BirthDate
    FROM
    (
        select TOP 50000
            CAST((row_number() OVER (ORDER BY (SELECT NULL)) -1) AS int) AS number
        FROM
            sys.all_columns AS AC
            CROSS APPLY
                sys.all_columns AS AC1
            CROSS APPLY
                sys.all_columns AS AC2
    
    ) D(number);
    

    我用两个表达式 . 第一个是通过派生列的新列,名为 DelimitedBirthDate ,类型DT_WSTR长度为10

    SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2)
    

    我创建的第二列是DT_DATE,名为 DateBirthDate . 我使用与上面相同的表达式,但显式地将结果表达式转换为日期 .

    (DT_DATE)(SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2))
    

    最后,我使用数据转换组件创建一个新列 DateDelimitedBirthDate ,它使用 DelimitedBirthDate 作为源,DT_DATE作为输出数据类型 .

    enter image description here

    你可以用Biml做到这一点 .

    • 下载并安装BIDS Helper .

    • 右键单击SSIS项目并选择Add new Biml File

    • 将以下代码粘贴到BimlScript.biml中

    • 更改以下内容的第5行以指向有效的服务器和可能的提供程序 .

    • 右键单击BimlScript.Biml并选择“生成SSIS包”

    利润!

    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <Connection
                Name="tempdb"
                ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
                />
        </Connections>
        <Packages>
            <Package Name="so_33226370">
                <Tasks>
                    <Dataflow Name="DFT Date conversion">
                        <Transformations>
                            <OleDbSource
                                ConnectionName="tempdb"
                                Name="OLE_SRC Generate dates">
                                <DirectInput>
                                    <![CDATA[-- Generate dates from 1900-01-01 to 2036-11-22
    -- sans dashes
    SELECT
        CONVERT(char(8), dateadd(day, D.number, '1900-01-01'), 112) As BirthDate
    FROM
    (
        select TOP 50000
            CAST((row_number() OVER (ORDER BY (SELECT NULL)) -1) AS int) AS number
        FROM
            sys.all_columns AS AC
            CROSS APPLY
                sys.all_columns AS AC1
            CROSS APPLY
                sys.all_columns AS AC2
    
    ) D(number);
    ]]>
                                </DirectInput>
                            </OleDbSource>
                            <DerivedColumns Name="DER Generate delimiters">
                                <Columns>
                                    <Column DataType="String" Name="DelimitedBirthDate" Length="10">SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2)</Column>
                                    <Column DataType="Date" Name="DateBirthDate">(DT_DATE)(SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2))</Column>
                                </Columns>
                            </DerivedColumns>
                            <DataConversion Name="DC Convert Delimited">
                                <Columns>
                                    <Column DataType="Date" SourceColumn="DelimitedBirthDate" TargetColumn="DateDelimitedBirthDate" />
                                </Columns>
                            </DataConversion>
    
                            <DerivedColumns Name="DER Placeholder"></DerivedColumns>
                        </Transformations>
                    </Dataflow>
                </Tasks>
            </Package>
        </Packages>
    </Biml>
    

相关问题