June 30, 2015 at 1:05 am
Hi everyone
We have several hundred very simple ETL SSIS 2K8 package files (*.dtsx).
I'd like to be able to interrogate them to determine source and destination fields.
There's no great need to map source to dest or to extract data types.
So far, the most promising candidate is to load them using OPENROWSET into an XML field in a SS table.
No problem there, but querying using OPENXML has me stumped.
If anyone is able to assist with this solution or perhaps suggest alternatives, it would be greatly appreciated.
The package files will change a couple of times per year, so the process needs to be repeatable with minimal manual intervention.
thanks.
July 1, 2015 at 5:14 pm
OK, I've got something that I can work with now.
For anyone facing similar situations, here is a summary:
The main problem was the schema references within the XML.
Adding it to the call to sp_xml_preparedocument allowed me to navigate the xml structure without error.
[font="Courier New"]EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<root xmlns:DTS="www.microsoft.com/SqlServer/Dts"/>'
[/font]
Then, a bit of reading and a lot of trial & error led me to the below queries to return the destination column structures and the source query.
[font="Courier New"]SELECT *
FROM OPENXML(@hDoc, '/DTS:Executable/DTS:Executable/DTS:ObjectData/pipeline/components/component/inputs/input/externalMetadataColumns/externalMetadataColumn')
WITH
(
DestinationFieldName [VARCHAR](2000) '@name',
DataType [VARCHAR](50) '@dataType',
Length INT '@length',
PRECISION INT '@precision',
Scale INT '@scale
)
SELECT *
FROM OPENXML(@hDoc, '/DTS:Executable/DTS:Executable/DTS:ObjectData/pipeline/components/component/properties/property')
WITH
(
Identifier [VARCHAR](2000) '@name',
SourceQuery [VARCHAR](MAX) '.'
)
WHERE
Identifier = 'SqlCommand'
AND COALESCE(sourcequery,'') <> ''
[/font]
July 2, 2015 at 6:39 am
Can you post your OPENROWSET query so I can see a working example? You can substitute things like 'D:\Folder\Filename.dtsx' for the actual filename.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 4:16 pm
sgmunson (7/2/2015)
Can you post your OPENROWSET query so I can see a working example? You can substitute things like 'D:\Folder\Filename.dtsx' for the actual filename.
I use the OPENROWSET to insert into this table (using dynamic sql iterations and some very ugly xp_cmdshell directory listing)
[font="Courier New"]CREATE TABLE XMLwithOpenXML (
Id INT IDENTITY PRIMARY KEY
,XMLData XML
,SourceFile NVARCHAR(500)
,LoadedDateTime DATETIME
)[/font]
with something like this:
[font="Courier New"]INSERT INTO XMLwithOpenXML(XMLData, SourceFile, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, 'c:\FilePath\PackageName.dtsx', GETDATE()
FROM OPENROWSET(BULK 'c:\FilePath\PackageName.dtsx', SINGLE_BLOB) AS x;
[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply