Ssis
使用 ssis 派生列將 yymmdd 轉換為日期時間
下面是代表出生日期 (yymmdd) 的字元串。我想將其轉換為日期時間格式並使用 ssis 載入到數據庫中。我該怎麼做？在我的派生列中，我有
(DT_DATE)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2))，但它不起作用。
- 470324
- 470324
- 470209
- 101
- 0
數據流
這是我解決問題的一般方法。
我從您的源數據開始並添加了一些其他條件 - NULL 以及 14 和 15 年，以確保我以後的邏輯是正確的。
SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , (NULL) ) D(DrvDOB);這似乎需要做很多工作，但對我來說，這是最易於維護的方法。如果出現問題，我可以簡單地檢查派生列之間並找到邏輯的失敗位。許多人錯誤地認為數據流上的組件數量會減少處理時間。相反的情況實際上可能是正確的，有關更多詳細資訊，請參閱末尾的 sqlblog 文章。
創建一個名為
IsValidLengthThis 的列，它將為我們提供一個布爾值，告訴我們是否需要嘗試從字元串中解析年、月和日。我確保我們沒有 NULL 值並且長度為 6。否則，我們將標記為無效!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6然後，我將使用已解析的年、月和日生成另外 3 個字元串列（der_Year、der_Month、der_Day）。否則，我將使用 00 作為我的值。同樣，我只嘗試解析有效的字元串。
(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00" (IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00" (IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"現在是真正有趣的部分，確定實際年份。由於您的領域稱為出生日期，因此我假設沒有未來的日期記錄-如果您有心理數據，我深表歉意。我採用了 SQL Server、.NET 庫和 COM 使用的路線，並有一個規定的年份用作標記為 19XX 和 20XX 之間的標記。我創建了一個名為 2014 的 SSIS 變數
TwoDigitYearCutoff並將其賦值為 2014。簡而言之，從 00 到 14 解析的任何年份都將被標記為 21 世紀，而其餘的則留在 20 世紀。根據需要調整此值。回到我的數據流中，我創建了另一列，這個 der_ActualYear 將是 4 位數的年份。
(@[User::TwoDigitYearCutoff] / 100 - ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0)) * 100 + (DT_I4)der_Year在這一點上，我們知道全年、月份和日期以及這些數據是否有用。最後的部分是將它們放在一起並使其成為實際的日期。我建構了一個 YYYY-MM-DD 字元串並將其轉換為日期。否則，我將 NULL 轉換為日期（以使我的元數據保持一致）
(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear] + "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE)您現在可以將 der_DrvDOB 連接到您的目標目的地，知道您有一個有效的日期或 NULL。
結果
正如預期的那樣，有效日期或 null
比姆
Biml，商業智能標記語言，描述了商業智能平台。在這裡，我們將使用它來描述 ETL。BIDS Helper是 Visual Studio/BIDS/SSDT 的免費外掛，解決了它的許多缺點。具體來說，我們將使用將描述 ETL 的 Biml 文件轉換為 SSIS 包的能力。這有一個額外的好處，即為您提供一種機制，可以準確地生成我所描述的解決方案，而不是點擊許多繁瑣的對話框。
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <!-- 74383 --> <Connections> <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" /> </Connections> <Packages> <Package Name="dba_74383" ConstraintMode="Linear"> <Variables> <Variable DataType="Int32" Name="TwoDigitYearCutoff">2014<Annotations> <Annotation AnnotationType="Description">Use the two digit year cutoff to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years</Annotation> </Annotations> </Variable> </Variables> <Tasks> <Dataflow Name="DFT Make Dates"> <Transformations> <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC Gen data"> <DirectInput>SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , (NULL) ) D(DrvDOB);</DirectInput> </OleDbSource> <!-- Test whether we have minimally valid data --> <DerivedColumns Name="DER IsValidLength"> <Columns> <Column Name="IsValidLength" DataType="Boolean"><![CDATA[!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6 ]]></Column> </Columns> </DerivedColumns> <!-- Extract the year --> <DerivedColumns Name="DER ExtractYear"> <Columns> <Column Name="der_Year" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00"</Column> </Columns> </DerivedColumns> <!-- Extract the Month --> <DerivedColumns Name="DER ExtractMonth"> <Columns> <Column Name="der_Month" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00"</Column> </Columns> </DerivedColumns> <!-- Extract the day --> <DerivedColumns Name="DER ExtractDay"> <Columns> <Column Name="der_Day" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"</Column> </Columns> </DerivedColumns> <!-- Guess at year based on cutoff date. Assume historical data and not future dated since this is DOB. For supplied value of 2014, if 14 is seen, it will be 2014 if 15 is seen, it will be 1915 http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year this.TwoDigitYearMax / 100 - (year > this.TwoDigitYearMax % 100 ? 1 : 0)) * 100 + year @[User::TwoDigitYearCutoff]) / 100 - ((Int32) [der_Year] > @[User::TwoDigitYearCutoff]) % 100 ? 1 : 0)) * 100 + (Int32) [der_Year] --> <DerivedColumns Name="DER Guess at year"> <Columns> <Column DataType="Int32" Name="der_ActualYear"><![CDATA[(@[User::TwoDigitYearCutoff] / 100 - ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0)) * 100 + (DT_I4)der_Year]]></Column> </Columns> <Annotations> <Annotation AnnotationType="Description">This might be overkill, but it sure was fun</Annotation> </Annotations> </DerivedColumns> <!-- Put it all together --> <DerivedColumns Name="DER Make valid date"> <Columns> <Column Name="der_DrvDOB" DataType="Date">(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear] + "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE) </Column> </Columns> </DerivedColumns> <DerivedColumns Name="DER bitbucket"> </DerivedColumns> </Transformations> </Dataflow> </Tasks> </Package> </Packages> </Biml>參考
- http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx
- https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year
- http://web.archive.org/web/20111227015148/http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/12/investigation-can-different-combinations-of-components-effect-dataflow-performance.aspx
看起來您的陳述正確，但似乎您的子字元串順序錯誤（目前您有 yy-mm-dd）。嘗試將其翻轉為 mm-dd-yy
(DT_DATE)(SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2) + "-" + SUBSTRING([Drv DOB],1,2))