This project has moved. For the latest updates, please go here.

Loading SQL package '\\Data Collector\PerfCountersCollect'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0,

Jan 29, 2013 at 2:02 AM

Hi has anyone seen this error. Am on sqlserver 2012.

Loading SQL package '\\Data Collector\PerfCountersCollect'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\PerfCountersUpload'... The exception
Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
was raised against query

 

Full output

Enumerating Database metadata for Server=localhost;Database=dbunion;Integrated Security=SSPI;.
Enumerate the Tables and their Dependencies
Could not enumerate the database: Object reference not set to an instance of an object.
Enumerating registered SSIS Data Flow components...Done
Enumerating registered SSIS Connection Managers...Done
Enumerating registered SSIS Tasks...Done
Enumerating File System Integration Services metadata.
Enumerating Integration Services metadata.
Loading SQL package '\\Data Collector\PerfCountersCollect'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\PerfCountersUpload'... The exception
Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
was raised against query
SELECT path, performance_counter_id FROM snapshots.performance_counter_instances
Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspx
Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\QueryActivityCollect'... The exception
Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
was raised against query
SET NOCOUNT ON
-- Get the collection time as UTC time
DECLARE @collection_time datetime
SET @collection_time = GETDATE()
SELECT
CONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id,
-- IDs and Blocking IDs
sess.session_id,
ISNULL (req.request_id, -1) AS request_id,
ISNULL (tasks.exec_context_id, -1) AS exec_context_id,
ISNULL (req.blocking_session_id, 0) AS blocking_session_id,
CONVERT (bit, CASE
                WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1
                ELSE 0
              END) AS is_blocking,
ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id,
tasks.scheduler_id,
DB_NAME(req.database_id) as database_name,
req.[user_id],
-- State information
LEFT (tasks.task_state, 10) AS task_state,
LEFT (req.status, 15) AS request_status,
LEFT (sess.status, 15) AS session_status,
req.executing_managed_code,
-- Session information
sess.login_time,
sess.is_user_process,
LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name],
LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name],
LEFT (ISNULL (sess.login_name, ''), 30) AS login_name,
-- Waits information
LEFT (ISNULL (req.wait_type, ''), 45) AS wait_type,
LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type,
ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms,
LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource,
LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description,
-- Transaction information
req.transaction_id,
ISNULL(req.open_transaction_count, 0) AS open_transaction_count,
COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level,
-- Request stats
req.cpu_time AS request_cpu_time,
req.logical_reads AS request_logical_reads,
req.reads AS request_reads,
req.writes AS request_writes,
req.total_elapsed_time AS request_total_elapsed_time,
req.start_time AS request_start_time,
-- Session stats
sess.memory_usage,
sess.cpu_time AS session_cpu_time,
sess.reads AS session_reads,
sess.writes AS session_writes,
sess.logical_reads AS session_logical_reads,
sess.total_scheduled_time AS session_total_scheduled_time,
sess.total_elapsed_time AS session_total_elapsed_time,
sess.last_request_start_time,
sess.last_request_end_time,
req.open_resultset_count AS open_resultsets,
sess.row_count AS session_row_count,
sess.prev_error,
tasks.pending_io_count,
-- Text/Plan handles
ISNULL (req.command, 'AWAITING COMMAND') AS command, 
req.plan_handle,
req.sql_handle,
req.statement_start_offset,
req.statement_end_offset,
@collection_time AS collection_time
FROM sys.dm_exec_sessions sess
LEFT OUTER MERGE JOIN sys.dm_exec_requests req  ON sess.session_id = req.session_id
LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_address
LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_address
WHERE
    sess.session_id <> @@SPID
    AND
    (
        (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping'))) -- active request
            OR
        (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0))              -- not active, but head blocker
    )
OPTION (FORCE ORDER)
Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspx
Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\QueryActivityUpload'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\SqlTraceCollect'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\SqlTraceUpload'... Error occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.
'
Loading SQL package '\\Data Collector\TSQLQueryCollect'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Loading SQL package '\\Data Collector\TSQLQueryUpload'... Error occurred: 'Could not load file or assembly 'TSQLParser, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.'
Committing analysis information to database...Completed.

Coordinator
Jan 29, 2013 at 2:17 AM

This is a bug in the installer.

It is failing to place the TSQLParser.dll file into the SQL 2012 folder.

To resolve, copy the file TSQLParser.dll from the folder "C:\Program Files (x86)\SQLServerMetadataToolkit\Dependency Analyzer 2008" to the folder "C:\Program Files (x86)\SQLServerMetadataToolkit\Dependency Analyzer 2012".

Keith

Coordinator
Jan 29, 2013 at 2:18 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jan 29, 2013 at 2:49 AM

Hi Keith,

It went further but am now getting this. The formatting is not coming out correctly here.

Enumerating Database metadata for Server=localhost;Database=dbunion;Integrated Security=SSPI;.Enumerate the Tables and their DependenciesCould not enumerate the database: Object reference not set to an instance of an object.Enumerating registered SSIS Data Flow components...DoneEnumerating registered SSIS Connection Managers...DoneEnumerating registered SSIS Tasks...DoneEnumerating File System Integration Services metadata.Enumerating Integration Services metadata.Loading SQL package '\\Data Collector\PerfCountersCollect'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query SELECT ? = CONVERT (int, SERVERPROPERTY ('ProcessID'))Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query EXEC dbo.sp_syscollector_event_onpackageupdate ?Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query {call dbo.sp_syscollector_event_onstatsupdate(?, N'PerfCountersCollect - Main Collect Loop', ?, ?, 0, ?)}Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Loading SQL package '\\Data Collector\PerfCountersUpload'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query SELECT path, performance_counter_id FROM snapshots.performance_counter_instancesPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call dbo.sp_syscollector_event_onstatsupdate (?, N'PerfCountersUpload - Main Upload Loop', ?, ?, ?, ?) }Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query execute [core].[sp_create_snapshot]  @collection_set_uid=?, @collector_type_uid=?, @machine_name=?, @named_instance=?, @log_id=?, @snapshot_id=? OUTPUTPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query execute [core].[sp_update_data_source]  @collection_set_uid=?, @machine_name=?, @named_instance=?, @days_until_expiration=?, @source_id=? OUTPUTPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Loading SQL package '\\Data Collector\QueryActivityCollect'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query SET NOCOUNT ON-- Get the collection time as UTC timeDECLARE @collection_time datetimeSET @collection_time = GETDATE()SELECTCONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id,-- IDs and Blocking IDssess.session_id, ISNULL (req.request_id, -1) AS request_id, ISNULL (tasks.exec_context_id, -1) AS exec_context_id, ISNULL (req.blocking_session_id, 0) AS blocking_session_id,CONVERT (bit, CASE                 WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1                ELSE 0              END) AS is_blocking,ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id, tasks.scheduler_id, DB_NAME(req.database_id) as database_name, req.[user_id], -- State informationLEFT (tasks.task_state, 10) AS task_state, LEFT (req.status, 15) AS request_status, LEFT (sess.status, 15) AS session_status,req.executing_managed_code, -- Session informationsess.login_time, sess.is_user_process, LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name], LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name], LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, -- Waits informationLEFT (ISNULL (req.wait_type, ''), 45) AS wait_type, LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type, ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource, LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description,-- Transaction informationreq.transaction_id, ISNULL(req.open_transaction_count, 0) AS open_transaction_count,COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level,-- Request statsreq.cpu_time AS request_cpu_time, req.logical_reads AS request_logical_reads, req.reads AS request_reads, req.writes AS request_writes, req.total_elapsed_time AS request_total_elapsed_time, req.start_time AS request_start_time, -- Session statssess.memory_usage, sess.cpu_time AS session_cpu_time, sess.reads AS session_reads, sess.writes AS session_writes, sess.logical_reads AS session_logical_reads, sess.total_scheduled_time AS session_total_scheduled_time, sess.total_elapsed_time AS session_total_elapsed_time, sess.last_request_start_time, sess.last_request_end_time, req.open_resultset_count AS open_resultsets, sess.row_count AS session_row_count, sess.prev_error, tasks.pending_io_count, -- Text/Plan handlesISNULL (req.command, 'AWAITING COMMAND') AS command,  req.plan_handle, req.sql_handle, req.statement_start_offset, req.statement_end_offset,@collection_time AS collection_timeFROM sys.dm_exec_sessions sess LEFT OUTER MERGE JOIN sys.dm_exec_requests req  ON sess.session_id = req.session_idLEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_addressLEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_addressWHERE     sess.session_id <> @@SPID    AND    (        (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping'))) -- active request            OR         (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0))            -- not active, but head blocker    )OPTION (FORCE ORDER)Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call [dbo].[sp_syscollector_event_onpackageupdate]( ? )}Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call [dbo].[sp_syscollector_event_onstatsupdate] (?, N'active_sessions_and_request collection', ?, ?, ?, ?) }Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Loading SQL package '\\Data Collector\QueryActivityUpload'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query {call dbo.sp_syscollector_event_onpackageupdate (?)}Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call dbo.sp_syscollector_event_onstatsupdate (?, N'active_sessions_and_requests upload', ?, ?, NULL, 0) }Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query SET NOCOUNT ONDECLARE @p1 datetimeSET @p1 = GETDATE()SELECT     [sql_handle],    statement_start_offset,    statement_end_offset,    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.      -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats     -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL.     MAX (plan_generation_num) AS plan_generation_num,    plan_handle,    MIN (creation_time) AS creation_time,     MAX (last_execution_time) AS last_execution_time,    SUM (execution_count) AS execution_count,    SUM (total_worker_time) AS total_worker_time,    MIN (min_worker_time) AS min_worker_time,           -- NULLable    MAX (max_worker_time) AS max_worker_time,    SUM (total_physical_reads) AS total_physical_reads,    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable    MAX (max_physical_reads) AS max_physical_reads,    SUM (total_logical_writes) AS total_logical_writes,    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable    MAX (max_logical_writes) AS max_logical_writes,    SUM (total_logical_reads) AS total_logical_reads,    MIN (min_logical_reads) AS min_logical_reads,       -- NULLable    MAX (max_logical_reads) AS max_logical_reads,    SUM (total_clr_time) AS total_clr_time,    MIN (min_clr_time) AS min_clr_time,                 -- NULLable    MAX (max_clr_time) AS max_clr_time,    SUM (total_elapsed_time) AS total_elapsed_time,    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable    MAX (max_elapsed_time) AS max_elapsed_time,    @p1 AS collection_timeFROM(    SELECT          [sql_handle],        statement_start_offset,        statement_end_offset,        plan_generation_num,        plan_handle,        creation_time,        last_execution_time,        execution_count,        total_worker_time,        min_worker_time,        max_worker_time,        total_physical_reads,        min_physical_reads,        max_physical_reads,        total_logical_writes,        min_logical_writes,        max_logical_writes,        total_logical_reads,        min_logical_reads,        max_logical_reads,        total_clr_time,        min_clr_time,        max_clr_time,        total_elapsed_time,        min_elapsed_time,        max_elapsed_time     FROM sys.dm_exec_query_stats AS q    -- Temporary workaround for VSTS #91422.  This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries.     UNION ALL     SELECT         r.[sql_handle],        r.statement_start_offset,        r.statement_end_offset,        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,        r.plan_handle,        ISNULL (qs.creation_time, r.start_time) AS creation_time,        r.start_time AS last_execution_time,        1 AS execution_count,        -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats         -- uses microseconds.  Convert ms to us.         r.cpu_time * CAST(1000 as bigint) AS total_worker_time,        qs.min_worker_time,     -- min should not be influenced by in-progress queries        r.cpu_time * CAST(1000 as bigint) AS max_worker_time,        r.reads AS total_physical_reads,        qs.min_physical_reads,  -- min should not be influenced by in-progress queries        r.reads AS max_physical_reads,        r.writes AS total_logical_writes,        qs.min_logical_writes,  -- min should not be influenced by in-progress queries        r.writes AS max_logical_writes,        r.logical_reads AS total_logical_reads,        qs.min_logical_reads,   -- min should not be influenced by in-progress queries        r.logical_reads AS max_logical_reads,        qs.total_clr_time,      -- CLR time is not available in dm_exec_requests        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests        -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats         -- uses microseconds.  Convert ms to us.         r.total_elapsed_time * CAST(1000 as bigint) AS total_elapsed_time,        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries        r.total_elapsed_time * CAST(1000 as bigint) AS max_elapsed_time    FROM sys.dm_exec_requests AS r     LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset         AND r.statement_end_offset = qs.statement_end_offset     WHERE r.sql_handle IS NOT NULL ) AS query_stats OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sqlGROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offsetPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query SET NOCOUNT ONDECLARE @p1 datetimeSET @p1 = GETDATE()SELECT     [sql_handle],    statement_start_offset,    statement_end_offset,    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.      -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats     -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL.     MAX (plan_generation_num) AS plan_generation_num,    plan_handle,    MIN (creation_time) AS creation_time,     MAX (last_execution_time) AS last_execution_time,    SUM (execution_count) AS execution_count,    SUM (total_worker_time) AS total_worker_time,    MIN (min_worker_time) AS min_worker_time,           -- NULLable    MAX (max_worker_time) AS max_worker_time,    SUM (total_physical_reads) AS total_physical_reads,    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable    MAX (max_physical_reads) AS max_physical_reads,    SUM (total_logical_writes) AS total_logical_writes,    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable    MAX (max_logical_writes) AS max_logical_writes,    SUM (total_logical_reads) AS total_logical_reads,    MIN (min_logical_reads) AS min_logical_reads,       -- NULLable    MAX (max_logical_reads) AS max_logical_reads,    SUM (total_clr_time) AS total_clr_time,    MIN (min_clr_time) AS min_clr_time,                 -- NULLable    MAX (max_clr_time) AS max_clr_time,    SUM (total_elapsed_time) AS total_elapsed_time,    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable    MAX (max_elapsed_time) AS max_elapsed_time,    @p1 AS collection_timeFROM(    SELECT          [sql_handle],        statement_start_offset,        statement_end_offset,        plan_generation_num,        plan_handle,        creation_time,        last_execution_time,        execution_count,        total_worker_time,        min_worker_time,        max_worker_time,        total_physical_reads,        min_physical_reads,        max_physical_reads,        total_logical_writes,        min_logical_writes,        max_logical_writes,        total_logical_reads,        min_logical_reads,        max_logical_reads,        total_clr_time,        min_clr_time,        max_clr_time,        total_elapsed_time,        min_elapsed_time,        max_elapsed_time     FROM sys.dm_exec_query_stats AS q    -- Temporary workaround for VSTS #91422.  This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries.     UNION ALL     SELECT         r.[sql_handle],        r.statement_start_offset,        r.statement_end_offset,        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,        r.plan_handle,        ISNULL (qs.creation_time, r.start_time) AS creation_time,        r.start_time AS last_execution_time,        1 AS execution_count,        -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats         -- uses microseconds.  Convert ms to us.         r.cpu_time * CAST(1000 as bigint) AS total_worker_time,        qs.min_worker_time,     -- min should not be influenced by in-progress queries        r.cpu_time * CAST(1000 as bigint) AS max_worker_time,        r.reads AS total_physical_reads,        qs.min_physical_reads,  -- min should not be influenced by in-progress queries        r.reads AS max_physical_reads,        r.writes AS total_logical_writes,        qs.min_logical_writes,  -- min should not be influenced by in-progress queries        r.writes AS max_logical_writes,        r.logical_reads AS total_logical_reads,        qs.min_logical_reads,   -- min should not be influenced by in-progress queries        r.logical_reads AS max_logical_reads,        qs.total_clr_time,      -- CLR time is not available in dm_exec_requests        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests        -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats         -- uses microseconds.  Convert ms to us.         r.total_elapsed_time * CAST(1000 as bigint) AS total_elapsed_time,        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries        r.total_elapsed_time * CAST(1000 as bigint) AS max_elapsed_time    FROM sys.dm_exec_requests AS r     LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset         AND r.statement_end_offset = qs.statement_end_offset     WHERE r.sql_handle IS NOT NULL ) AS query_stats OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sqlGROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offsetPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query [dbo].[sp_syscollector_text_query_plan_lookpup] ?,?,?Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query EXEC snapshots.sp_update_query_plan ?,?,?,?,?,?,?,?,?,?Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxError occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.'Loading SQL package '\\Data Collector\SqlTraceCollect'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query DECLARE @TraceStatus INTDECLARE @TraceID INTSELECT  @TraceID = id , @TraceStatus = statusFROM sys.tracesWHERE  [id] = ? AND ([path] LIKE ? + N'%.trc' OR is_default=1)IF @TraceID IS NULLBEGIN RAISERROR(14678, 16, -1, @TraceID)ENDELSEBEGIN IF @TraceStatus <> 1  EXEC sp_trace_setstatus @TraceID, 1ENDPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call dbo.sp_syscollector_event_onpackageupdate( ? )}Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query { call [dbo].[sp_syscollector_event_onstatsupdate] (?, N'Monitor trace', 0, 0, 0, ?) }Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query DECLARE @TraceStatus INTDECLARE @TraceID INTSELECT  @TraceID = id , @TraceStatus = statusFROM sys.tracesWHERE  [path] LIKE ? + N'%.trc' AND is_default=0IF NOT @TraceID IS NULLBEGIN  EXEC sp_trace_setstatus @TraceID, 0  EXEC sp_trace_setstatus @TraceID, 2ENDPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query DECLARE @TraceID INTSELECT @TraceID = ?IF EXISTS (SELECT id FROM sys.traces WHERE id = @TraceID AND is_default = 0)BEGIN    EXEC sp_trace_setstatus @TraceID, 0ENDPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Loading SQL package '\\Data Collector\SqlTraceUpload'... Error occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.'Loading SQL package '\\Data Collector\TSQLQueryCollect'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query EXEC dbo.sp_syscollector_get_tsql_query_collector_package_ids ?, ?, ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUTPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxThe exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query EXEC dbo.sp_syscollector_create_tsql_query_collector ?, ?, ?, ?Please report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Loading SQL package '\\Data Collector\TSQLQueryUpload'... The exception Could not load file or assembly 'Microsoft.Data.Schema.ScriptDom, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.was raised against query EXEC dbo.sp_syscollector_get_tsql_query_collector_package_ids ?, ?, ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUTPlease report to http://sqlmetadata.codeplex.com/WorkItem/List.aspxCompleted Successfully.Committing analysis information to database...Completed.

Coordinator
Jan 29, 2013 at 3:43 AM

2 more DLL's required from the "C:\Program Files (x86)\SQLServerMetadataToolkit\Dependency Analyzer 2008" folder:

Microsoft.Data.Schema.ScriptDom.dll and Microsoft.Data.Schema.ScriptDom.Sql.dll

ie.  All the DLL's from the 2008 folder need to be copied to the 2012 folder.

This will get rid of the assembly missing errors.  (My work PC is working against 2012 using these workarounds, I'd only tested on my home PC, whilst in the debugger).

I also note that it's having a problem with the database enumeration.  Do you have a database called dbunion on the localhost SQL Server?

 

Jan 29, 2013 at 3:57 AM

 

Yes there is a db called dbunion which is the one I am trying to enumerate.

Very close here.

Also am assuming that after doing this I will be able to get column lineage information from the SSIS package.

 

Enumerating Database metadata for Server=localhost;Database=dbunion;Integrated Security=SSPI;.

Enumerate the Tables and their Dependencies

Enumerate the Views and their Dependencies

Enumerate the StoredProcedures and their Dependencies

Enumerate the UserDefinedFunction and their Dependencies

Enumerating registered SSIS Data Flow components...Done

Enumerating registered SSIS Connection Managers...Done

Enumerating registered SSIS Tasks...Done

Enumerating File System Integration Services metadata.

Enumerating Integration Services metadata.

Loading SQL package '\\PerfCountersCollect'... The following messages where generated whilst parsing the sql statement

{call dbo.sp_syscollector_event_onstatsupdate(?, N'PerfCountersCollect - Main Collect Loop', ?, ?, 0, ?)}

TSP0010 Incorrect syntax near {.

Completed Successfully.

Loading SQL package '\\Data Collector\PerfCountersCollect'... The following messages where generated whilst parsing the sql statement

{call dbo.sp_syscollector_event_onstatsupdate(?, N'PerfCountersCollect - Main Collect Loop', ?, ?, 0, ?)}

TSP0010 Incorrect syntax near {.

Completed Successfully.

Loading SQL package '\\Data Collector\PerfCountersUpload'... The following messages where generated whilst parsing the sql statement

{ call dbo.sp_syscollector_event_onstatsupdate (?, N'PerfCountersUpload - Main Upload Loop', ?, ?, ?, ?) }

TSP0010 Incorrect syntax near {.

Completed Successfully.

Loading SQL package '\\Data Collector\QueryActivityCollect'... The following messages where generated whilst parsing the sql statement

{ call [dbo].[sp_syscollector_event_onpackageupdate]( ? )}

TSP0010 Incorrect syntax near {.

The following messages where generated whilst parsing the sql statement

{ call [dbo].[sp_syscollector_event_onstatsupdate] (?, N'active_sessions_and_request collection', ?, ?, ?, ?) }

TSP0010 Incorrect syntax near {.

Completed Successfully.

Loading SQL package '\\Data Collector\QueryActivityUpload'... The following messages where generated whilst parsing the sql statement

{call dbo.sp_syscollector_event_onpackageupdate (?)}

TSP0010 Incorrect syntax near {.

The following messages where generated whilst parsing the sql statement

{ call dbo.sp_syscollector_event_onstatsupdate (?, N'active_sessions_and_requests upload', ?, ?, NULL, 0) }

TSP0010 Incorrect syntax near {.

Error occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

'

Loading SQL package '\\Data Collector\SqlTraceCollect'... The following messages where generated whilst parsing the sql statement

{ call dbo.sp_syscollector_event_onpackageupdate( ? )}

TSP0010 Incorrect syntax near {.

The following messages where generated whilst parsing the sql statement

{ call [dbo].[sp_syscollector_event_onstatsupdate] (?, N'Monitor trace', 0, 0, 0, ?) }

TSP0010 Incorrect syntax near {.

Completed Successfully.

Loading SQL package '\\Data Collector\SqlTraceUpload'... Error occurred: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

'

Loading SQL package '\\Data Collector\TSQLQueryCollect'... Completed Successfully.

Loading SQL package '\\Data Collector\TSQLQueryUpload'... Completed Successfully.

Loading SQL package '\\SanjayDeploy\Package'... Completed Successfully.

Loading SQL package '\\SanjayDeploy\Package-Sanjay'... Completed Successfully.

Committing analysis information to database...Completed.

 

Coordinator
Jan 29, 2013 at 4:39 AM

Looks like only the Data Collector packages are problematic now.

The Data Collector packages are "painful" as they have some extra little bits in them which aren't currently handled correctly.  (ODBC { call } syntax)

Your database and your packages have been analysed.  If you use the view button, and select the last scan (after hitting load), you should see the results.

Column level within the SSIS package states where the column started from for each transformation.

eg. For an OLE DB Destination you would get output like:

OLE DB Destination Input [Generic Column][ID:123]          From [Generic Source] Column [Demo Column 1] of Type [DT_STR] Length 30

Information is stored in the dbo.ObjectAttributes table, within the ObjectAttrValue column.

SELECT [ObjectName], [ObjectAttrName], [ObjectAttrValue]
  FROM [dbo].[ObjectAttributes]
  INNER JOIN [dbo].[Objects]
 ON [ObjectAttributes].[RunKey] = [Objects].[RunKey]
 AND [ObjectAttributes].[ObjectKey] = [Objects].[ObjectKey]
  INNER JOIN [dbo].[ObjectTypes]
 ON [Objects].[ObjectTypeString] = [ObjectTypes].[ObjectTypeKey]
  WHERE [ObjectAttributes].[RunKey] = (SELECT MAX([RunKey]) FROM [dbo].[RunScan])
  AND [ObjectTypeName] = 'OLE DB Destination'

 

 

There is an open enhancement request to make full column level tracking available, which has proved rather difficult, and is dependant on me getting more time to work on it, which I haven't had since September last year.