July 30, 2014 at 5:43 pm
Please help me find out how to get past this error.
[OLE DB Source 1 [98]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E07 Description:
"Conversion failed when converting the nvarchar value '2014-07-30' to data type int.".
How can I fix? Everywherein SSIS I've set the variable to string and in the sproc that needs them, the parameters are varchar(20) so why is ssis trying to convert to int? How to fix?
I used this guide to configure my settings:
RUNDOWN:
Control Flow
package scope variables: date_start = String and date_end = String both evaluate to a package configuration value which gets it's date_start from a query earlier in the control flow.
breakpoint shows the date_start and date_end values are coming through to pre-execute stage of the Data Flow task that kicks off oledb.
Data Flow Task
OLE DB connection manager to sql database
Data Access mode: SQL Command
SQL command text:
exec spGetCoreMediaExtract
@date_start = ?,
@date_end = ?
parameters:
@date_start = user::date_start
@date_end - user::date_end
sproc (which works, btw). is like this
/****** Object: StoredProcedure [dbo].[spGetCoreMediaExtract] Script Date: 07/30/2014 15:16:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGetCoreMediaExtract]
@date_start nvarchar(20),
@date_end nvarchar(20)
AS
WITH CTE AS
(
SELECT
DL.lead_id
, FCCA.dnis
FROM
fact_call_center_activity FCCA
JOIN dim_lead DL ON FCCA.dim_lead_key = DL.dim_lead_key
WHERE FCCA.DNIS IS NOT NULL --Can't use missing DNIS
AND DL.lead_id <> -1 --Can't use NULL leads
), CTE2 As
(
SELECT
LAD.dnisAS 'Phone_Number_Dialed'
, DD.dim_date_keyAS 'Date_of_Response'
, DT.hour_of_day
, DT.minute_of_hour
, FLA.leads_created
, FLA.referred_lead
, FLA.move_ins_booked
, DL.contact_zip
, DL.contact_phone
, DL.lead_id
FROM
fact_lead_activity FLA
JOIN CTE LAD ON FLA.lead_id = LAD.lead_id
JOIN dim_lead DL ON FLA.lead_id = DL.lead_id AND DL.date_end = '20991231' AND DL.sub_referral_source LIKE 'TV.CH%'
JOIN dim_date DD ON DD.dim_date_key = FLA.dim_date_key_activity
JOIN dim_time DT ON DT.dim_time_key = FLA.dim_time_key_activity
WHERE
FLA.dim_date_key_activity > @date_start
AND FLA.dim_date_key_activity <= @date_end
AND (
FLA.leads_created = 1
OR FLA.referred_lead = 1
OR move_ins_booked = 1
)
), CTE3 AS
(SELECT
'TELE' --AS Telemarketer_Code --hardcoded
+ SUBSTRING(Phone_Number_Dialed + ' ', 1, 10) --AS Phone_Number_Dialed --truncated to ten digits and padded with spaces if necessary
+ CAST(Date_of_Response AS VARCHAR(8)) --AS Date_of_Response
+ RIGHT('00' + CAST(hour_of_day AS VARCHAR(2)),2) + RIGHT('00' + CAST(minute_of_hour AS VARCHAR(2)),2) --AS Time_of_Response_Military
+ CASE
WHEN leads_created = 1 THEN 'LEAD'
WHEN referred_lead = 1 THEN 'REFE'
WHEN move_ins_booked = 1 THEN 'MOVE'
ELSE ' '
END --AS Response_Code
+ '000001' -- Response_Counter_Field is hard coded number of responses each phone call provided
+ ' ' --AS DNIS_Code --Non-required DNIS
+ CASE
WHEN ISNUMERIC(contact_zip) = 1 THEN SUBSTRING(COALESCE(contact_zip, '') + ' ', 1, 5)
ELSE SUBSTRING(COALESCE(contact_zip, '') + ' ', 1, 3) + ' ' --Canada Postal Codes, hopefully
END --AS Zip_Code_of_Caller
+ SUBSTRING(COALESCE(contact_phone, '') + ' ', 1, 3) --AS Area_Code_of_Caller
+ SUBSTRING(CAST(lead_id AS VARCHAR(15)) + ' ',1,15) --AS Customer_Identification
as raw_data
FROM CTE2)
SELECT raw_data FROM CTE3
GO
when I hard code the sql command text like so it works to get the extract I'm after:
SQL command text:
exec spGetCoreMediaExtract
@date_start = 20140615,
@date_end = 20140615
it even works when I do this
SQL command text:
exec spGetCoreMediaExtract
@date_start = '20140615',
@date_end = '20140615'
July 30, 2014 at 6:06 pm
Does this work:
exec spGetCoreMediaExtract
@date_start = '2014-06-15',
@date_end = '2014-06-15'
Asking because this is what it looks like you are passing in based on your post: '2014-07-30'.
July 30, 2014 at 6:48 pm
No, because the sproc is expecting the 20140625 format. The sproc works by the way.
I don't see where in my post i use a date format such as you suggest.
July 30, 2014 at 6:56 pm
KoldCoffee (7/30/2014)
Please help me find out how to get past this error.[OLE DB Source 1 [98]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E07 Description:
"Conversion failed when converting the nvarchar value '2014-07-30' to data type int.". <<<< Here
Look with eye.
July 30, 2014 at 7:02 pm
you're on to something. I missed that. Thanks. I shall maybe need to convert!
July 30, 2014 at 7:14 pm
lynn....this time...
replace('2014-07-30', '-', '')
hee haw. Thanks a lot
just keep it tame!
July 30, 2014 at 7:27 pm
KoldCoffee (7/30/2014)
lynn....this time...replace('2014-07-30', '-', '')
hee haw. Thanks a lot
just keep it tame!
Glad to help. And I was tame.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply