I am using a view to use as basis of a Lookup transformation in SSIS.
In my view, I have a couple of columns which is of DATE data type.
ALTER VIEW [dbo].[vCampaign] AS
SELECT
CampaignID
,CAST([startDate] AS DATE) AS [CampaignStartDate]
,CAST([endDate] AS DATE) [CampaignEndDate]
,CAST([expirationDate] AS DATE) AS [CampaignExpirationDate]
FROM campaigns
GOMy problem is that those dates show as DT_WSTR in my lookup output columns and not DT_DBDATE. When I use DATETIME rather than DATE, I am getting DT_DBTIMESTAMP which is closer to what I want. I am trying to forgo having to use a derived column for this simple data conversion, am I missing something that would make this work?
Best,
Tribe84
3条答案
按热度按时间mtb9vblg1#
I had the same problem and I fixed it changing the OLE DB Provider from my shared connection. When I selected "Microsoft OLE DB Provider for SQL Server" the look up component identified the date column in my SQL SERVER 2014 databse like DT_WSTR. I changed the OLE DB Provider to "SQL Native Client 11.0" and the problem was solved.
jljoyd4f2#
Right-click on the Look-up and choose "Show Advanced Editor".
Go to
Input and Output Propertiesand specify the data-type of the date columns asDT_DBDATE.qyswt5oh3#
I came across a very similar error moving data between SAP HANA 2.0 and a SQL Server 2019 in Azure. I was using SSDT in Visual Studio 2019 and changing my connection manager from "Microsoft OLE DB Provider for SQL Server" to "Microsoft OLE DB Driver for SQL Server" fixed the issue.
I hope this helps others, as VS and SSIS error message descriptions can still be a bit cryptic in v2019. However, I will say that I no longer need to recreate different package objects when I change the data source from one target to another, which is a great time saver to avoid workarounds for these issues with earlier versions of SSIS.
Cheers!