Collecting contents of a package

Question

Collecting contents of a package

Charmer

SSChampion

Points: 14903
More actions
August 13, 2015 at 4:20 am

#320632

Hi Friends,
I am trying to collect the metadata of a package in SSIS. What are the securities are needed to collect a metadata of a package?
since I am testing on my own environment, I could not find any issues. But when the package is ran across different machines, what should I be aware of ?
friends,please post your suggestions.
Any suggestions would be appreciated.
Thanks,
Charmer

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Charmer (8/13/2015)
Hi Friends,
I am trying to collect the metadata of a package in SSIS. What are the securities are needed to collect a metadata of a package?
since I am testing on my own environment, I could not find any issues. But when the package is ran across different machines, what should I be aware of ?
friends,please post your suggestions.
Any suggestions would be appreciated.

By "collect the metadata of a package in SSIS" are you saying you want to extract information about that SSIS (.dtsx) package? If so, take a look at this article:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

SSIS packages are XML files so if you (or the the application that you are running) have read access to the file that should be good enough.

Regarding "when the package is ran across different machines"... You need to give more detail about what you mean by "run across different machines".

"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

Charmer SSChampion Points: 14903 More actions · Answer 2

Yes Alan, I am collecting the metadata information same as in the Jamie Thompson's post advised.

When I say that running in different machines, I meant that running in cross servers through SQL agent job.

So giving folder access is enough?

And for your information, I am using this script to collect the attributes of a package. Got this from the link posted by Aamir

http://www.techbrothersit.com/2013/07/ssis-read-ssis-package-filedtsx-for.html

SELECT ObjectName AS PackageName,

?,

PackageFormatVersion,

CreatorName,

CreationDate,

VersionMajor,

VersionMinore,

CreatorComputerName,

ProtectionLevel,

EnableConfig

FROM (SELECT --Props.Prop.query('.') as PropXml

Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";

string(./@p1:Name)', 'nvarchar(max)') AS PropName,

Props.Prop.value('.', 'nvarchar(max)') AS PropValue

FROM (SELECT Cast(COL AS XML) AS pkgXML

FROM dbo.Test) t

CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";

/DTS:Executable/DTS:Property') Props(Prop)) D

PIVOT (Min(propValue)

FOR PropName IN (ObjectName,

PackageFormatVersion,

CreatorName,

CreationDate,

VersionMajor,

VersionMinore,

CreatorComputerName,

ProtectionLevel,

EnableConfig) ) AS PV

I also need to get the last modified date and last accessed date of a package. Is it possible to get from XML? I have no idea how this script works. So I have no idea what else we can get from this script?

Could you help me understanding this?

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 3

Ok Alan,

Now I am able to understand the script little bit. It gets all the attributes from the XML file.

SELECT

ObjectName AS PackageName,

@PackagePath as PackagePath,

PackageFormatVersion,

CreatorName,

CreationDate,

FileLastModifiedDate,

CreatorComputerName,

FileLastAccessDate

FROM (SELECT --Props.Prop.query('.') as PropXml

Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";

string(./@p1:Name)', 'nvarchar(max)') AS PropName,

Props.Prop.value('.', 'nvarchar(max)') AS PropValue

FROM (SELECT Cast(COL AS XML) AS pkgXML

FROM dbo.Test) t

CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";

/DTS:Executable/DTS:Property') Props(Prop)) D

PIVOT (Min(propValue)

FOR PropName IN (ObjectName,

PackageFormatVersion,

CreatorName,

CreationDate,

FileLastModifiedDate,

CreatorComputerName,

FileLastAccessDate

) ) AS PV

I added few columns FileLastModifiedDate, CreatorComputerName, FileLastAccessDate further to the original query. But I am not getting values to the modified date and last access date. They are empty. I am able to get these information from script task (using FileInfo function, assigning into a variable and passing into my stored proc). What I am trying is , whether we can get it directly from XML or not? So that it would avoid some work around.

Alan, If you are not clear with my questions and answers...please d let me know. I will explain you the complete picture of what I am trying to do.

Thanks,
Charmer

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

Hey Charmer. I will get back to you tomorrow. (Long day)

"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

Charmer SSChampion Points: 14903 More actions · Answer 5

Charmer

SSChampion

Points: 14903

August 17, 2015 at 11:41 pm

#1820615

Sure, Thank you.

Thanks,
Charmer