none
Extract the Columns and datatype from Flat File Connection Manager from DTSX File RRS feed

  • Question

  • Hello Experts,

    I needs some help to Extract the Columns and datatype from Flat File Connection Manager from SSIS package and I need to compare the same the with the database columns. any idea how to achieve this.



    Regards chintu


    • Edited by Chintu1984 Thursday, November 14, 2019 6:11 PM
    Thursday, November 14, 2019 6:11 PM

All replies

  • Hi Chintu,

    You can easily query SSIS *.dtsx files using T-SQL language, because they are XML files in reality.

    Just search this forum for how to do that. I answered few posts on the subject in the past, and showed how to query SSIS packages.

    Thursday, November 14, 2019 6:35 PM
  • Hi chintu,

    The following link will be helpful:

    How to retrieve information about SSIS packages stored in MSDB Database

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 15, 2019 1:52 AM
  • Hi Yitzhak/Mona, the package has 100 Flat File Connections.Is there any way that i can get the list of Columns from each connection manager. I have tried the same with BIML(noluck) and XQuery(its taking so long time like 3 hrs ormore) here is the Query to Extract the File Connection metadata from the .dtsx file deployed in the SSIS Catalog.

    select * from (
    SELECT   name,--Props.Prop.query('.') 
    Props1.Prop1.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                                string(./@p1:ObjectName)','nvarchar(max)') connectionName ,
          Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                                string(./@p1:ObjectName)','nvarchar(max)')                    as ColumnName,
    Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                                string(./@p1:DataType)','nvarchar(max)') DataType    
     
    --,        Props.Prop.value('.', 'nvarchar(max)')                                        as PropValue
    FROM    (
            SELECT    name,
                        CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) pkgXML
              FROM      [msdb].[dbo].[sysssispackages] 
                   
            ) t
    CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                            /DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn') Props(Prop) 
    CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                            /DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') Props1(Prop1)                                                                                                                                                                                                                                                                            
                                             ) as a order by connectionName 

    so the question i have is

    1. how to retrieve the columns and its datatype for all 100 connection managers

    2. is there any way that i can add the columns dynamically to flat file connection manager with out opening the SSIS package.

    Can someone please help me on this.

    Best Regards Chintu


    Regards chintu



    • Edited by Chintu1984 Thursday, December 5, 2019 6:32 AM
    Thursday, December 5, 2019 6:29 AM
  • Hi chintu,

    Please refer to Extracting SSIS Package information using T-SQL.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 6, 2019 9:52 AM