数据流任务未从oracle db更新SQL Server表

Question

“我的sql编辑器”任务使用“sql from a variable”选项，该选项在代码中包含最后一次运行日期变量（变量lastRundate，该变量应查看sql server表中的字段，显示包上次成功运行的日期）。使用此日期，它应该将在此日期之后输入的Oracle db数据拉到SQL Server表中。sql编辑器任务运行良好；但是，当随后调用数据流时，OLE DB源错误输出

[OLE DB源[1]]错误：SSIS错误代码DTS_E_OLEDBERROR。
发生OLE DB错误。
错误代码：0x80040E14。OLE DB记录可用。
源：“Microsoft OLE DB Provider for Oracle”HRESULT：0x80040E14
说明：“ORA-00923:在所需位置未找到FROM关键字”。

我设置得不恰当的地方是什么？

在SQL编辑器任务的SQL语句中：

declare @lastRunDate datetime, @lastRunDate_AsString varchar(30), 
        @sqlScript varchar(1000);

set @lastRunDate = (SELECT LastSuccessRunDate 
                    FROM dbo.LastSuccessRunDate 
                    WHERE PackageName = 'V_M_E')

set @lastRunDate_AsString = (SELECT 
                                convert(varchar(20), @lastRunDate, 101) + '
                              ' + substring(convert(varchar(20), @lastRunDate, 9), 13, 5) + substring(convert(varchar(30), @lastRunDate, 9), 25, 2)
currentdate_time)

set @sqlScript = 'SELECT ep.member_id AS MEMBER_ID,  ep.NAME_FIRST AS
                  NAME_FIRST, ep.NAME_LAST AS NAME_LAST, ep.ADDRESS1 AS ADDRESS1, 
                  ep.ADDRESS2 AS ADDRESS2, ep.ZIP AS ZIP, ep.CITY AS CITY, ep.STATE
                  AS STATE, to_date(ep.BIRTH_DATE, ''MM/DD/YYYY'') AS BIRTH_DATE, 
                  ep.GENDER AS GENDER,  '''' AS RACE ,  '''' AS "LANGUAGE",  
                  nc.NAME_FIRST  || '' '' || nc.NAME_LAST AS EMER_CONTACT,  ep.plan AS
                  "LOB", al.affiliation_secondary AS PLAN_DESC, to_date(ep.end_date,
                  ''MM/DD/YYYY'') AS EXP_COVERAGE_DATE, ''RECIPIENT''  AS "TYPE"  
                  FROM
                       scb.ep_view ep,   SCB.N_C NC,  scb.name,  scb.afflib al 
                  WHERE
                       al.affiliation_id = sc_base.name.text3 AND sc_base.name.name_id =
nc.name_id AND nc.name_id  = ep.name_id AND
(to_date(ep.addr_update_date,''mm/dd/yyyy'') > to_date(''' +
@lastRunDate_AsString + ''', ''mm/dd/yyyy hh:miam'') OR
to_date(ep.plan_update_date,''mm/dd/yyyy'') > to_date(''' +
@lastRunDate_AsString + ''', ''mm/dd/yyyy hh:miam'')) UNION SELECT
hf.member_id AS MEMBER_ID,  hf.NAME_FIRST AS NAME_FIRST,  hf.NAME_LAST
AS NAME_LAST,  hf.ADDRESS1 AS ADDRESS1,  hf.ADDRESS2 AS ADDRESS2, 
 hf.ZIP AS ZIP,  hf.CITY AS CITY,  hf.STATE AS STATE,
to_date(hf.BIRTH_DATE, ''MM/DD/YYYY'') AS BIRTH_DATE,  hf.GENDER AS
GENDER,  '''' AS RACE ,  '''' AS "LANGUAGE",   nc.NAME_FIRST  || '' ''
|| nc.NAME_LAST AS EMER_CONTACT,  hf.plan AS "LOB",
al.affiliation_secondary AS PLAN_DESC, TO_DATE(hf.end_date,
''MM/DD/YYYY'') AS EXP_COVERAGE_DATE, ''RECIPIENT''  AS "TYPE" FROM
scb.hf_view hf,   SCB.N_C NC,  scb.name,  scb.afflib al WHERE
al.affiliation_id = sc_base.name.text3 AND sc_base.name.name_id  =
nc.name_id AND nc.name_id  = hf.name_id AND
(to_date(hf.addr_update_date,''mm/dd/yyyy'') > to_date(''' +
 @lastRunDate_AsString + ''', ''mm/dd/yyyy hh:miam'') OR
to_date(hf.plan_update_date,''mm/dd/yyyy'')  >  to_date(''' +
@lastRunDate_AsString + ''', ''mm/dd/yyyy hh:miam''))'

select @sqlScript as SQLSU

Answer 1

动态sql中Union中的第二个sql中缺少FROM子句。

这是因为当您动态创建sql语句时，您的文本将在1000个字符处被截断。

使用nvarchar(1000)代替varchar(1000)