drop table #a
go
create table #a (i int, j int, BlockType varchar(25), s varchar(1000))

delete #a
declare @i int
insert #a
select top 1 id, id, 'DatabaseConnection', s from ExportPackageTest where s like '%DTS:ConnectionString=%'
insert #a
select top 1 id, id, 'FileConnection', s from ExportPackageTest where s like '%DTS:ConnectionString=%' and id > (select max(i) from #a)
select top 1 @i = id+1 from ExportPackageTest where s like '%<DTS:FlatFileColumns%' and id > (select max(i) from #a)
insert #a
select top 1 id, id, 'FileCol1', s from ExportPackageTest where id = @i
select top 1 @i = id
from  ExportPackageTest
where s like '%</DTS:FlatFileColumns>%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)

select top 1 @i = id+1 from ExportPackageTest where s like '%<inputColumn%' and id > (select max(i) from #a)
insert #a
select top 1 id, id, 'TableCol1', s from ExportPackageTest where id = @i
select top 1 @i = id
from  ExportPackageTest
where s like '%</inputColumns>%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)
select top 1 @i = id+2 from ExportPackageTest where s like '%<externalMetadataColumn%' and id > (select max(i) from #a)
insert #a
select top 1 id, id, 'FileCol2', s from ExportPackageTest where id = @i
select top 1 @i = id
from  ExportPackageTest
where s like '%</externalMetadataColumns>%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)
select top 1 @i = id+1 from ExportPackageTest where s like '%description="Specifies the name of the database object used to open a rowset."%' and id > (select max(i) from #a)
insert #a
select id, id, 'TableName', s from ExportPackageTest where id = @i
select top 1 @i = id+1 from ExportPackageTest where s like '%<outputColumns>%' and id > (select max(i) from #a)
insert #a
select id, id, 'ColMap1', s from ExportPackageTest where id = @i
select top 1 @i = id from ExportPackageTest where s like '%</outputColumns>%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)
select top 1 @i = id+2 from ExportPackageTest where s like '%<externalMetadataColumns%' and id > (select max(i) from #a)
insert #a
select id, id, 'ColMap2', s from ExportPackageTest where id = @i
select top 1 @i = id from ExportPackageTest where s like '%</externalMetadataColumns>%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)
select top 1 @i = id+1 from ExportPackageTest where s like '%<outputColumns>%' and id > (select max(i) from #a)
insert #a
select id, id, 'TableCol2', s from ExportPackageTest where id = @i
select top 1 @i = id from ExportPackageTest where s like '%<outputColumn%' and id > (select max(i) from #a)
update #a set j = @i-1 where i = (select max(i) from #a)


drop table Export_Package_Template
go
create table Export_Package_Template
	(
		seq int primary key
		, s varchar(1000)
	)
go

drop table Export_Package_Template_Replace
go
create table Export_Package_Template_Replace
	(
		seq1 int
		, seq2 int
		, Type1 varchar(25)
		, Type2 varchar(25)
		, s varchar(1000)
		, primary key (seq1, seq2)
	)
go

insert Export_Package_Template_Replace
select b.i, a.id, b.BlockType
		,	case when BlockType in ('FileCol1','FileCol2','ColMap1','ColMap2','TableCol1','TableCol2')
					then 'varchar'
				else ''
			end
		,	case 
				when BlockType = 'DatabaseConnection' then 'DTS:ConnectionString="Data Source=||ServerName||;Initial Catalog=||DatabaseName||;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />'
				when BlockType = 'FileConnection' then 'DTS:ConnectionString="||FileName||">'
				when BlockType = 'TableName' then 'name="OpenRowset">[||SchemName||].[||TableName||]</property>'
				when BlockType in ('FileCol1','FileCol2','ColMap1','ColMap2','TableCol1','TableCol2') then replace(replace(replace(a.s,'c001','||colname||'),'20','||colsize||'),'_x000D__x000A_','||coldelim||')
				else a.s
			end
from ExportPackageTest a
	join #a b
		on a.id between b.i and b.j


insert Export_Package_Template
select a.id, a.s
from ExportPackageTest a
	left join #a b
		on a.id between b.i and b.j
where b.i is null

/*
Results

select * from #a
i           j           BlockType                 s
----------- ----------- ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
26          26          DatabaseConnection                  DTS:ConnectionString="Data Source=DESKTOP-D6M6JCB;Initial Catalog=SSIS_Test;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
43          43          FileConnection                      DTS:ConnectionString="C:\Users\nigel\OneDrive\Nigel\Articles\SSIS_FileExport\FileExport_c001.txt">
45          53          FileCol1                              <DTS:FlatFileColumn
105         112         TableCol1                                     <inputColumn
116         121         FileCol2                                      <externalMetadataColumn
142         142         TableName                                   name="OpenRowset">[dbo].[ExportTest_c001]</property>
187         197         ColMap1                                       <outputColumn
201         206         ColMap2                                       <externalMetadataColumn
214         220         TableCol2                                     <outputColumn


select * from Export_Package_Template
seq         s
----------- -----------------------------------------------------------------
1           <?xml version="1.0"?>
2           <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
3             DTS:refId="Package"
4             DTS:CreationDate="8/12/2019 12:09:30 AM"
5             DTS:CreationName="Microsoft.Package"
6             DTS:CreatorComputerName="DESKTOP-D6M6JCB"
7             DTS:CreatorName="DESKTOP-D6M6JCB\nigel"
8             DTS:DTSID="{9793F500-A2D7-4FE6-9E6F-17F6416D92F2}"
9             DTS:ExecutableType="Microsoft.Package"
10            DTS:LastModifiedProductVersion="12.0.4100.1"
11            DTS:LocaleID="2057"
12            DTS:ObjectName="Export_c001"
13            DTS:PackageType="5"
14            DTS:VersionBuild="3"
15            DTS:VersionGUID="{9881842A-8552-4663-8B96-592678AE5D03}">
16            <DTS:Property
17              DTS:Name="PackageFormatVersion">8</DTS:Property>
18            <DTS:ConnectionManagers>
19              <DTS:ConnectionManager
20                DTS:refId="Package.ConnectionManagers[DESKTOP-D6M6JCB.SSIS_Test]"
21                DTS:CreationName="OLEDB"
22                DTS:DTSID="{5BC03D55-BF91-4B20-87EF-DBC3004AAA06}"
23                DTS:ObjectName="DESKTOP-D6M6JCB.SSIS_Test">
24                <DTS:ObjectData>
25                  <DTS:ConnectionManager
27                </DTS:ObjectData>
28              </DTS:ConnectionManager>
29              <DTS:ConnectionManager
30                DTS:refId="Package.ConnectionManagers[Flat File Connection Manager]"
31                DTS:CreationName="FLATFILE"
32                DTS:DTSID="{2D7F11E2-8EF0-474B-B453-134722FEAF19}"
33                DTS:ObjectName="Flat File Connection Manager">
34                <DTS:ObjectData>
35                  <DTS:ConnectionManager
36                    DTS:Format="Delimited"
37                    DTS:LocaleID="2057"
38                    DTS:HeaderRowDelimiter="_x000D__x000A_"
39                    DTS:ColumnNamesInFirstDataRow="True"
40                    DTS:RowDelimiter=""
41                    DTS:TextQualifier="_x0022_"
42                    DTS:CodePage="1252"
44                    <DTS:FlatFileColumns>
54                    </DTS:FlatFileColumns>
55                  </DTS:ConnectionManager>
56                </DTS:ObjectData>
57              </DTS:ConnectionManager>
58            </DTS:ConnectionManagers>
59            <DTS:Variables />
60            <DTS:Executables>
61              <DTS:Executable
62                DTS:refId="Package\Data Flow Task"
63                DTS:CreationName="Microsoft.Pipeline"
64                DTS:Description="Data Flow Task"
65                DTS:DTSID="{8CE50240-0F45-4D33-A5E2-41C9A3048943}"
66                DTS:ExecutableType="Microsoft.Pipeline"
67                DTS:LocaleID="-1"
68                DTS:ObjectName="Data Flow Task">
69                <DTS:Variables />
70                <DTS:ObjectData>
71                  <pipeline
72                    version="1">
73                    <components>
74                      <component
75                        refId="Package\Data Flow Task\Flat File Destination"
76                        componentClassID="Microsoft.FlatFileDestination"
77                        contactInfo="Flat File Destination;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0"
78                        description="Flat File Destination"
79                        localeId="2057"
80                        name="Flat File Destination">
81                        <properties>
82                          <property
83                            dataType="System.Boolean"
84                            description="Specifies whether the data will overwrite or append to the destination file."
85                            name="Overwrite">true</property>
86                          <property
87                            dataType="System.Null"
88                            description="Specifies the text to write to the destination file before any data is written."
89                            expressionType="Notify"
90                            name="Header" />
91                        </properties>
92                        <connections>
93                          <connection
94                            refId="Package\Data Flow Task\Flat File Destination.Connections[FlatFileConnection]"
95                            connectionManagerID="Package.ConnectionManagers[Flat File Connection Manager]"
96                            connectionManagerRefId="Package.ConnectionManagers[Flat File Connection Manager]"
97                            name="FlatFileConnection" />
98                        </connections>
99                        <inputs>
100                         <input
101                           refId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input]"
102                           hasSideEffects="true"
103                           name="Flat File Destination Input">
104                           <inputColumns>
113                           </inputColumns>
114                           <externalMetadataColumns
115                             isUsed="True">
122                           </externalMetadataColumns>
123                         </input>
124                       </inputs>
125                     </component>
126                     <component
127                       refId="Package\Data Flow Task\OLE DB Source"
128                       componentClassID="Microsoft.OLEDBSource"
129                       contactInfo="OLE DB Source;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7"
130                       description="OLE DB Source"
131                       name="OLE DB Source"
132                       usesDispositions="true"
133                       version="7">
134                       <properties>
135                         <property
136                           dataType="System.Int32"
137                           description="The number of seconds before a command times out.  A value of 0 indicates an infinite time-out."
138                           name="CommandTimeout">0</property>
139                         <property
140                           dataType="System.String"
141                           description="Specifies the name of the database object used to open a rowset."
143                         <property
144                           dataType="System.String"
145                           description="Specifies the variable that contains the name of the database object used to open a rowset."
146                           name="OpenRowsetVariable"></property>
147                         <property
148                           dataType="System.String"
149                           description="The SQL command to be executed."
150                           name="SqlCommand"
151                           UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor"></property>
152                         <property
153                           dataType="System.String"
154                           description="The variable that contains the SQL command to be executed."
155                           name="SqlCommandVariable"></property>
156                         <property
157                           dataType="System.Int32"
158                           description="Specifies the column code page to use when code page information is unavailable from the data source."
159                           name="DefaultCodePage">1252</property>
160                         <property
161                           dataType="System.Boolean"
162                           description="Forces the use of the DefaultCodePage property value when describing character data."
163                           name="AlwaysUseDefaultCodePage">false</property>
164                         <property
165                           dataType="System.Int32"
166                           description="Specifies the mode used to access the database."
167                           name="AccessMode"
168                           typeConverter="AccessMode">0</property>
169                         <property
170                           dataType="System.String"
171                           description="The mappings between the parameters in the SQL command and variables."
172                           name="ParameterMapping"></property>
173                       </properties>
174                       <connections>
175                         <connection
176                           refId="Package\Data Flow Task\OLE DB Source.Connections[OleDbConnection]"
177                           connectionManagerID="Package.ConnectionManagers[DESKTOP-D6M6JCB.SSIS_Test]"
178                           connectionManagerRefId="Package.ConnectionManagers[DESKTOP-D6M6JCB.SSIS_Test]"
179                           description="The OLE DB runtime connection used to access the database."
180                           name="OleDbConnection" />
181                       </connections>
182                       <outputs>
183                         <output
184                           refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output]"
185                           name="OLE DB Source Output">
186                           <outputColumns>
198                           </outputColumns>
199                           <externalMetadataColumns
200                             isUsed="True">
207                           </externalMetadataColumns>
208                         </output>
209                         <output
210                           refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output]"
211                           isErrorOut="true"
212                           name="OLE DB Source Error Output">
213                           <outputColumns>
221                             <outputColumn
222                               refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorCode]"
223                               dataType="i4"
224                               lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorCode]"
225                               name="ErrorCode"
226                               specialFlags="1" />
227                             <outputColumn
228                               refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorColumn]"
229                               dataType="i4"
230                               lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorColumn]"
231                               name="ErrorColumn"
232                               specialFlags="2" />
233                           </outputColumns>
234                           <externalMetadataColumns />
235                         </output>
236                       </outputs>
237                     </component>
238                   </components>
239                   <paths>
240                     <path
241                       refId="Package\Data Flow Task.Paths[OLE DB Source Output]"
242                       endId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input]"
243                       name="OLE DB Source Output"
244                       startId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output]" />
245                   </paths>
246                 </pipeline>
247               </DTS:ObjectData>
248             </DTS:Executable>
249           </DTS:Executables>
250           <DTS:DesignTimeProperties><![CDATA[<?xml version="1.0"?>
251         <!--This CDATA section contains the layout information of the package. The section includes information such as (x,y) coordinates, width, and height.-->
252         <!--If you manually edit this section and make a mistake, you can delete it. -->
253         <!--The package will still be able to load normally but the previous layout information will be lost and the designer will automatically re-arrange the elements on the design surface.-->
254         <Objects
255           Version="sql12">
256           <!--Each node below will contain properties that do not affect runtime behavior.-->
257           <Package
258             design-time-name="Package">
259             <LayoutInfo>
260               <GraphLayout
261                 Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph">
262                 <NodeLayout
263                   Size="151,42"
264                   Id="Package\Data Flow Task"
265                   TopLeft="5.5,5.5" />
266               </GraphLayout>
267             </LayoutInfo>
268           </Package>
269           <TaskHost
270             design-time-name="Package\Data Flow Task">
271             <LayoutInfo>
272               <GraphLayout
273                 Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph" xmlns:mssgle="clr-namespace:Microsoft.SqlServer.Graph.LayoutEngine;assembly=Microsoft.Sql
274                 <NodeLayout
275                   Size="150,42"
276                   Id="Package\Data Flow Task\OLE DB Source"
277                   TopLeft="378,113" />
278                 <NodeLayout
279                   Size="172,42"
280                   Id="Package\Data Flow Task\Flat File Destination"
281                   TopLeft="371,298" />
282                 <EdgeLayout
283                   Id="Package\Data Flow Task.Paths[OLE DB Source Output]"
284                   TopLeft="455,155">
285                   <EdgeLayout.Curve>
286                     <mssgle:Curve
287                       StartConnector="{assembly:Null}"
288                       EndConnector="0,143"
289                       Start="0,0"
290                       End="0,135.5">
291                       <mssgle:Curve.Segments>
292                         <mssgle:SegmentCollection
293                           Capacity="5">
294                           <mssgle:LineSegment
295                             End="0,135.5" />
296                         </mssgle:SegmentCollection>
297                       </mssgle:Curve.Segments>
298                     </mssgle:Curve>
299                   </EdgeLayout.Curve>
300                   <EdgeLayout.Labels>
301                     <EdgeLabelCollection />
302                   </EdgeLayout.Labels>
303                 </EdgeLayout>
304               </GraphLayout>
305             </LayoutInfo>
306           </TaskHost>
307           <PipelineComponentMetadata
308             design-time-name="Package\Data Flow Task\OLE DB Source">
309             <Properties>
310               <Property>
311                 <Name>DataSourceViewID</Name>
312               </Property>
313               <Property>
314                 <Name>TableInfoObjectType</Name>
315                 <Value
316                   type="q2:string">Table</Value>
317               </Property>
318             </Properties>
319           </PipelineComponentMetadata>
320         </Objects>]]></DTS:DesignTimeProperties>
321         </DTS:Executable>


select * from Export_Package_Template_Replace

Result
seq1        seq2        Type1                     Type2                     s
----------- ----------- ------------------------- ------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------
26          26          DatabaseConnection                                  DTS:ConnectionString="Data Source=||ServerName||;Initial Catalog=||DatabaseName||;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
43          43          FileConnection                                      DTS:ConnectionString="||FileName||">
45          45          FileCol1                  varchar                               <DTS:FlatFileColumn
45          46          FileCol1                  varchar                                 DTS:ColumnType="Delimited"
45          47          FileCol1                  varchar                                 DTS:ColumnDelimiter="||coldelim||"
45          48          FileCol1                  varchar                                 DTS:MaximumWidth="||colsize||"
45          49          FileCol1                  varchar                                 DTS:DataType="129"
45          50          FileCol1                  varchar                                 DTS:TextQualified="True"
45          51          FileCol1                  varchar                                 DTS:ObjectName="||colname||"
45          52          FileCol1                  varchar                                 DTS:DTSID="{008EBA94-7184-465B-A6FC-A74341888919}"
45          53          FileCol1                  varchar                                 DTS:CreationName="" />
105         105         TableCol1                 varchar                                       <inputColumn
105         106         TableCol1                 varchar                                         refId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].Columns[||colname||]"
105         107         TableCol1                 varchar                                         cachedCodepage="1252"
105         108         TableCol1                 varchar                                         cachedDataType="str"
105         109         TableCol1                 varchar                                         cachedLength="||colsize||"
105         110         TableCol1                 varchar                                         cachedName="||colname||"
105         111         TableCol1                 varchar                                         externalMetadataColumnId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].ExternalColumns[||colname||]"
105         112         TableCol1                 varchar                                         lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[||colname||]" />
116         116         FileCol2                  varchar                                       <externalMetadataColumn
116         117         FileCol2                  varchar                                         refId="Package\Data Flow Task\Flat File Destination.Inputs[Flat File Destination Input].ExternalColumns[||colname||]"
116         118         FileCol2                  varchar                                         codePage="1252"
116         119         FileCol2                  varchar                                         dataType="str"
116         120         FileCol2                  varchar                                         length="||colsize||"
116         121         FileCol2                  varchar                                         name="||colname||" />
142         142         TableName                                           name="OpenRowset">[||SchemName||].[||TableName||]</property>
187         187         ColMap1                   varchar                                       <outputColumn
187         188         ColMap1                   varchar                                         refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[||colname||]"
187         189         ColMap1                   varchar                                         codePage="1252"
187         190         ColMap1                   varchar                                         dataType="str"
187         191         ColMap1                   varchar                                         errorOrTruncationOperation="Conversion"
187         192         ColMap1                   varchar                                         errorRowDisposition="FailComponent"
187         193         ColMap1                   varchar                                         externalMetadataColumnId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[||colname||]"
187         194         ColMap1                   varchar                                         length="||colsize||"
187         195         ColMap1                   varchar                                         lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].Columns[||colname||]"
187         196         ColMap1                   varchar                                         name="||colname||"
187         197         ColMap1                   varchar                                         truncationRowDisposition="FailComponent" />
201         201         ColMap2                   varchar                                       <externalMetadataColumn
201         202         ColMap2                   varchar                                         refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[||colname||]"
201         203         ColMap2                   varchar                                         codePage="1252"
201         204         ColMap2                   varchar                                         dataType="str"
201         205         ColMap2                   varchar                                         length="||colsize||"
201         206         ColMap2                   varchar                                         name="||colname||" />
214         214         TableCol2                 varchar                                       <outputColumn
214         215         TableCol2                 varchar                                         refId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[||colname||]"
214         216         TableCol2                 varchar                                         codePage="1252"
214         217         TableCol2                 varchar                                         dataType="str"
214         218         TableCol2                 varchar                                         length="||colsize||"
214         219         TableCol2                 varchar                                         lineageId="Package\Data Flow Task\OLE DB Source.Outputs[OLE DB Source Error Output].Columns[||colname||]"
214         220         TableCol2                 varchar                                         name="||colname||" />
*/