For those who are not used to this term, here is a definition from Wikipedia:. It can be used by programmers to verify each piece of item content in a document.
They can check if it adheres to the description of the element it is placed in. The corresponding definition can be seen on this page. In this article, we extract information on SSIS packages that are stored in msdb database. For that purpose, Microsoft provided us three tables. You will notice that only two of them are relievant to be used in the following of this article.
Contains one row for each logging entry that is generated by packages or their tasks and containers at run time. Once created, you can either run it when desired or let it run automatically based on the schedules you defined. Anyway, SQL Server Agent will just take the role of an interface to a command line tool called « dtutil ». You will find such a list in the table below. The purpose of this list is not to be exhaustive, but feel free to contact me with suggestions.
Based on these questions and everything we discussed above, we can list the following actions :. Well, while the target is well known, we still need to take the time to answer the following question : « how will we do it? Actually, the answer will be pretty « simple ». While this table will allow us to list SSIS packages action 1 , the packagedata column can be used as input to extract attributes of a SSIS package like the connections action 2. This table alone should be enough, but if you want to get the full path of a given SSIS Package, there is some more work to do.
This table contains a folderid column that refers to the key column with the same name in msdb. Folders can be structured and have a parent folder.
You will find below the TSQL statements to create such a table. The following table maps the columns created in last T-SQL statement with the answers we need to provide. While the PackageRunningId column could be used as the primary key for this table. There are other columns we also keep as they might be useful one day. For instance, the isEncrypted column may allow us to pinpoint the need to extract a certificate or to get a password used to encrypt this package.
We will also take other columns for eventual analysis. Now we know which information is needed, we will take the time to build the query that will fill the SSISPackagesList temporary table step by step.
Notice that the following parameters are used :. The previous query will bring back the minimal set of folders, the first-level folders. Notice that there is a FullPath column that retains the full path of the folder which is exactly what we want to get at the moment.
This statement will use the msdb. Finally, we can get back the list of SSIS packages with their full path in folder hierarchy. First of all, there is a Retain property that you can set to « True » if you want to share this connection accross multiple tasks in a given package. Furthermore, when we edit a new connection, we can tell SSIS to delay the validation of this connection until run time. We will then use the DelayValidation property.
Finally, there are two base properties ObjectName and Description that can be useful. While previous action is a little bit more complex, the following one is pretty straightforwards. It consists more or less in a query against msdb. The first list is easy to get from second. We will first identity the corresponding SSIS package that is used for a given step. To do so, there is the GUID of the package to identify the package. We know that this is not perfect and we might change this by a unique identifier generated at run time.
Finally, as we get data on steps and not only jobs, we will collect the step id, the target server and the entire text of the command that composes the step. We have seen that a SSIS Package is composed of multiple components and properties that can be extracted using different sources and techniques, especially the XML column called packagedata , in msdb. The queries used in this article are grouped together in a SQL file you can download here. We extracted some information. We can take these as input of an analysis process to pinpoint deviations to enterprise standards.
This simplifies change management. END ;. Query 1 — get root - level packages. END as varchar max as FullPath ,. THEN ''. RootFolder ,. FullPath ,. CASE P. ELSE 'Unknown'. END as PackageType ,. FullPath asc , P. PackageUniqifier ,. DelayValidation ,. ObjectName ,. ObjectDescription ,. Retain ,. THEN 'False'. THEN 'True'. FROM msdb. JOIN msdb. ON js. JOIN master. JobId ,. JobName ,.
Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 12 years, 8 months ago. Active 1 year, 8 months ago. Viewed k times. Improve this question. Dylan Corriveau 2, 4 4 gold badges 28 28 silver badges 36 36 bronze badges.
Caveatrob Caveatrob Add a comment. Active Oldest Votes. Then you'll be able to see what packages are in the db. Improve this answer. Casey Williams Casey Williams 3, 2 2 gold badges 22 22 silver badges 15 15 bronze badges. This was the answer, but I had a firewall issue that was blocking me from connecting from my local PC; on the server I could see everything just fine.
Chiramisu I just had to figure this out. Right-click on the package, select to export it to a file system, and then you'll have a. Yeah, that's right MarkMeuer, as I found out later. I guess it wasn't as cut and dry as I thought it was, having not been exposed to DTS packages before. Now I know, thanks. Create a blank solution and add your.
JohnIdol JohnIdol Once you have the. You don't need to create a new solution. Spyder Spyder 3, 2 2 gold badges 25 25 silver badges 14 14 bronze badges. Go to Object Explorer on the left corner. You can see the Stored Package folder in Object Explorer. Naveen Kumar Naveen Kumar 1 1 gold badge 6 6 silver badges 22 22 bronze badges.
0コメント