September 8, 2015 at 2:34 pm
Good article, thanks.
November 24, 2015 at 10:29 am
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?
November 25, 2015 at 11:08 am
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
November 25, 2015 at 11:24 am
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------------------------------
...
November 26, 2015 at 5:22 am
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
November 30, 2015 at 7:22 am
I'm not sure how you determine this. We're using the VS 2010 shell distributed with SQL Server 2012.
November 30, 2015 at 10:41 am
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
November 30, 2015 at 11:44 am
It would appear so. The last run populated PackageXML for the 60 rows that were generated.
November 30, 2015 at 10:51 pm
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
December 3, 2015 at 2:05 pm
Where you able to run this in SSIS 2008?
If so would you share how you did it?
December 3, 2015 at 8:33 pm
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
December 4, 2015 at 7:30 am
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.
December 4, 2015 at 11:38 am
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
December 4, 2015 at 11:47 am
Sorry. I couldn't release the package outside of our organization.
December 6, 2015 at 3:54 am
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 43 total)
You must be logged in to reply to this topic. Login to reply