SSIS Package documentor

  • Good article, thanks.

  • Testing now. Admittedly, I'm just executing this to see how the results look after one pass. How are subsequent executions managed? Do you have to DELETE rows from the tables before running again?

  • bteague (11/24/2015)


    Testing now. Admittedly, I'm just executing this to see how the results look after one pass. How are subsequent executions managed? Do you have to DELETE rows from the tables before running again?

    Deletion not required..do let me know how much useful it was. We have used the code to verify many things..recently to check lookup query

    --Divya

  • Thanks. I received messages indicating issues with the variable and constraint sections (below). Also, it didn't appear to find any of the scripts in our packages.

    ...

    ------------------------Get all variable details-------------------------------------

    Msg 8152, Level 16, State 10, Procedure GetPackageDetails, Line 447

    String or binary data would be truncated.

    The statement has been terminated.

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ----------------Get precedence constraint details------------------------------

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint1' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint1' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

    ----------------Get precedence constraint details------------------------------

    ...

  • bteague (11/25/2015)


    Thanks. I received messages indicating issues with the variable and constraint sections (below). Also, it didn't appear to find any of the scripts in our packages.

    ...

    ------------------------Get all variable details-------------------------------------

    Msg 8152, Level 16, State 10, Procedure GetPackageDetails, Line 447

    String or binary data would be truncated.

    The statement has been terminated.

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ----------------Get precedence constraint details------------------------------

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint1' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint1' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

    ----------------Get precedence constraint details------------------------------

    ...

    which version of ssis package you are having? the code works for ssis 2012 packages.

    --Divya

  • I'm not sure how you determine this. We're using the VS 2010 shell distributed with SQL Server 2012.

  • bteague (11/30/2015)


    I'm not sure how you determine this. We're using the VS 2010 shell distributed with SQL Server 2012.

    This should work. Can you please check if the package xml is being fetched in pkgstats table (the first table in which we load package xml using xpcmdshell) ?

    --Divya

  • It would appear so. The last run populated PackageXML for the 60 rows that were generated.

  • bteague (11/30/2015)


    It would appear so. The last run populated PackageXML for the 60 rows that were generated.

    Can you send me one package?

    --Divya

  • Where you able to run this in SSIS 2008?

    If so would you share how you did it?

  • pchelptx (12/3/2015)


    Where you able to run this in SSIS 2008?

    If so would you share how you did it?

    The XML code behind of SSIS pkg slightly differs in 2008. I have just had a glance on it. To be honest I never run the script on 2008. Give a try once

    --Divya

  • I managed to have the script load the DB with data in the "pkgStats" table. But when running the rest of the script there is no data in the rest of the tables.

    [SSIS_Extra].[dbo].[pkgStats]

    This is the output when running the script:

    ---------------Get all connections-----------------------------------

    ---------------Get all connections-----------------------------------

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ----------------Get precedence constraint details------------------------------

    Msg 537, Level 16, State 5, Procedure GetPackageDetails, Line 614

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    The statement has been terminated.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

  • pchelptx (12/4/2015)


    I managed to have the script load the DB with data in the "pkgStats" table. But when running the rest of the script there is no data in the rest of the tables.

    [SSIS_Extra].[dbo].[pkgStats]

    This is the output when running the script:

    ---------------Get all connections-----------------------------------

    ---------------Get all connections-----------------------------------

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all variable details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ------------------------Get all parameter details-------------------------------------

    ----------------Get precedence constraint details------------------------------

    Msg 537, Level 16, State 5, Procedure GetPackageDetails, Line 614

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    The statement has been terminated.

    Warning! The maximum key length is 900 bytes. The index 'NCIX_TblPrecedenceConstraint' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

    Ok can you please send me a sample package ?

    --Divya

  • Sorry. I couldn't release the package outside of our organization.

  • bteague (12/4/2015)


    Sorry. I couldn't release the package outside of our organization.

    The error occurs when you run the script for packages not of 2012 version. Please can you migrate your packages (temporary) using migration utility to 2012 and then run the script.

    --Divya

Viewing 15 posts - 16 through 30 (of 41 total)

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