Intermediate commit on each ISPAC... about sqlservermetadata HOT 8 CLOSED

At the moment the commit for the repository is called once the analysis is complete.
It uses SqlBulkCopy to save the data, utilising the WriteToServer method. There is another overload on that method, that allows specifying a DataRowState. See the Repository.cs around line 261.

In theory, the commit could be modified to use the alternate WriteToServer(DataTable, DataRowState) and include only DataRowState of Added.
Then after each commit of a table, the rows would need updated to a DataRowState of Unchanged, via a call to AcceptChanges() against the table.
WriteToServer does not update the records in the associated DataTable's state.
Then the repository commit could be called many times, for just added records.
Some work to handle DataRowState.Modified will be required, as comments can be updated. But the vast majority of the execution is adding new records.

If this was done, then the various loops that are scanning items, could include a call to the repository commit.

from sqlservermetadata.

Hello...
I did a modifications see blow and it works for the context of ispac files

in program.cs :
public static void Commit_Intermediate(Repository repository)
{
Console.Write("Committing ISPAC analysis information to database...");
repository.Commit_Intermediate();
Console.WriteLine("Done for ISPAC File.");
}

in Repository.cs :
public void Commit_Intermediate()
{
// write each data table to the database
SqlBulkCopy bulkCopy = new SqlBulkCopy(repositoryConnection);

		bulkCopy.DestinationTableName = "RunScan";
		bulkCopy.WriteToServer(runScanTable);
		runScanTable.Clear (); //in the final & next intermadiate commit mean nothing to add
		
		bulkCopy.DestinationTableName = "Objects";
		bulkCopy.WriteToServer(objectTable);
		objectTable.Clear ();
		
		bulkCopy.DestinationTableName = "ObjectDependencies";
		bulkCopy.WriteToServer(objectDependenciesTable);
		objectDependenciesTable.Clear ();

		bulkCopy.DestinationTableName = "ObjectAttributes";
		bulkCopy.WriteToServer(objectAttributesTable);
		objectAttributesTable.Clear ();

		bulkCopy.DestinationTableName = "ObjectTypes";
		bulkCopy.WriteToServer(objectTypesTable);
		objectTypesTable.Clear ();


		bulkCopy.Close();
	}

and in SsisEnumerator.cs ONLY for ISPAC FILES :

	private void EnumerateProjects(string rootFolder, string pattern, bool recurseSubFolders, string locationName)
    {
        string[] filesToInspect;
        Console.Write("Enumerating projects...");
        filesToInspect = System.IO.Directory.GetFiles(rootFolder, pattern, (recurseSubFolders) ? System.IO.SearchOption.AllDirectories : System.IO.SearchOption.TopDirectoryOnly);
        Console.WriteLine("done.");

        foreach (string projectFileName in filesToInspect)
        {
           EnumerateIntegrationServicePack(projectFileName, locationName);
			Program.Commit_Intermediate(repository);
        }
    }

from sqlservermetadata.

Your code will work.
It's just not quite the way I have implemented it. (I hadn't added a comment that I'd made changes, as I haven't finished regression testing everything.)

If you grab the latest commit you can see they way that I implemented it, which is slightly different, as I changed the main commit routine to handle both bulk insert and updates to change records.
I've also got significantly more commit's happening, on a "per folder basis", including where you have yours above.

from sqlservermetadata.

hello,

Ok i can see your change :-) glade to see you already work on it :-)

As is said can be very interesting in a big SSIS referential to avoid 2 hours treatment to go in the trash...

Of course the next step after this one would be to allow a new analyse to be done with the same run id avoiding to re analyse ISPAC already loaded.

And the cherry on the cake would be to allow the storage of the ISPAC filename + timestamp update date to allow a run upon a ispac referential and update only ISPAC file that have been updated without re analyse all the set...
this is the first milestone of the project for me to implement in order to have a daily updated SSIS metadata upon our 1200 dtsx with 100 Ispac files.

Hope you share the same roadmap :-) any way thank you for your work.

from sqlservermetadata.

If you could raise each of those ideas as a separate issues.
That way I can tag them as enhancements, and at least provide ideas on how they could be implemented.

from sqlservermetadata.

NO probs thank you again

from sqlservermetadata.

hello...we try this and found that if the project is in exception the commit happen...this corrupt the run cause the analysis is uncompleted.
This commit should only happen if there is no exception i guess ...

Anyway we are now looping on each isapc...meaning this intermediate commit may be not needed anymore.

Regards

from sqlservermetadata.

I have added Rollback capability, which removes any added records, when an exception happens whilst scanning an SSIS package. Each ssis package is now committed individually.

from sqlservermetadata.