![\"\"](\"https:\/\/myworldofit.net\/wp-content\/uploads\/2015\/01\/sqlserver.jpg\")
Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows.<\/p>\n
Although I won’t delve into too much detail in this post I’ll take a look at what is possible in particular-<\/p>\n
In the majority of businesses (and in all schools) there will be some kind of central database which stores information on employees\/staff and in schools students. Where I work this is Capita SIMS, the database its self comprises of a great number of tables however its possible to avoid rooting through the tables to find the data you want by using the commandreporter.exe application.<\/p>\n
The basic workflow for the generation of new staff or student accounts is-<\/p>\n
This SSIS package has been running very well for the past year (which included the generation of ~115 accounts at the same time for the summer Year 7 intake) with only a modification to allow for students with names longer than 100 characters long (yes that one took me by surprise!).<\/p>\n
\n Its an unfortunate fact that its people and not systems that are the biggest security risk to any company\/business (nice read here about this<\/a>); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.<\/p>\n As a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.<\/p>\n Here comes SSIS…<\/p>\n Originally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!<\/p>\n The process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.<\/p>\n \n Where I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\n For this SSIS package we have-<\/p>\n \n Where I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\n The sequence for the SSIS package is<\/p>\n This is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n \n I hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n As you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n \n Hopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":" Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t
\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nAlerting the service desk when a student or staff member leaves the school<\/h4>\n
\n
\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t
\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\n
\n
\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t
\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\n
\n
\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t
\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\n
\n
\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t
\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\n
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nAlerting the service desk when a student or staff member leaves the school<\/h4>\nIts an unfortunate fact that its people and not systems that are the biggest security risk to any company\/business (nice read here about this<\/a>); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.<\/p>\nAs a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.<\/p>\nHere comes SSIS…<\/p>\n\nFor this package every 30 minutes a report is run (as a CSV) which exports the PersonID, Forename and Surname of any staff member who has a leaving date set, this information is then imported into a SQL database (using a Slowly Changing Dimension transformation to detect any already imported duplicates).<\/li>\nUsing the data stored in the SQL database a CSV is generated and emailed to the service desk with a link to the procedure to carry out whenever a staff member leaves.<\/li>\nA field is then set in the SQL database that lists the person as already exported thus preventing any further automated notifications about this account being generated.<\/li>\n<\/ol>\nOriginally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!<\/p>\nThe process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-new-user\/1.PNG\"\n\t\t\t\ttitle=\"Quite){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nAlerting the service desk when a student or staff member leaves the school<\/h4>\nIts an unfortunate fact that its people and not systems that are the biggest security risk to any company\/business (nice read here about this<\/a>); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.<\/p>\nAs a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.<\/p>\nHere comes SSIS…<\/p>\n\nFor this package every 30 minutes a report is run (as a CSV) which exports the PersonID, Forename and Surname of any staff member who has a leaving date set, this information is then imported into a SQL database (using a Slowly Changing Dimension transformation to detect any already imported duplicates).<\/li>\nUsing the data stored in the SQL database a CSV is generated and emailed to the service desk with a link to the procedure to carry out whenever a staff member leaves.<\/li>\nA field is then set in the SQL database that lists the person as already exported thus preventing any further automated notifications about this account being generated.<\/li>\n<\/ol>\nOriginally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!<\/p>\nThe process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-new-user\/3.PNG\"\n\t\t\t\ttitle=\"This){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nAlerting the service desk when a student or staff member leaves the school<\/h4>\nIts an unfortunate fact that its people and not systems that are the biggest security risk to any company\/business (nice read here about this<\/a>); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.<\/p>\nAs a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.<\/p>\nHere comes SSIS…<\/p>\n\nFor this package every 30 minutes a report is run (as a CSV) which exports the PersonID, Forename and Surname of any staff member who has a leaving date set, this information is then imported into a SQL database (using a Slowly Changing Dimension transformation to detect any already imported duplicates).<\/li>\nUsing the data stored in the SQL database a CSV is generated and emailed to the service desk with a link to the procedure to carry out whenever a staff member leaves.<\/li>\nA field is then set in the SQL database that lists the person as already exported thus preventing any further automated notifications about this account being generated.<\/li>\n<\/ol>\nOriginally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!<\/p>\nThe process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-new-user\/4.PNG\"\n\t\t\t\ttitle=\"This){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nAlerting the service desk when a student or staff member leaves the school<\/h4>\nIts an unfortunate fact that its people and not systems that are the biggest security risk to any company\/business (nice read here about this<\/a>); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.<\/p>\nAs a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.<\/p>\nHere comes SSIS…<\/p>\n\nFor this package every 30 minutes a report is run (as a CSV) which exports the PersonID, Forename and Surname of any staff member who has a leaving date set, this information is then imported into a SQL database (using a Slowly Changing Dimension transformation to detect any already imported duplicates).<\/li>\nUsing the data stored in the SQL database a CSV is generated and emailed to the service desk with a link to the procedure to carry out whenever a staff member leaves.<\/li>\nA field is then set in the SQL database that lists the person as already exported thus preventing any further automated notifications about this account being generated.<\/li>\n<\/ol>\nOriginally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!<\/p>\nThe process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-new-user\/5.PNG\"\n\t\t\t\ttitle=\"The){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-leaver\/1.PNG\"\n\t\t\t\ttitle=\"One){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-leaver\/2.PNG\"\n\t\t\t\ttitle=\"Here){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nImporting new staff members into the asset management system<\/h4>\nWhere I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).<\/p>\nFor this SSIS package we have-<\/p>\n\nEvery 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title)\u00a0 using calculated fields the additional database fields that are used by the asset management system are generated.<\/li>\nNext using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.<\/li>\n<\/ol>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-leaver\/3.PNG\"\n\t\t\t\ttitle=\"Here){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-wasp\/1.PNG\"\n\t\t\t\ttitle=\"A){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-wasp\/2.PNG\"\n\t\t\t\ttitle=\"Here){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nEmailing a report on printing activity to the finance department<\/h4>\nWhere I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).<\/p>\nThe sequence for the SSIS package is<\/p>\n\nOn the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.<\/li>\nThis data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.<\/li>\nAn email is then generated with the printing report attached and sent to the finance department to action.<\/li>\n<\/ol>\nThis is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-wasp\/3.PNG\"\n\t\t\t\ttitle=\"The){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-email-finance\/1.PNG\"\n\t\t\t\ttitle=\"Very){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-email-finance\/2.PNG\"\n\t\t\t\ttitle=\"Here){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-email-finance\/3.PNG\"\n\t\t\t\ttitle=\"Instead){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nGenerating email distribution lists automatically<\/h4>\nI hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of\u00a0 \ud83d\ude42<\/p>\n\nUsing commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.<\/li>\nThe current information in locally held SQL tables is then deleted and\u00a0the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.<\/li>\nThis information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.<\/li>\n<\/ol>\nAs you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.<\/p>\n\n\n\n\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-email-finance\/4.PNG\"\n\t\t\t\ttitle=\"The){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-update-dgs\/1.PNG\"\n\t\t\t\ttitle=\"Quite){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-update-dgs\/2.PNG\"\n\t\t\t\ttitle=\"The){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-update-dgs\/3.PNG\"\n\t\t\t\ttitle=\"The){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t\t\n\t\t\t\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-update-dgs\/4.PNG\"\n\t\t\t\ttitle=\"Staff){kind=link}
![\n\t\t\t\t\t\t\t\t\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\n\t\n\t\n\n\t\n\t\t<\/div>\n\t<\/div>\n<\/p>\nThat’s all folks…<\/h4>\nHopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.<\/p>\n","protected":false},"excerpt":{"rendered":"Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows. Although I won’t delve into too much detail in this… Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[11,20],"tags":[126,256,253,254],"series":[],"class_list":["post-8445","post","type-post","status-publish","format-standard","hentry","category-servers","category-software","tag-active-directory","tag-automation","tag-sql-server","tag-ssis"],"_links":{"self":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8445"}],"version-history":[{"count":12,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions"}],"predecessor-version":[{"id":8516,"href":"https:\/\/myworldofit.net\/index.php?rest_route=\/wp\/v2\/posts\/8445\/revisions\/8516"}],"wp:attachment":[{"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8445"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/myworldofit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=8445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/myworldofit.net\/wp-content\/gallery\/ssis-update-dgs\/5.PNG\"\n\t\t\t\ttitle=\"Not){kind=link}