SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a file		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lightbug3
Starting Member

6 Posts
Posted - 06/28/2006 :  13:23:59  Show Profile  Reply with Quote
Hi All!

I am still trying to understand all the differences between SQL 2000 and 2005. This question probably has a really easy answer, but I just don't know it. I need to create a DTS routine that creates a file from a table. The catch is, it needs to have a header segment, data segment, and footer segment. In SQL 2000, I would have created activex script to create this file. Since SQL 2005 is going away from activex script I am at a loss.

The file will be in this format.
Header
Data
Data
Data
Data
Data
Data
Footer

Basically output header from table... loop through table for data... and output footer from table.

If anyone knows how to do this, I would appreciate the help.
Thanks.
Danielle

X002548
Not Just a Number

15586 Posts
Posted - 06/28/2006 :  13:38:06  Show Profile  Reply with Quote
There is no DTS in 2k5

Got any details about the header and trailer, or some DDL of the table you are going against, and maybe some sample data?

Read the hint link in my sig, but I think I can get you a solution that would work rather nicely with no looping or cursors



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Lightbug3
Starting Member

6 Posts
Posted - 06/28/2006 :  14:39:55  Show Profile  Reply with Quote
I meant SSIS, not DTS. I am so used to DTS. :-)

The header record is just hard coded data. There is nothing special about it. The trailor record contains the sum of all the detail records and some hard coded data. The detail records come from the table listed below.

CREATE TABLE [dbo].[Member](
[MemberID] [bigint] IDENTITY(1000000,1) NOT NULL,
[PlanID] [smallint] NOT NULL,
[Prefix] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MI] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DOB] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ZipCode] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MAddress1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAddress2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MZipCode] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EContact] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EPhone] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERelationship] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSN] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClaimNumber] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PartAEffDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PartBEffDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DeductSSA] [bit] NOT NULL CONSTRAINT [DF_Member_DeductSSA] DEFAULT (0),
[OtherCoverage] [bit] NOT NULL CONSTRAINT [DF_Member_OtherCoverage] DEFAULT (0),
[OtherName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OtherID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OtherGroup] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LongTermCare] [bit] NULL CONSTRAINT [DF_Member_LongTermCare] DEFAULT (0),
[Institution] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IAddress1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IAddress2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ICity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IZipCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IPhone] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Representative] [bit] NULL,
[RName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAddress1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAddress2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RZipCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPhone] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RRelationship] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_Member_CreatedDate] DEFAULT (getdate()),
[ModifiedDate] [datetime] NULL,
[ExportDate] [datetime] NULL,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[MemberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The Query I am using to get the detail lines is below. Note: This query will eventually join with other tables... I don't know if that matters.

Select Convert(varchar(12), ClaimNumber) AS HICN,
Convert(varchar(12),LastName) AS LastName,
Convert(varchar(7), FirstName) AS FirstName,
Convert(varchar(1), MI) AS MI,
CASE Gender WHEN 'M' THEN '1' WHEN 'F' THEN '2' ELSE '0' END AS Gender,
CONVERT(varchar(8),REPLACE(CONVERT(varchar(10), DOB, 101),'/','')) AS DOB,
CONVERT(varchar(1), '') AS EGHP, --Don't know what to put here
CONVERT(varchar(3), '') AS PBPN, --Don't know what to put here
CONVERT(varchar(1), '') AS ElectionType, --Don't know what to put here
CONVERT(varchar(5), '') AS ContractNum, --Don't know what to put here
CONVERT(varchar(8),REPLACE(CONVERT(varchar(10), CreatedDate, 101),'/','')) AS ApplicationDate,
CONVERT(varchar(2), '') AS TransactionCode, --Don't know what to put here
CONVERT(varchar(2), '') AS DisenrollmentReason,
CONVERT(varchar(8),REPLACE(CONVERT(varchar(10), CreatedDate, 101),'/','')) AS EffectiveDate, --Don't know where I am getting this yet
CONVERT(varchar(3), '') AS SegmentID, --Don't know what to put here
CONVERT(varchar(5), '') AS Filler1,
CONVERT(varchar(1), '') AS PCO, --Don't know what to put here
CONVERT(varchar(1), '') AS PWOP, --Don't know what to put here
CONVERT(varchar(6), '') AS CAmount, --Don't know where I am getting this yet
CONVERT(varchar(6), '') AS DAmount, --Don't know where I am getting this yet
CONVERT(varchar(1), '') AS CoverageFlag, --Don't know where I am getting this yet
CONVERT(varchar(3), '') AS UncoveredMonths, --Don't know where I am getting this yet
CONVERT(varchar(1), '') AS EmpSubsidyFlag, --Don't know where I am getting this yet
CONVERT(varchar(1), '') AS DOptOut, --Don't know what to put here
CONVERT(varchar(20), '') AS Filler2,
CONVERT(varchar(15), '') AS Filler3,
CONVERT(varchar(1), '') AS SecDrugInsFlag, --Don't know where I am getting this yet
CONVERT(varchar(20), '') AS SecRxID, --Don't know where I am getting this yet
CONVERT(varchar(15), '') AS SecRxGroup, --Don't know where I am getting this yet
CONVERT(varchar(1), '') AS EnrollmentSource,
CONVERT(varchar(9), '') AS SSN,
CONVERT(varchar(9), '') AS TrustRouteNum,
CONVERT(varchar(17), '') AS BankNumber,
CONVERT(varchar(1), '') AS BankType,
CONVERT(varchar(17), '') AS Filler4
From Member
Where ExportDate is null

Thanks for your help.
Danielle
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/29/2006 :  10:43:13  Show Profile  Reply with Quote
I just reloaded SQL Server client tools...give me a minute for an example



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/29/2006 :  13:00:32  Show Profile  Reply with Quote
I would do this for example


USE Northwind
GO

CREATE VIEW EXPORT_ORDERS
AS
SELECT	1 AS ROW_ORDER, 
	 'HEADER ' 
	+ CONVERT(char(25),GetDate()) AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER,
	  COALESCE(CONVERT(char(15),OrderID),'')
	+ COALESCE(CustomerID,'')
	+ COALESCE(CONVERT(char(15),EmployeeID),'')
	+ COALESCE(CONVERT(char(25),OrderDate),'')
	+ COALESCE(CONVERT(char(25),RequiredDate),'')
	+ COALESCE(CONVERT(char(25),ShippedDate),'')
	+ COALESCE(CONVERT(char(15),ShipVia),'')
	+ COALESCE(CONVERT(char(15),Freight),'')
	+ COALESCE(CONVERT(char(80),ShipName),'')
	+ COALESCE(CONVERT(char(120),ShipAddress),'')
	+ COALESCE(CONVERT(char(30),ShipCity),'')
	+ COALESCE(CONVERT(char(30),ShipRegion),'')
	+ COALESCE(CONVERT(char(20),ShipPostalCode),'')
	+ COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line
  FROM Orders
UNION ALL
SELECT	3 AS ROW_ORDER,
	 'TRAILER ' 
	+ CONVERT(char(25),GetDate())
	+ CONVERT(char(15),COUNT(*)) AS Data_Line
  FROM Orders
GO

SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER

EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'
GO

DROP VIEW EXPORT_ORDERS
GO



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

scared
Starting Member

14 Posts
Posted - 04/24/2007 :  16:08:23  Show Profile  Reply with Quote
Brett:

Thank you so much for the export snippet below. I'm doing a DB conversion from SQL -> DB/2 and this looks very helpful; the sqlteam email function is disabled or I'd send you a question. Thanks again.

Lisa

quote:
Originally posted by X002548

I would do this for example


USE Northwind
GO

CREATE VIEW EXPORT_ORDERS
AS
SELECT	1 AS ROW_ORDER, 
	 'HEADER ' 
	+ CONVERT(char(25),GetDate()) AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER,
	  COALESCE(CONVERT(char(15),OrderID),'')
	+ COALESCE(CustomerID,'')
	+ COALESCE(CONVERT(char(15),EmployeeID),'')
	+ COALESCE(CONVERT(char(25),OrderDate),'')
	+ COALESCE(CONVERT(char(25),RequiredDate),'')
	+ COALESCE(CONVERT(char(25),ShippedDate),'')
	+ COALESCE(CONVERT(char(15),ShipVia),'')
	+ COALESCE(CONVERT(char(15),Freight),'')
	+ COALESCE(CONVERT(char(80),ShipName),'')
	+ COALESCE(CONVERT(char(120),ShipAddress),'')
	+ COALESCE(CONVERT(char(30),ShipCity),'')
	+ COALESCE(CONVERT(char(30),ShipRegion),'')
	+ COALESCE(CONVERT(char(20),ShipPostalCode),'')
	+ COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line
  FROM Orders
UNION ALL
SELECT	3 AS ROW_ORDER,
	 'TRAILER ' 
	+ CONVERT(char(25),GetDate())
	+ CONVERT(char(15),COUNT(*)) AS Data_Line
  FROM Orders
GO

SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER

EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'
GO

DROP VIEW EXPORT_ORDERS
GO



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000