private void WriteCacheFile(DataTable collectedData, CollectionItemConfig itm)
        {
            String ts          = DateTime.Now.ToString("yyyyMMddHHmmss");
            String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index;

            String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache");

            if (verbose)
            {
                logger.logMessage("Saving to cache file " + destFile);
            }


            //
            // Save data to a binary cache file
            //
            if (File.Exists(destFile))
            {
                File.Delete(destFile);
            }



            System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

            using (FileStream fs = new FileStream(destFile, FileMode.CreateNew))
            {
                fm.Serialize(fs, collectedData);
                fs.Close();
            }
            if (verbose)
            {
                logger.logMessage("File saved successfully");
            }

            collectedData.Rows.Clear();
        }
        /*
         * Creates the target table from the
         * output definition of the query
         */
        protected override String createTargetTable(
            CollectorConfig cfg,
            CollectionItemConfig itm
            )
        {
            String TableName = itm.OutputTable;
            TSQLCollectionItemConfig itemCfg = (TSQLCollectionItemConfig)itm;

            String CollectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index;

            //
            // checks if the table exists and which schema it belongs
            //
            String checkedTableName = checkTable(cfg, itm);

            if (checkedTableName != null)
            {
                return(checkedTableName);
            }

            String statement = @"

	        IF NOT EXISTS (
		        SELECT *
		        FROM sys.servers
		        WHERE NAME = 'LOOPBACK'
	        )
	        BEGIN

		        DECLARE @srv nvarchar(4000);
		        SET @srv = @@SERVERNAME; -- gather this server name
                 
		        -- Create the linked server
		        EXEC master.dbo.sp_addlinkedserver
		            @server     = N'LOOPBACK',
		            @srvproduct = N'SQLServ', -- it's not a typo: it can't be 'SQLServer'
		            @provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
		            @datasrc    = @srv;
                 
		        -- Set the authentication to 'current security context'
		        EXEC master.dbo.sp_addlinkedsrvlogin
		            @rmtsrvname  = N'LOOPBACK',
		            @useself     = N'True',
		            @locallogin  = NULL,
		            @rmtuser     = NULL,
		            @rmtpassword = NULL;

	        END
             
	        USE tempdb;
	        GO

	        IF OBJECT_ID('{0}') IS NOT NULL
		        DROP PROCEDURE [{0}]
	        GO
             
	        CREATE PROCEDURE [{0}]
	        AS
	        BEGIN
	            SET NOCOUNT ON;
             
	            {1}
	        END
	        GO

	        IF SCHEMA_ID('custom_snapshots') IS NULL
		        EXEC('CREATE SCHEMA [custom_snapshots]')

	        IF OBJECT_ID('custom_snapshots.{2}') IS NOT NULL
		        DROP TABLE [custom_snapshots].[{2}]
	        GO

	        SELECT TOP 0 *, 
		        CAST(NULL AS sysname) AS _database_name, 
		        CAST(NULL AS datetimeoffset(7)) AS _collection_time,
		        CAST(NULL AS int) AS _snapshot_id
	        INTO tempdb.[custom_snapshots].[{2}]
	        FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF; EXEC tempdb.dbo.[{0}]');
             
	        DROP PROCEDURE [{0}];
	        GO
            
	        IF EXISTS(
		        SELECT 1 
		        FROM sys.columns 
		        WHERE name = 'database_name'
		        AND object_id = OBJECT_ID('[custom_snapshots].[{2}]')
	        )
	        BEGIN
		        EXEC sp_rename '[custom_snapshots].[{2}].[database_name]', '__database_name', 'COLUMN';
	        END
	        EXEC sp_rename '[custom_snapshots].[{2}].[_database_name]', 'database_name', 'COLUMN';


	        IF EXISTS(
		        SELECT 1 
		        FROM sys.columns 
		        WHERE name = 'collection_time'
		        AND object_id = OBJECT_ID('[custom_snapshots].[{2}]')
	        )
	        BEGIN
		        EXEC sp_rename '[custom_snapshots].[{2}].[collection_time]', '__collection_time', 'COLUMN';
	        END
	        EXEC sp_rename '[custom_snapshots].[{2}].[_collection_time]', 'collection_time', 'COLUMN';

            
	        IF EXISTS(
		        SELECT 1 
		        FROM sys.columns 
		        WHERE name = 'snapshot_id'
		        AND object_id = OBJECT_ID('[custom_snapshots].[{2}]')
	        )
	        BEGIN
		        EXEC sp_rename '[custom_snapshots].[{2}].[snapshot_id]', '__snapshot_id', 'COLUMN';
	        END
	        EXEC sp_rename '[custom_snapshots].[{2}].[_snapshot_id]', 'snapshot_id', 'COLUMN';

	        "    ;

            statement = String.Format(statement, CollectorId, itemCfg.Query, itm.OutputTable);

            CollectorUtils.InvokeSqlBatch(SourceServerInstance, "tempdb", statement);

            String scriptText = CollectorUtils.ScriptTable(SourceServerInstance, "tempdb", TableName);

            CollectorUtils.InvokeSqlCmd(cfg.MDWInstance, cfg.MDWDatabase, scriptText);

            return("[custom_snapshots].[" + TableName + "]");
        }
        public void CollectData(
            String SourceServerInstance,
            Guid CollectionSetUid,
            int ItemId
            )
        {
            CollectorLogger logger = new CollectorLogger(SourceServerInstance, CollectionSetUid, ItemId);

            DataTable collectedData = null;

            if (verbose)
            {
                logger.logMessage("--------------------------------");
            }
            if (verbose)
            {
                logger.logMessage("      ExtendedTSQLCollector      ");
            }
            if (verbose)
            {
                logger.logMessage("--------------------------------");
            }
            if (verbose)
            {
                logger.logMessage("Copyright© sqlconsulting.it 2014");
            }
            if (verbose)
            {
                logger.logMessage("-");
            }

            if (verbose)
            {
                logger.logMessage("Loading configuration");
            }
            //
            // Load Configuration
            //
            TSQLCollectorConfig cfg = new TSQLCollectorConfig();

            cfg.readFromDatabase(SourceServerInstance, CollectionSetUid, ItemId);


            if (verbose)
            {
                logger.logMessage("Entering collection items loop");
            }
            foreach (CollectionItemConfig item in cfg.collectionItems)
            {
                TSQLCollectionItemConfig itm = (TSQLCollectionItemConfig)item;
                if (verbose)
                {
                    logger.logMessage("Processing item n. " + itm.Index);
                }
                if (verbose)
                {
                    logger.logMessage("Processing query " + itm.Query);
                }

                collectedData = null;

                String ts          = DateTime.Now.ToString("yyyyMMddHHmmss");
                String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index;

                String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache");

                //
                // Iterate through the enabled databases
                //
                if (verbose)
                {
                    logger.logMessage("Entering databases loop");
                }
                foreach (String currentDatabase in cfg.Databases)
                {
                    if (verbose)
                    {
                        logger.logMessage("Processing database " + currentDatabase);
                    }
                    //
                    // Execute the query in the collection item
                    //

                    DataTable dt = CollectorUtils.GetDataTable(SourceServerInstance, currentDatabase, itm.Query);

                    //
                    // Add computed columns
                    //
                    if (dt.Columns.Contains("database_name"))
                    {
                        dt.Columns["database_name"].ColumnName = "__database_name";
                    }
                    DataColumn cl_db = new DataColumn("database_name", typeof(String));
                    cl_db.DefaultValue = currentDatabase;
                    dt.Columns.Add(cl_db);


                    if (dt.Columns.Contains("collection_time"))
                    {
                        dt.Columns["collection_time"].ColumnName = "__collection_time";
                    }
                    DataColumn cl_dt = new DataColumn("collection_time", typeof(DateTime));
                    cl_dt.DefaultValue = DateTime.Now;
                    dt.Columns.Add(cl_dt);


                    //
                    // Merge collected data in a single DataTable
                    //
                    if (collectedData != null)
                    {
                        collectedData.Merge(dt);
                    }
                    else
                    {
                        collectedData = dt;
                        collectedData.DataSet.RemotingFormat = System.Data.SerializationFormat.Binary;
                        collectedData.RemotingFormat         = System.Data.SerializationFormat.Binary;
                    }
                }

                if (verbose)
                {
                    logger.logMessage("Saving to cache file " + destFile);
                }
                //
                // Save data to a binary cache file
                //
                if (File.Exists(destFile))
                {
                    File.Delete(destFile);
                }



                System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

                using (FileStream fs = new FileStream(destFile, FileMode.CreateNew))
                {
                    fm.Serialize(fs, collectedData);
                    fs.Close();
                }
                if (verbose)
                {
                    logger.logMessage("File saved successfully");
                }
            }


            logger.cleanupLogFiles(cfg.DaysUntilExpiration);
        }