This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 72%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMS%3C/text%3E%3C/svg%3E)
Hello,
I map "date" information obtained from the Excel source in SSIS (1.3.2 version) through the Data Conversion Transformation Editor with the 'database date' data type
In Date Lookup Transformation Editor, I use the Date_Dim table and the data type of the full_date attribute in this table is "date" as well. However, I get a warning like the one below.
I
interestingly full_date can be matched with Copy of competiton_id in the Unicode String DT-WSTR. Could you help me, please?
*I also tried the following SQL commands in Lookup, but it does not work.
SELECT
DateKey,
CAST(full_date AS DATE) AS full_date,
day_name,
month,
year
FROM
Date_Dim;
Unfortunately, it's truncated in the screenshot but is it definitely DT_DBDATE (not any of the other ones that are similar). Also in the lookup screen, hover over each of the columns, and it should show you the data types that it thinks are being used.
---------------
*Thank you. it was database date[DT-DBDATE]
In order to match full_date with Copy of Date, I am required to select Copy of Date as Unicode String [DT-WSTR] in the Data Conversion Transformation Editor. However, when I execute it in this manner, I receive the following warnings.
Error: 0xC020901E at Fact Data Flow Task, Date Lookup [143]: Row yielded no match during lookup. Error: 0xC0209029 at Fact Data Flow Task, Date Lookup [143]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Date Lookup" failed because error code 0xC020901E occurred, and the error row disposition on "Date Lookup.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at Fact Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Date Lookup" (143) failed with error code 0xC0209029 while processing input "Lookup Input" (157). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Error: 0xC02020C4 at Fact Data Flow Task, Apperance Excel Source [32]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. Error: 0xC0047038 at Fact Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Apperance Excel Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Fact Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
2)When I try the following commands in the Date lookup
SELECT
DateKey,
CONVERT(varchar(8), full_date, 112) AS full_datenew,
day_name,
month,
year
FROM
Date_Dim;
Then, to match Copy of Date for the lookup, I need to map Copy of Date to String [DT-STR] in the Data Conversion Transformation Editor. However, the process fails when I run it.
3I'm new to this, what I don't understand is, although the full_date in the Date_Dim table is in the date data type, it appears in a different data type in the Lookup. Also, if I ignore the Date operation, my Run process is successful, but DateKey is a required column.
What you could also do is make the value on each side be a 'YYYYMMDD' string. SQL Server gets them right every time. (It's the best date literal format). On the SQL Server lookup side, you can get that by CONVERT(varchar(8), column, 112)
Normally, I don't need information in the format hh:mm:ss within the date for my project. I used database timestamp instead of date[DT-DBDATE] in the Data Conversion Transformation Editor for the issues.
I executed the following commands in Date Lookup. I mapped Copy of Date to full_date. and SSIS ran successfully
SELECT
DateKey,
CAST(full_date AS smalldatetime) AS full_date,
day_name,
month,
year
```FROM
Date_Dim;
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 94%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I had a similar problem, but I was trying to do a data flow lookup that had been working in SSDT 2017 with SSIS 2012, but failed when I upgraded to SSIS on SQL 2022 and Visual Studio 2019. My fix was to pick the Microsoft OLEDB Driver for SQL Server. We had been using SQL Native Client before, but it isn't in VS 2019 and later, so I tried the Microsoft OLE DB Provider for SQL Server because I was having a hard time finding documentation to tell me what the preferred provider inside of the SSIS project tool was the correct one to use.
The Provider option was converting the database DATE datatype to WString. When I switched to the preferred (and of course, I found the documentation saying it was the correct one to use AFTER I figured it out on my own by trial and error) one is the Microsoft OLE DB Driver for SQL Server. Once I did that, the package once again recognized the SQL Date column and the correct data type of Date.
It would be really nice if the program defaulted to the most updated and preferred driver over the deprecated driver that isn't even there any longer when you create a new connection.