Skip to content

Instantly share code, notes, and snippets.

@billinkc
Created August 8, 2014 19:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save billinkc/41e90bdc7e607f5bd0b2 to your computer and use it in GitHub Desktop.
Save billinkc/41e90bdc7e607f5bd0b2 to your computer and use it in GitHub Desktop.
A potential reproduction for Connect item 901313
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=msdb" Name="CM_OLE_DB" />
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="swasheck">
<Variables>
<Variable Name="instanceID" DataType="Int32">0</Variable>
</Variables>
<Tasks>
<Dataflow Name="DFT Repro">
<Transformations>
<!--
Generate our data
-->
<OleDbSource Name="OLE_SRC" ConnectionName="CM_OLE_DB">
<DirectInput>
SELECT j.[name] as [JobName]
, [job_id]
, suser_sname(j.owner_sid) AS [OwnerNameWSTR]
, j.[enabled]
, j.[description]
, j.[date_created]
, j.[date_modified]
, CASE j.[notify_level_email] WHEN 0 THEN NULL WHEN 1 THEN 'Success' WHEN 2 THEN 'Failure' WHEN 3 THEN 'Completion' ELSE 'UNKNOWN' END AS [NotifyLevelEmail]
, s.[name] AS [NotifyOperatorName]
, s.[email_address] AS [NotifyOperatorEmail]
FROM [msdb].[dbo].[sysjobs] j
LEFT OUTER JOIN [msdb].[dbo].[sysoperators] s ON j.[notify_email_operator_id] = s.[id]
ORDER BY j.[name]
</DirectInput>
<Columns>
<Column SourceColumn="OwnerName" TargetColumn="OwnerNameWSTR" />
<Column SourceColumn="description" TargetColumn="descriptionWSTR" />
<Column SourceColumn="NotifyLevelEmail" TargetColumn="NotifyLevelEmailWSTR" />
<Column SourceColumn="NotifyOperatorName" TargetColumn="NotifyOperatorNameWSTR" />
<Column SourceColumn="NotifyOperatorEmail" TargetColumn="NotifyOperatorEmailWSTR" />
</Columns>
</OleDbSource>
<!--
Do the things that make it go boom
-->
<DerivedColumns Name="DER Go boom">
<Columns>
<Column Name="InstanceID" DataType="Int32">@[User::instanceID]</Column>
<Column Name="OwnerName" DataType="AnsiString" Length="200">(DT_STR, 200, 1252)OwnerNameWSTR</Column>
<Column Name="Description" DataType="AnsiString" Length="2000">(DT_STR, 2000, 1252)descriptionWSTR</Column>
<Column Name="NotifyLevelEmail" DataType="AnsiString" Length="20">(DT_STR, 20, 1252)NotifyLevelEmailWSTR</Column>
<Column Name="NotifyOperatorName" DataType="AnsiString" Length="200">(DT_STR, 200, 1252)NotifyOperatorNameWSTR</Column>
<Column Name="NotifyOperatorEmail" DataType="AnsiString" Length="200">(DT_STR, 200, 1252)NotifyOperatorEmailWSTR</Column>
</Columns>
</DerivedColumns>
<!--
Some transformations just want to watch the world burn
-->
<DerivedColumns Name="DER bit bucket"></DerivedColumns>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment