Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a file

Author  Topic 

Lightbug3
Starting Member

6 Posts
Posted - 2006-06-28 : 13:23:59
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 - 2006-06-28 : 13:38:06
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 - 2006-06-28 : 14:39:55
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 - 2006-06-29 : 10:43:13
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 - 2006-06-29 : 13:00:32
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 - 2007-04-24 : 16:08:23
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site