how to load sql server table(varchar(max) or nvarchar(max)) data into text file using ssis

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  October 28, 2017 at 7:32 am

  #340997

  Hi Friends I have one doubt in ssis how to load sql server table data into flat file using ssis package source sql server table like below : in this table have multiple histore table information have.

  CREATE TABLE [dbo].[testobjects11](
    [ObjectText] [nvarchar](max) NULL,
    [ObjectType] [nvarchar](max) NULL,
    [ObjectName] [nvarchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [testobjects] ( [tablesttructure] nvarchar(MAX) NULL )', N'U ', N'test')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [testobjects1] ( [ObjectText] nvarchar(MAX) NULL [ObjectType] nvarchar(MAX) NULL [ObjectName] nvarchar(MAX) NULL )', N'U ', N'test1')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [test123] ( [id] int NOT NULL [name] varchar(50) NOT NULL [sal] int NOT NULL )', N'U ', N'test3')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [test] ( [Freight bill origin terminal ID] nvarchar(250) NULL [Freight bill sequence number] nvarchar(250) NULL [Destination terminal ID] nvarchar(250) NULL [Type of shipment] nvarchar(250) NULL [Total pieces, this shipment] nvarchar(250) NULL [Total charges this shipment, freight] nvarchar(250) NULL [Total Pro charges] nvarchar(250) NULL [Billing terms] nvarchar(250) NULL [Pickup driver number] nvarchar(250) NULL [Pickup driver run] nvarchar(250) NULL [Pickup unit] nvarchar(250) NULL [Shipper''s B/L #] nvarchar(250) NULL [Special commodity code] nvarchar(250) NULL [Palletized freight code] nvarchar(250) NULL [Trailer number] nvarchar(250) NULL [Trailer owner SCAT code] nvarchar(250) NULL [Consignee code] nvarchar(250) NULL [Consignee name] nvarchar(250) NULL [Consignee address 1] nvarchar(250) NULL [Consignee address 2] nvarchar(250) NULL [Consignee city] nvarchar(250) NULL [Consignee state] nvarchar(250) NULL [Consignee zip code] nvarchar(250) NULL [Consignee zip + 4 code] nvarchar(250) NULL [Shipper code] nvarchar(250) NULL [Shipper name] nvarchar(250) NULL [Shipper address 1] nvarchar(250) NULL [Shipper address 2] nvarchar(250) NULL [Shipper city] nvarchar(250) NULL [Shipper state] nvarchar(250) NULL [Shipper zip code] nvarchar(250) NULL [Shipper zip + 4 code] nvarchar(250) NULL [Transfer from carrier] nvarchar(250) NULL [Transfer from carrier at] nvarchar(250) NULL [Transfer from F/B #] nvarchar(250) NULL [Transfer to carrier] nvarchar(250) NULL [Transfer to carrier at] nvarchar(250) NULL [Split charges: From %''age] nvarchar(250) NULL [Split charges: NP %''age] nvarchar(250) NULL [Split charges: To %''age] nvarchar(250) NULL [Split charges: From $] nvarchar(250) NULL [Split charges: NP $] nvarchar(250) NULL [Split charges: To $] nvarchar(250) NULL [Pro split charges: From $] nvarchar(250) NULL [Pro Split charges: NP $] nvarchar(250) NULL [Pro Split charges: To $] nvarchar(250) NULL [Estimated revenue] nvarchar(250) NULL [COD amount] nvarchar(250) NULL [Origin terminal revenue] nvarchar(250) NULL [Destination terminal revenue] nvarchar(250) NULL [Accessorial code #1] nvarchar(250) NULL [Accessorial amount #1] nvarchar(250) NULL [Accessorial code #2] nvarchar(250) NULL [Accessorial amount #2] nvarchar(250) NULL [Accessorial code #3] nvarchar(250) NULL [Accessorial amount #3] nvarchar(250) NULL [Accessorial code #4] nvarchar(250) NULL [Accessorial amount #4] nvarchar(250) NULL [Accessorial code #5] nvarchar(250) NULL [Accessorial amount #5] nvarchar(250) NULL [Accessorial code #6] nvarchar(250) NULL [Accessorial amount #6] nvarchar(250) NULL [Allowance amount, dollars] nvarchar(250) NULL [Allowance percentage] nvarchar(250) NULL [Double dip amount, dollars] nvarchar(250) NULL [Double dip percentage] nvarchar(250) NULL [Print status] nvarchar(250) NULL [Relay/intermediate terminal ID] nvarchar(250) NULL [Century of delivery to R/T] nvarchar(250) NULL [Not used] nvarchar(250) NULL [Time of delivery to R/T] nvarchar(250) NULL [Road manifest OT] nvarchar(250) NULL [Road manifest number] nvarchar(250) NULL [City manifest OT] nvarchar(250) NULL [City manifest number] nvarchar(250) NULL [Number of bring-backs] nvarchar(250) NULL [Last bring-back code] nvarchar(250) NULL [Delivered to customer code] nvarchar(250) NULL [Delivery century] nvarchar(250) NULL [Not used1] nvarchar(250) NULL [Delivery code] nvarchar(250) NULL [Delivery suffix] nvarchar(250) NULL [Cross-reference pro OT] nvarchar(250) NULL [Cross-reference pro number] nvarchar(250) NULL [Suffix code] nvarchar(250) NULL [Pro control status] nvarchar(250) NULL [Pro day sequence code] nvarchar(250) NULL [Company code] nvarchar(250) NULL [Bill-to address] nvarchar(250) NULL [Bill-to code] nvarchar(250) NULL [Actual billed-to code] nvarchar(250) NULL [Total calculated weight] nvarchar(250) NULL [Total rated weight] nvarchar(250) NULL [Pro entry century] nvarchar(250) NULL [Not used2] nvarchar(250) NULL [Pro entry time, HHMMSS] nvarchar(250) NULL [Entry person ID] nvarchar(250) NULL [Century revenue recorded] nvarchar(250) NULL [Not used3] nvarchar(250) NULL [Origin terminal revenue taken] nvarchar(250) NULL [Destination terminal revenue taken] nvarchar(250) NULL [Origin terminal revenue adjustment] nvarchar(250) NULL [Destination terminal revenue adjust] nvarchar(250) NULL [Allowance Basing Code] nvarchar(250) NULL [Billing century] nvarchar(250) NULL [Not used4] nvarchar(250) NULL [Billed amount] nvarchar(250) NULL [Billing status code] nvarchar(250) NULL [Accessorial Audit Flag] nvarchar(250) NULL [Billing type code] nvarchar(250) NULL [COD Terms (P/C)] nvarchar(250) NULL [COD Fee] nvarchar(250) NULL [Hold billing code] nvarchar(250) NULL [Void billing code] nvarchar(250) NULL [Prepaid/collect billing] nvarchar(250) NULL [Allowance test code] nvarchar(250) NULL [Payable status code] nvarchar(250) NULL [Interline payable carrier code] nvarchar(250) NULL [Interline payable setup century] nvarchar(250) NULL [Not used5] nvarchar(250) NULL [Interline payable amount] nvarchar(250) NULL [Coding status for prepaids] nvarchar(250) NULL [Coding status for collects] nvarchar(250) NULL [Coding status for consignees/prepai] nvarchar(250) NULL [Extra status code 1] nvarchar(250) NULL [Extra status code 2] nvarchar(250) NULL [Extra status code 3] nvarchar(250) NULL [Extra status code 4] nvarchar(250) NULL [Total accessorial amount from FA] nvarchar(250) NULL [Extra amount code 6] nvarchar(250) NULL [# OF PIPES] nvarchar(250) NULL [# OF DRUMS] nvarchar(250) NULL [# OF PALLETS] nvarchar(250) NULL [# OF UNPALLETIZED PIECES] nvarchar(250) NULL [FB MATCHED CONSIGNEE NUMBER] nvarchar(250) NULL [FROM CARTAGE CARRIER CODE] nvarchar(250) NULL [FROM CARTAGE CHARGE] nvarchar(250) NULL [FROM CARTAGE BILLING CODE] nvarchar(250) NULL [TO CARTAGE CARRIER CODE] nvarchar(250) NULL [TO CARTAGE CHARGE] nvarchar(250) NULL [TO CARTAGE BILLING CODE] nvarchar(250) NULL [Century of Interline dlvy] nvarchar(250) NULL [Not used6] nvarchar(250) NULL [Int. Delv. Carrier Scac] nvarchar(250) NULL [Int. Carrier Dlv Pro.] nvarchar(250) NULL [Century of Intlin Appt] nvarchar(250) NULL [Not used7] nvarchar(250) NULL [Int. Delivery Status code..] nvarchar(250) NULL [Int. Delivery Notify Code..] nvarchar(250) NULL [Bill to name] nvarchar(250) NULL [Bill to address 1] nvarchar(250) NULL [Bill to address 2] nvarchar(250) NULL [Bill to city] nvarchar(250) NULL [Bill to state] nvarchar(250) NULL [Bill to zip code] nvarchar(250) NULL [Bill to zip + 4 code] nvarchar(250) NULL [Total Cube] nvarchar(250) NULL [Class Revenue] nvarchar(250) NULL [Hazardous Flag] nvarchar(250) NULL [Freezable Flag] nvarchar(250) NULL [Guaranteed/Time Definite ID] nvarchar(250) NULL [Hi Value Code] nvarchar(250) NULL [Shippers P.O. Number] nvarchar(250) NULL [Shippers Dept Number] nvarchar(250) NULL [Forced Allowance Percentage] nvarchar(250) NULL [Rating Aggregate Code] nvarchar(250) NULL [Rating Aggregate Wgt] nvarchar(250) NULL [Shp Ldg All Amt$] nvarchar(250) NULL [Shp Ldg All Pct] nvarchar(250) NULL [Cns Unldg All Amt$] nvarchar(250) NULL [Cns Undgl All Pct] nvarchar(250) NULL [3rd Pty Comm. Amt$] nvarchar(250) NULL [3rd Pty Comm. Pct] nvarchar(250) NULL [Special Exemption Code] nvarchar(250) NULL [PRE-APPT/HOT FLAG] nvarchar(250) NULL [Auto Rate Base Suf] nvarchar(250) NULL [Auto Rate Base No.] nvarchar(250) NULL [Auto Rate Tariff#] nvarchar(250) NULL [Rated by-Cust Code] nvarchar(250) NULL [Rated by-Inb/Otb/3rd] nvarchar(250) NULL [Manual Rate Code] nvarchar(250) NULL [Custom Rated Code] nvarchar(250) NULL [Auto Re-Rate Flag] nvarchar(250) NULL [Int. From-Orig Shipper Code] nvarchar(250) NULL [Int. To-Finl Consgne Code] nvarchar(250) NULL [Special Customer Ref] nvarchar(250) NULL [Pickup driver run1] nvarchar(250) NULL [Accessorial Status #1] nvarchar(250) NULL [Accessorial Status #2] nvarchar(250) NULL [Accessorial Status #3] nvarchar(250) NULL [Accessorial Status #4] nvarchar(250) NULL [Accessorial Status #5] nvarchar(250) NULL [Accessorial Status #6] nvarchar(250) NULL [LAST UPDATE USER] nvarchar(250) NULL [Time Stamp] nvarchar(250) NULL [Billing date] nvarchar(250) NULL [Int. Delv Appt Date] nvarchar(250) NULL [Delivery date] nvarchar(250) NULL [Interline Dlvy Date] nvarchar(250) NULL [Interline payable setup date] nvarchar(250) NULL [Pro entry date] nvarchar(250) NULL [Date revenue recorded] nvarchar(250) NULL [Date of delivery to R/T] nvarchar(250) NULL [Biller SSN#] nvarchar(250) NULL [PICKUP NUMBER] nvarchar(250) NULL [MANUAL FORCED DISCOUNT%] nvarchar(250) NULL [RATE STATUS BYTE#1] nvarchar(250) NULL [RATE STATUS BYTE#2] nvarchar(250) NULL [Adjstd Date of pickup] nvarchar(250) NULL [MIN RATED PRO] nvarchar(250) NULL [Hot/Committed Date] nvarchar(250) NULL [Time Open] nvarchar(250) NULL [Time Close] nvarchar(250) NULL [Distribution Pickup Label # ] nvarchar(250) NULL [Projected Receive Date] nvarchar(250) NULL [Distribution SCAC Code] nvarchar(250) NULL [Linehaul Trailer #] nvarchar(250) NULL [Actual Pickup Date] nvarchar(250) NULL [Quote Reference #] nvarchar(250) NULL [Message ID Term Abrv] nvarchar(250) NULL [Message Sequence #] nvarchar(250) NULL [Break Bulk Msg Seq #] nvarchar(250) NULL [Column223] nvarchar(250) NULL )', N'U ', N'test4')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [abctest123] ( [lat] varchar(500) NULL [long] varchar(500) NULL [add] varchar(500) NULL [city] varchar(500) NULL [state] varchar(5000) NULL )', N'U ', N'test5')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [final] ( [id] int IDENTITY(1,1) NOT NULL [lat] varchar(500) NULL [long] varchar(500) NULL [add] varchar(500) NULL [city] varchar(500) NULL [state] varchar(5000) NULL )', N'U ', N'test6')
GO
INSERT [dbo].[testobjects11] ([ObjectText], [ObjectType], [ObjectName]) VALUES (N'create table [emp] ( [id] int NULL [salesmoney] money NULL [salesdecimal] decimal(10, 2) NULL [salesnum] numeric NULL )', N'U ', N'test7')
GO
  I want load this testobjects11 table data into object.txt file(destination)

  I have tried like below steps: step1 : drag and drop oledb source and connfigure to soruce table(testobjects11) after that I have used derived column task and write expression like (DT_NTEXT)ObjectText after that I drag and drop the flatfile destination task and configure to the file after that I execute the ssis package ,but package is faieled . I got error like below:

  [Flat File Destination [12]] Error: The data type for "Flat File Destination.Inputs[Flat File Destination Input].Columns[ObjectText]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
  [SSIS.Pipeline] Error: "Flat File Destination" failed validation and returned validation status "VS_ISBROKEN".
  can you please tell me how to achive this task in ssis

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  October 28, 2017 at 8:59 am

  #1965672

  What is the error if you use DT_TEXT?

  ---