Post reply

Powershell script to find the text between two strings

  • April 11, 2017 at 10:40 am

    #401932

    Hi ,Iam trying to find a script which can loop through the dtsx files and get the connection managers.

    Get-ChildItem -Path C:\temp\*.dtsx -recurse | Select-String -Pattern "XXX" | Substring (10,20)

    I wanted to add Substring to the above code and get the server and databases.

    Appreciate your help..!!

    thanks,
    demin

  • April 12, 2017 at 10:40 am

    #1937898

    demin99 - Tuesday, April 11, 2017 10:40 AM

    Hi ,Iam trying to find a script which can loop through the dtsx files and get the connection managers.

    Get-ChildItem -Path C:\temp\*.dtsx -recurse | Select-String -Pattern "XXX" | Substring (10,20)

    I wanted to add Substring to the above code and get the server and databases.

    Appreciate your help..!!

    thanks,
    demin

    What are you getting back from Select-String -Pattern "XXX"?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • April 14, 2017 at 8:38 am

    #1938181

    I get all the dtsx which has XXX values in it

  • April 14, 2017 at 3:41 pm

    #1938235

    If you have some basic SQL and XML knowledge you can use SQLXML to query the packages for the ConnectionManager. ConnectionManger Info lives in DTS:ConnectionManger. 
    For example, if your SSIS packages were deployed to the package store in msdb you could get what you are looking for (or close to it) with this SQL query:

    ;WITH XMLNAMESPACES
    ('www.microsoft.com/SqlServer/Dts' AS pNS1, 'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces
    SELECT
    package = c.name,
    ConnectionManager =
    SSIS_XML.value('./pNS1Tongueroperty[@pNS1:Name="ObjectName"][1]',
    'varchar(100)'),
    ConnectionString =
      SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1Tongueroperty[@pNS1:Name="ConnectionString"][1]', 'varchar(8000)')
    FROM
    (
    SELECT id, PackageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM msdb.dbo.sysssispackages
    ) AS PackageXML
    CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') AS SSIS_XML(SSIS_XML)
    JOIN msdb.dbo.sysssispackages AS c
    ON PackageXML.id = c.id;

    If you deploy to an SSIS catalog (SSISDB) then the FROM and namespace info will need to be updated accordingly. If you run the packages as .dtsx files then you would need to bring the files in using OPENROWSET or something similar.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply