Home Forums SQL Server 2008 T-SQL (SS2K8) extracting source/dest column details from SSIS package files RE: extracting source/dest column details from SSIS package files

  • 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]