Give something like the below a try...
You'll obviously need to plug in your variables for your environment, check the data types (may need to add logic to keep leading zeros?), change from the final temp tables to your regular table(s), etc.
Works fine for me for import from XML files to temp tables without deleting the files afterwards but adding logic to delete files from the UNC path shouldn't be too difficult with another xp_cmdshell command.
DECLARE @folder AS VARCHAR(1000) = '\\servername\sharename\folder\subfolder1\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)
-- create global temp table
IF OBJECT_ID('tempdb..##XMLImport') IS NOT NULL
DROP TABLE ##XMLImport
CREATE TABLE ##XMLImport (
R00000010 VARCHAR(7)
,R00000020 VARCHAR(1)
,R00000030 INT
,R00000040 INT
,R00000050 INT
,R00000060 INT
)
INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command
-- create cursor
DECLARE filecurs CURSOR
FOR
SELECT REPLACE(filenameswithfolder, @folder, '') AS filenames
FROM @filesinafolder
WHERE filenameswithfolder IS NOT NULL
OPEN filecurs
FETCH NEXT
FROM filecurs
INTO @file
IF @file = 'FILE NOT FOUND'
GOTO exitprocessing
WHILE @@fetch_status != - 1
BEGIN
SET @sql = 'DECLARE @X XML
SELECT @X = P
FROM OPENROWSET(BULK ''' + @folder + '' + @file + ''', SINGLE_BLOB) AS Products(P)
DECLARE @iX INT
EXEC sp_xml_preparedocument @iX OUTPUT
,@X
SELECT *
INTO #XMLResults
FROM OPENXML(@iX, ''/*/*'', 2) WITH (
R00000010 VARCHAR(7)
,R00000020 VARCHAR(1)
,R00000030 INT
,R00000040 INT
,R00000050 INT
,R00000060 INT
)
EXEC sp_xml_removedocument @iX
INSERT INTO ##XMLImport
SELECT R00000010
,R00000020
,R00000030
,R00000040
,R00000050
,R00000060
FROM #XMLResults'
--PRINT @sql
EXEC sp_executesql @sql
-- process next file
FETCH NEXT
FROM filecurs
INTO @file
END
exitprocessing:
-- clean up
CLOSE filecurs
DEALLOCATE filecurs
SELECT *
FROM ##XMLImport
Two options come to mind, both are features of the can't-say-enough-good-things-about-this-free-tool BIDS Helper.
Create Fixed Width Columns
The first is the Create Fixed Width Columns feature. It's as simple as Create a Flat File Manager pointing at the correct file and define 1 column.
Once that's done, then you right click back on the Connection Manager and use the context sensitive feature of "Create Fixed Width Columns..."
In the next window, you paste the tab delimited list (Excel) into the editor
Oh yeah, it's that's simple. If you need to do some tweaking for column types and such, then you go in and change it as normal with edit.
Biml
I like the Business Intelligence Markup Language for my SSIS development. There's lots of benefit to it but at a most basic level, use it for things like godawful flat files, especially when they provide no header rows (I'm looking at you, you mainframes).
It has often been my experience when dealing with legacy systems, that they have their file definitions in COBOL copybooks. Those devs will send me Excel files with the column layout like
DATABASE FIELD NAME START LOC LENGTH
SEND.DT 1 STRING(08)
SEND.TIME 9 STRING(08)
DT 17 STRING(08)
TERM 25 STRING(04)
%ZONE 29 STRING(01)
It ain't fancy, but Excel formulas made short work of it. So I'd create 4 new columns based on the above.
Sanitized Name (removed invalid characters for column names)
=SUBSTITUTE(SUBSTITUTE(B3, ".", "_"), "%", "PCT_")
Length (extracted the numbers, note this still has a leading 0 but it doesn't hurt)
=MID(E3,LEN("STRING(")+1,LEN(E3)-LEN("STRING(")-1)
XML (this builds out the actual XML for a flat file definition)
=CONCATENATE("<Column Name=""",F3,""" Length=""",G3,""" DataType=""AnsiString"" ColumnType=""FixedWidth"" CodePage=""1252"" />")
DDL (Might as well build the target table while I'm at it)
=CONCATENATE(", ", F3, " varchar(", G3, ")")
The first column of the DDL gets the lead comma dropped and then wrapped with CREATE TABLE dbo.FOO()
Ultimately, that XML results in something that gets slapped into a FlatFileFormat tag and then it's a simple matter of creating the Connections collection and finally the package itself. Looks a lot but it's actually pretty simple, once you have a pattern.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<FileFormats>
<FlatFileFormat
Name="FFF Pickup"
CodePage="1252"
RowDelimiter="CRLF"
IsUnicode="false"
FlatFileType="RaggedRight">
<Columns>
<Column Name="SEND_DT" Length="08" DataType="AnsiString" ColumnType="FixedWidth" CodePage="1252" />
<Column Name="SEND_TIME" Length="08" DataType="AnsiString" ColumnType="FixedWidth" CodePage="1252" />
<Column Name="DT" Length="08" DataType="AnsiString" ColumnType="FixedWidth" CodePage="1252" />
<Column Name="TERM" Length="04" DataType="AnsiString" ColumnType="FixedWidth" CodePage="1252" />
<!--
ad nauseum
-->
<Column Name="RPRTD_PU_PCS" Length="5" DataType="AnsiString" ColumnType="FixedWidth" CodePage="1252" />
</Columns>
</FlatFileFormat>
</FileFormats>
<Connections>
<FlatFileConnection
Name="FF Pickup"
FileFormat="FFF Pickup"
FilePath="C:\ssisdata\Operations\Input\Pickup Report Pickups.txt"
CreateInProject="false"
/>
</Connections>
<Packages>
<Package Name="PickupLoad" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
<Tasks>
<Dataflow Name="DFT Load Pickups" >
<Transformations>
<FlatFileSource
Name="OLE_SRC Pickup"
ConnectionName="FF Pickup"
RetainNulls="true">
</FlatFileSource>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
Oh and a similar question from SO with a disturbingly similar answer. Wish I had looked at Skinner's comments before I rewrote this answer.
Best Answer
Something has happened such that the source column doesn't exist any more and the mapping is broken. In the bottom drop down, "spaltzenzuodnungsoption..." there should be 3? options. You want the one that says delete. This will remove all the mappings so you can start over.