July 19, 2013 at 1:04 pm
Hi,
I need to import data from Oracle table to SQL Server table using SSIS.
I'm using data flow task with OLEDB source, Data Conversion and OLEDB destination
Here is the table script for Oracle table. Can I have equivalent SQL Server Table script for this Oracle Table?
CREATE TABLE AMC.POLICY
(
POLICY NUMBER NOT NULL,
AGENCY NUMBER,
WRITING_COMPANY NUMBER,
NEW_WRITING_COMPANY NUMBER,
DEC_ADDR NUMBER,
POLICY_PREFIX NUMBER,
NAMED_INSURED NUMBER,
INSPECTOR NUMBER,
RENEWED_AS NUMBER,
NEW_WRITING_DATE DATE,
RENEWED_FROM NUMBER,
NEW_AGENCY NUMBER,
NEW_AGENCY_DATE DATE,
QUOTE_FLAG NUMBER(38) DEFAULT 0,
PRODUCER VARCHAR2(80 BYTE),
TERM_TYPE VARCHAR2(80 BYTE),
CURRENT_DEC NUMBER,
TERM_MONTHS NUMBER(38),
CURRENT_DEC_DATE DATE,
TERM_EFFECTIVE_DATE DATE,
UMBRELLA VARCHAR2(80 BYTE),
AGENCY_CODE NUMBER(38),
TERM_EXPIRATION_DATE DATE,
LEGAL_TEXT VARCHAR2(80 BYTE),
POLICY_NBR VARCHAR2(80 BYTE),
EMPLOYEE_FLAG NUMBER(38) DEFAULT 0,
INCEPTION_DATE DATE,
QUOTE_SUFFIX VARCHAR2(80 BYTE),
ENDORSE_MODE NUMBER(38) DEFAULT 1,
QUOTE_REASON VARCHAR2(40 BYTE),
POLICY_SEARCH_NBR VARCHAR2(80 BYTE),
APP_CHECK VARCHAR2(80 BYTE),
AUDIT_FREQ NUMBER(38),
TERM_NBR NUMBER(38) DEFAULT 1 NOT NULL,
REMITTER VARCHAR2(40 BYTE),
AUDIT_FORM VARCHAR2(40 BYTE),
AUDIT_DATE DATE,
MVR_REQUEST_DATE DATE,
POLICY_STATUS VARCHAR2(40 BYTE),
APP_REMITTER VARCHAR2(40 BYTE),
POLICY_STATUS_DATE DATE,
PYMT_PLAN VARCHAR2(40 BYTE),
ACTIVITY_STATUS VARCHAR2(40 BYTE),
APP_CASH_AMT FLOAT(126),
ACTIVITY_STATUS_DATE DATE,
APP_CHECK_KEY NUMBER,
CANCEL_WAIT_DAYS NUMBER(38),
CANCEL_EFFECTIVE_DATE DATE,
APP_INSURED_NAME VARCHAR2(80 BYTE),
CANCEL_REQUEST_BY VARCHAR2(40 BYTE),
RISK_GROSS_AMT FLOAT(126),
CANCEL_CHECK_SENT VARCHAR2(40 BYTE),
CANCEL_INSPECTION NUMBER(38) DEFAULT 0,
REINSTATED_EFFECTIVE_DATE DATE,
NONRENEW_WAIT_DAYS NUMBER(38),
NON_PAY_NOTICE NUMBER(38) DEFAULT 0,
CANCEL_NOTICE_DATE DATE,
NONRENEW_NOTICE_DATE DATE,
NONRENEW_INSPECTION NUMBER(38) DEFAULT 0,
CANCEL_PREM_DATE DATE,
EXTENSION_DATE DATE,
CANCEL_ACTUAL_DATE DATE,
REISSUE_FEE NUMBER(38) DEFAULT 0,
CREATE_ID VARCHAR2(30 BYTE) DEFAULT USER,
CANCEL_NSF_AMT FLOAT(126),
FIRST_MODIFIED DATE DEFAULT SYSDATE,
AUDIT_ID VARCHAR2(30 BYTE) DEFAULT USER NOT NULL,
LAST_MODIFIED DATE DEFAULT SYSDATE NOT NULL,
REINSTATED_REASON VARCHAR2(40 BYTE),
NON_PAY_COUNT NUMBER(38),
BILL_TYPE VARCHAR2(40 BYTE),
LAST_BILL_DATE DATE,
NEXT_BILL_DATE DATE,
NONRENEW_EFFECTIVE_DATE DATE,
BILL_DUE_DATE DATE,
CURRENT_GROSS_OS FLOAT(126),
CURRENT_NET_OS FLOAT(126),
COMMISSION_PCT FLOAT(126),
CURRENT_INFORCE FLOAT(126),
CARRY_DATE DATE,
PREVIOUS_CARRIER VARCHAR2(40 BYTE),
REFERRED_BY VARCHAR2(80 BYTE),
CURRENT_MIN_DUE FLOAT(126),
RENEWAL_CERT_COUNT NUMBER(38),
FACULTATIVE_FLAG NUMBER(38) DEFAULT 0,
WRITING_CODE NUMBER(38),
BRANCH_CODE NUMBER(38),
PRINT_CANCEL NUMBER(38) DEFAULT 0,
PRINT_NONRENEW NUMBER(38) DEFAULT 0,
PRINT_REINSTATE NUMBER(38) DEFAULT 0,
PRINT_NR_RESCIND NUMBER(38) DEFAULT 0,
PRINT_AGENCY NUMBER(38) DEFAULT 0,
BILL_STATUS VARCHAR2(40 BYTE),
BILL_STATUS_DATE DATE,
NOTICE_STATUS VARCHAR2(40 BYTE),
NOTICE_STATUS_DATE DATE,
CANCEL_COUNT NUMBER(38),
CANCEL_NOTICE_COUNT NUMBER(38),
FULL_TERM NUMBER(38) DEFAULT 0,
NEW_AGENCY_DEC_FLAG NUMBER(38),
BUSINESS_LINE NUMBER,
AGENCY_EXTENSION NUMBER(38),
LEGAL_TEXT_LONG VARCHAR2(256 BYTE),
HOLD_NOTICE_DATE DATE,
PRINT_CANCEL_NP NUMBER(38) DEFAULT 0,
PMWRITING_COMPANY NUMBER,
QUOTE_STATUS VARCHAR2(40 BYTE),
INFLATION_GUARD_PCT FLOAT(126),
PRINT_DECLINATION NUMBER(38) DEFAULT 0,
CANCEL_FUNC_DEPT VARCHAR2(80 BYTE),
NONRENEW_FUNC_DEPT VARCHAR2(80 BYTE),
CANCEL_TYPE VARCHAR2(40 BYTE),
NONRENEW_TYPE VARCHAR2(40 BYTE),
PARENT_AGENCY_KEY NUMBER,
INFLATION_GUARD VARCHAR2(40 BYTE),
DEC_FUNC_DEPT VARCHAR2(80 BYTE),
SCANLINE VARCHAR2(254 BYTE),
VIEW_PARENT_AGENCY NUMBER(38) DEFAULT 0 NOT NULL,
CANCEL_SYSTEM NUMBER(38) DEFAULT 0,
IS_SPECIAL_BILL NUMBER(1) DEFAULT 0,
UMBRELLA_KEY NUMBER,
REVERSE_REISSUE_FEE NUMBER DEFAULT 0,
CONV_IN_THIS_TERM NUMBER DEFAULT 0,
RENEWAL_CR_OVERRIDE_FLAG NUMBER,
APP_AGENCY NUMBER,
ACCOUNT NUMBER,
CONTROL NUMBER,
SPECIALTY_PGM NUMBER,
ONLINE_REF_NUMBER NUMBER,
GLOBAL_TERR_CLEARANCE NUMBER,
GTC_ACTION NUMBER,
LOSS_FREE_CREDIT NUMBER,
ENTERED_BY VARCHAR2(80 BYTE),
ENDORSE_QUOTE_INFO NUMBER,
STP_FLAG NUMBER(10) DEFAULT 0,
MVR_STATUS VARCHAR2(40 BYTE),
APP_CASH_TYPE VARCHAR2(80 BYTE)
)
________________________________________________________
________________________________________________________
In the OLEDB Destination editior, I'm getting the below query when I click to create new table. Can I use this as is or do I need to modify? Because it's creating the columns twice with copy. Why columns are getting created twice?
CREATE TABLE [OLE DB Destination] (
[POLICY] nvarchar(38),
[AGENCY] nvarchar(38),
[WRITING_COMPANY] nvarchar(38),
[NEW_WRITING_COMPANY] nvarchar(38),
[DEC_ADDR] nvarchar(38),
[POLICY_PREFIX] nvarchar(38),
[NAMED_INSURED] nvarchar(38),
[INSPECTOR] nvarchar(38),
[RENEWED_AS] nvarchar(38),
[NEW_WRITING_DATE] datetime,
[RENEWED_FROM] nvarchar(38),
[NEW_AGENCY] nvarchar(38),
[NEW_AGENCY_DATE] datetime,
[QUOTE_FLAG] numeric(38,0),
[PRODUCER] varchar(80),
[TERM_TYPE] varchar(80),
[CURRENT_DEC] nvarchar(38),
[TERM_MONTHS] numeric(38,0),
[CURRENT_DEC_DATE] datetime,
[TERM_EFFECTIVE_DATE] datetime,
[UMBRELLA] varchar(80),
[AGENCY_CODE] numeric(38,0),
[TERM_EXPIRATION_DATE] datetime,
[LEGAL_TEXT] varchar(80),
[POLICY_NBR] varchar(80),
[EMPLOYEE_FLAG] numeric(38,0),
[INCEPTION_DATE] datetime,
[QUOTE_SUFFIX] varchar(80),
[ENDORSE_MODE] numeric(38,0),
[QUOTE_REASON] varchar(40),
[POLICY_SEARCH_NBR] varchar(80),
[APP_CHECK] varchar(80),
[AUDIT_FREQ] numeric(38,0),
[TERM_NBR] numeric(38,0),
[REMITTER] varchar(40),
[AUDIT_FORM] varchar(40),
[AUDIT_DATE] datetime,
[MVR_REQUEST_DATE] datetime,
[POLICY_STATUS] varchar(40),
[APP_REMITTER] varchar(40),
[POLICY_STATUS_DATE] datetime,
[PYMT_PLAN] varchar(40),
[ACTIVITY_STATUS] varchar(40),
[APP_CASH_AMT] float,
[ACTIVITY_STATUS_DATE] datetime,
[APP_CHECK_KEY] nvarchar(38),
[CANCEL_WAIT_DAYS] numeric(38,0),
[CANCEL_EFFECTIVE_DATE] datetime,
[APP_INSURED_NAME] varchar(80),
[CANCEL_REQUEST_BY] varchar(40),
[RISK_GROSS_AMT] float,
[CANCEL_CHECK_SENT] varchar(40),
[CANCEL_INSPECTION] numeric(38,0),
[REINSTATED_EFFECTIVE_DATE] datetime,
[NONRENEW_WAIT_DAYS] numeric(38,0),
[NON_PAY_NOTICE] numeric(38,0),
[CANCEL_NOTICE_DATE] datetime,
[NONRENEW_NOTICE_DATE] datetime,
[NONRENEW_INSPECTION] numeric(38,0),
[CANCEL_PREM_DATE] datetime,
[EXTENSION_DATE] datetime,
[CANCEL_ACTUAL_DATE] datetime,
[REISSUE_FEE] numeric(38,0),
[CREATE_ID] varchar(30),
[CANCEL_NSF_AMT] float,
[FIRST_MODIFIED] datetime,
[AUDIT_ID] varchar(30),
[LAST_MODIFIED] datetime,
[REINSTATED_REASON] varchar(40),
[NON_PAY_COUNT] numeric(38,0),
[BILL_TYPE] varchar(40),
[LAST_BILL_DATE] datetime,
[NEXT_BILL_DATE] datetime,
[NONRENEW_EFFECTIVE_DATE] datetime,
[BILL_DUE_DATE] datetime,
[CURRENT_GROSS_OS] float,
[CURRENT_NET_OS] float,
[COMMISSION_PCT] float,
[CURRENT_INFORCE] float,
[CARRY_DATE] datetime,
[PREVIOUS_CARRIER] varchar(40),
[REFERRED_BY] varchar(80),
[CURRENT_MIN_DUE] float,
[RENEWAL_CERT_COUNT] numeric(38,0),
[FACULTATIVE_FLAG] numeric(38,0),
[WRITING_CODE] numeric(38,0),
[BRANCH_CODE] numeric(38,0),
[PRINT_CANCEL] numeric(38,0),
[PRINT_NONRENEW] numeric(38,0),
[PRINT_REINSTATE] numeric(38,0),
[PRINT_NR_RESCIND] numeric(38,0),
[PRINT_AGENCY] numeric(38,0),
[BILL_STATUS] varchar(40),
[BILL_STATUS_DATE] datetime,
[NOTICE_STATUS] varchar(40),
[NOTICE_STATUS_DATE] datetime,
[CANCEL_COUNT] numeric(38,0),
[CANCEL_NOTICE_COUNT] numeric(38,0),
[FULL_TERM] numeric(38,0),
[NEW_AGENCY_DEC_FLAG] numeric(38,0),
[BUSINESS_LINE] nvarchar(38),
[AGENCY_EXTENSION] numeric(38,0),
[LEGAL_TEXT_LONG] varchar(256),
[HOLD_NOTICE_DATE] datetime,
[PRINT_CANCEL_NP] numeric(38,0),
[PMWRITING_COMPANY] nvarchar(38),
[QUOTE_STATUS] varchar(40),
[INFLATION_GUARD_PCT] float,
[PRINT_DECLINATION] numeric(38,0),
[CANCEL_FUNC_DEPT] varchar(80),
[NONRENEW_FUNC_DEPT] varchar(80),
[CANCEL_TYPE] varchar(40),
[NONRENEW_TYPE] varchar(40),
[PARENT_AGENCY_KEY] nvarchar(38),
[INFLATION_GUARD] varchar(40),
[DEC_FUNC_DEPT] varchar(80),
[SCANLINE] varchar(254),
[VIEW_PARENT_AGENCY] numeric(38,0),
[CANCEL_SYSTEM] numeric(38,0),
[IS_SPECIAL_BILL] numeric(1,0),
[UMBRELLA_KEY] nvarchar(38),
[REVERSE_REISSUE_FEE] nvarchar(38),
[CONV_IN_THIS_TERM] nvarchar(38),
[RENEWAL_CR_OVERRIDE_FLAG] nvarchar(38),
[APP_AGENCY] nvarchar(38),
[ACCOUNT] nvarchar(38),
[CONTROL] nvarchar(38),
[SPECIALTY_PGM] nvarchar(38),
[ONLINE_REF_NUMBER] nvarchar(38),
[GLOBAL_TERR_CLEARANCE] nvarchar(38),
[GTC_ACTION] nvarchar(38),
[LOSS_FREE_CREDIT] nvarchar(38),
[ENTERED_BY] varchar(80),
[ENDORSE_QUOTE_INFO] nvarchar(38),
[STP_FLAG] numeric(10,0),
[MVR_STATUS] varchar(40),
[APP_CASH_TYPE] varchar(80),
[Copy of POLICY] nvarchar(38),
[Copy of AGENCY] nvarchar(38),
[Copy of WRITING_COMPANY] nvarchar(38),
[Copy of NEW_WRITING_COMPANY] nvarchar(38),
[Copy of DEC_ADDR] nvarchar(38),
[Copy of POLICY_PREFIX] nvarchar(38),
[Copy of NAMED_INSURED] nvarchar(38),
[Copy of INSPECTOR] nvarchar(38),
[Copy of RENEWED_AS] nvarchar(38),
[Copy of NEW_WRITING_DATE] datetime,
[Copy of RENEWED_FROM] nvarchar(38),
[Copy of NEW_AGENCY] nvarchar(38),
[Copy of NEW_AGENCY_DATE] datetime,
[Copy of QUOTE_FLAG] numeric(38,0),
[Copy of PRODUCER] varchar(80),
[Copy of TERM_TYPE] varchar(80),
[Copy of CURRENT_DEC] nvarchar(38),
[Copy of TERM_MONTHS] numeric(38,0),
[Copy of CURRENT_DEC_DATE] datetime,
[Copy of TERM_EFFECTIVE_DATE] datetime,
[Copy of UMBRELLA] varchar(80),
[Copy of AGENCY_CODE] numeric(38,0),
[Copy of TERM_EXPIRATION_DATE] datetime,
[Copy of LEGAL_TEXT] varchar(80),
[Copy of POLICY_NBR] varchar(80),
[Copy of EMPLOYEE_FLAG] numeric(38,0),
[Copy of INCEPTION_DATE] datetime,
[Copy of QUOTE_SUFFIX] varchar(80),
[Copy of ENDORSE_MODE] numeric(38,0),
[Copy of QUOTE_REASON] varchar(40),
[Copy of POLICY_SEARCH_NBR] varchar(80),
[Copy of APP_CHECK] varchar(80),
[Copy of AUDIT_FREQ] numeric(38,0),
[Copy of TERM_NBR] numeric(38,0),
[Copy of REMITTER] varchar(40),
[Copy of AUDIT_FORM] varchar(40),
[Copy of AUDIT_DATE] datetime,
[Copy of MVR_REQUEST_DATE] datetime,
[Copy of POLICY_STATUS] varchar(40),
[Copy of APP_REMITTER] varchar(40),
[Copy of POLICY_STATUS_DATE] datetime,
[Copy of PYMT_PLAN] varchar(40),
[Copy of ACTIVITY_STATUS] varchar(40),
[Copy of APP_CASH_AMT] float,
[Copy of ACTIVITY_STATUS_DATE] datetime,
[Copy of APP_CHECK_KEY] nvarchar(38),
[Copy of CANCEL_WAIT_DAYS] numeric(38,0),
[Copy of CANCEL_EFFECTIVE_DATE] datetime,
[Copy of APP_INSURED_NAME] varchar(80),
[Copy of CANCEL_REQUEST_BY] varchar(40),
[Copy of RISK_GROSS_AMT] float,
[Copy of CANCEL_CHECK_SENT] varchar(40),
[Copy of CANCEL_INSPECTION] numeric(38,0),
[Copy of REINSTATED_EFFECTIVE_DATE] datetime,
[Copy of NONRENEW_WAIT_DAYS] numeric(38,0),
[Copy of NON_PAY_NOTICE] numeric(38,0),
[Copy of CANCEL_NOTICE_DATE] datetime,
[Copy of NONRENEW_NOTICE_DATE] datetime,
[Copy of NONRENEW_INSPECTION] numeric(38,0),
[Copy of CANCEL_PREM_DATE] datetime,
[Copy of EXTENSION_DATE] datetime,
[Copy of CANCEL_ACTUAL_DATE] datetime,
[Copy of REISSUE_FEE] numeric(38,0),
[Copy of CREATE_ID] varchar(30),
[Copy of CANCEL_NSF_AMT] float,
[Copy of FIRST_MODIFIED] datetime,
[Copy of AUDIT_ID] varchar(30),
[Copy of LAST_MODIFIED] datetime,
[Copy of REINSTATED_REASON] varchar(40),
[Copy of NON_PAY_COUNT] numeric(38,0),
[Copy of BILL_TYPE] varchar(40),
[Copy of LAST_BILL_DATE] datetime,
[Copy of NEXT_BILL_DATE] datetime,
[Copy of NONRENEW_EFFECTIVE_DATE] datetime,
[Copy of BILL_DUE_DATE] datetime,
[Copy of CURRENT_GROSS_OS] float,
[Copy of CURRENT_NET_OS] float,
[Copy of COMMISSION_PCT] float,
[Copy of CURRENT_INFORCE] float,
[Copy of CARRY_DATE] datetime,
[Copy of PREVIOUS_CARRIER] varchar(40),
[Copy of REFERRED_BY] varchar(80),
[Copy of CURRENT_MIN_DUE] float,
[Copy of RENEWAL_CERT_COUNT] numeric(38,0),
[Copy of FACULTATIVE_FLAG] numeric(38,0),
[Copy of WRITING_CODE] numeric(38,0),
[Copy of BRANCH_CODE] numeric(38,0),
[Copy of PRINT_CANCEL] numeric(38,0),
[Copy of PRINT_NONRENEW] numeric(38,0),
[Copy of PRINT_REINSTATE] numeric(38,0),
[Copy of PRINT_NR_RESCIND] numeric(38,0),
[Copy of PRINT_AGENCY] numeric(38,0),
[Copy of BILL_STATUS] varchar(40),
[Copy of BILL_STATUS_DATE] datetime,
[Copy of NOTICE_STATUS] varchar(40),
[Copy of NOTICE_STATUS_DATE] datetime,
[Copy of CANCEL_COUNT] numeric(38,0),
[Copy of CANCEL_NOTICE_COUNT] numeric(38,0),
[Copy of FULL_TERM] numeric(38,0),
[Copy of NEW_AGENCY_DEC_FLAG] numeric(38,0),
[Copy of BUSINESS_LINE] nvarchar(38),
[Copy of AGENCY_EXTENSION] numeric(38,0),
[Copy of LEGAL_TEXT_LONG] varchar(256),
[Copy of HOLD_NOTICE_DATE] datetime,
[Copy of PRINT_CANCEL_NP] numeric(38,0),
[Copy of PMWRITING_COMPANY] nvarchar(38),
[Copy of QUOTE_STATUS] varchar(40),
[Copy of INFLATION_GUARD_PCT] float,
[Copy of PRINT_DECLINATION] numeric(38,0),
[Copy of CANCEL_FUNC_DEPT] varchar(80),
[Copy of NONRENEW_FUNC_DEPT] varchar(80),
[Copy of CANCEL_TYPE] varchar(40),
[Copy of NONRENEW_TYPE] varchar(40),
[Copy of PARENT_AGENCY_KEY] nvarchar(38),
[Copy of INFLATION_GUARD] varchar(40),
[Copy of DEC_FUNC_DEPT] varchar(80),
[Copy of SCANLINE] varchar(254),
[Copy of VIEW_PARENT_AGENCY] numeric(38,0),
[Copy of CANCEL_SYSTEM] numeric(38,0),
[Copy of IS_SPECIAL_BILL] numeric(1,0),
[Copy of UMBRELLA_KEY] nvarchar(38),
[Copy of REVERSE_REISSUE_FEE] nvarchar(38),
[Copy of CONV_IN_THIS_TERM] nvarchar(38),
[Copy of RENEWAL_CR_OVERRIDE_FLAG] nvarchar(38),
[Copy of APP_AGENCY] nvarchar(38),
[Copy of ACCOUNT] nvarchar(38),
[Copy of CONTROL] nvarchar(38),
[Copy of SPECIALTY_PGM] nvarchar(38),
[Copy of ONLINE_REF_NUMBER] nvarchar(38),
[Copy of GLOBAL_TERR_CLEARANCE] nvarchar(38),
[Copy of GTC_ACTION] nvarchar(38),
[Copy of LOSS_FREE_CREDIT] nvarchar(38),
[Copy of ENTERED_BY] varchar(80),
[Copy of ENDORSE_QUOTE_INFO] nvarchar(38),
[Copy of STP_FLAG] numeric(10,0),
[Copy of MVR_STATUS] varchar(40),
[Copy of APP_CASH_TYPE] varchar(80)
)
July 22, 2013 at 3:53 am
Your data conversion component doesn't actually convert the column itself to another data type. It makes a copy and stores the converted data in that new column.
That's why you have the double amount of columns.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply