Creating a txt file via DTS

[virtualranger]

I need to create a .txt file for uploading some of our sql server based data into another comapany's database (non-sql). It's a daily upload so the file creation needs to be automated.

The file needs to consist of a header, a definition (detailing the data attributes), and the data itself. The Header, and definition are constant, it is only the data section that will change on each upload.

E.g. The file content will look exactly like this:

#HEADER#
EOF:'^'
EOR:'~'

#DEFINITION#
AGENT_REF^DATE^PROPERTY_ID^ADDR1^TOWN^COUNTY^

#DATA#
RQ12345^12/01/2005^00001^11 SOUTH STREET^TAUNTON^SOMERSET~
RQ12345^12/01/2005^00002^43 NORTH STREET^TAUNTON^SOMERSET~
RQ12345^12/01/2005^00003^18 WEST STREET^TAUNTON^SOMERSET~

#END#

I have no idea of the best way to do this! I can get the data out into a txt file with all the correct separators ('^' and '~') by using bcp command and xp_cmdshell, but I have no idea how to automatically insert the header and definition. Does anyone know if is possible to do the whole thing via DTS, or perhaps an alternative method?

Thanks,
Jamie

 

[DrSql]

Make sure you create this pacakge on the SQL Server via terminal server or some other tools.

Goto Sql Server drill down to Data Trasnsformation Services.
Right Click on Local Packages and select New Packages

Source:
Select from Connections Menu and by click "Microsoft OLE DB Provider for SQL Server". Fill in your server details, userid, password and select the db you want to import the data from.

Destination:
Select from Connections Menu and by click "Text File (Destination)". Browse to the file locations and name the file. Got propertiies you have few options to set as you desire with your specs.

Now you have Source and destination.

Proceed to Task menu and select "Transform Data Task"

It will ask you to celect your source and then destination.
Double click on the Trasnformation arrow.
At the source tab clcik on sql query
and paste the sql statement that you used in you BCPO statement. Click on destination, Populate the source and Execute. Clcik on the Transformations and select DTSTransformation_1.
Click ok.
From the top bar you can see the execute button.

You can save this and schdule this job by right click on the newly created package.


Dr.Sql
Good Luck.

 

[virtualranger]

Sorry, you have misunderstood. I know how to do basic export via DTS, thats all fine for the data, but how do I automatically insert the static text insert into the top of the file above the data:

#HEADER#
EOF:'^'
EOR:'~'

#DEFINITION#
AGENT_REF^DATE^PROPERTY_ID^ADDR1^TOWN^COUNTY^

Ta


Thanks,
Jamie

 

[DrSql]

You might need to think about creating a sp. and execute that create a file, and execute the DTS pacakage. Thats qucik over view.

Setp1
Create a Temp table #header
Insert the header values in to #header
Step 2.
Body in Temp table #body
Insert body values in to #body
setp3
Footer in Temp table #Footer
Insert footer values in to #footer

Step 4
Create a table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[data]
GO

CREATE TABLE [dbo].[data] (
[Id] [int] NULL ,
[Body] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Insert into data values(1,@header)
go
Insert into data values(2,@body)
go
Insert into data values(1,@footer)

Now you have all header, body and footer in one table.

Then use a DTS to exeport into TXT.

Select Body from Data order by ID should give you the out values as you prefer.

Any clarificatiosn let me know,




Dr.Sql
Good Luck.

 

[virtualranger]

Excellent, I think I understand that, I'll give it a try.

Thanks,
Jamie