World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Monday, February 23, 2009

Validation error. An input column with the lineage ID , referenced in the ParameterMap custom property with the parameter on position number 1, cannot be found in the input columns collection

A great deal of thanks to Catherine Eibner for her blog post
http://blog.cybner.com.au/2008/03/working-with-complex-lookups-in-ssis.html

We had used date ranges in lookups here before but one coworker did
"something" to a package that just kept breaking. I ran into the same
issue (Validation error. An input column with the lineage ID ,
referenced in the ParameterMap custom property with the parameter on
position number 1, cannot be found in the input columns collection)
and luckily came across her posting. The short of it, in case her
post goes away, is that even though you are explicitly mapping values
in the advanced tab, you must map it as well in the Columns tab. The
Columns mapping will be discarded but it must be done. *shrug* These
are the mysteries of SSIS and simply need to be acknowledged. Read
her post, far better than this one but I wanted to link to it as there
weren't many hits from google when I searched for it.

Posted by at 1 comment: Links to this post

Wednesday, February 18, 2009

Foreach Nodelist Enumerator

As a followup to my fumblings with Foreach From Variable Enumerator, in theory I was right but I misremembered my available enumerators.

What I am attempting to do is keep an externally configured list that is consumed by an enumerator. That isn't a database table. I haven't given up on finding a useful case for the FEFVE, but as I have slightly more than a month before my project is due, I need to take a working path and run with it.

The solution I'm going to go with is fairly simple. I have a script task that will take my input string and transform it into a simple XML blob. That blob will be shredded by an Foreach Nodelist Enumerator and voila it's done. And really, I spent more time trying to build my XML using the real libraries instead of cobbling together strings than it took to wire the enumerator up.

From ProfessionalBlog


Given a starting value of FileList:Foo.txt;Bar.txt;Blee.txt

The Script task will generate output of


   Foo.txt
   Bar.txt
   Blee.txt



An interesting thing to note, I am using a semi-colon as my delimiter. Assuming I tried to set the value at runtime via dtexec, the not-entirely-intuitive manner of escaping the semicolon is to put it all in quotes. And then escape the quotes.

C:\>dtexec /File XMLEnumerator.dtsx /Set \Package.Variables[User::FileList].Properties[Value];\"A.txt;b.txt;c.csv;d.dat\"


The package has been uploaded to my skydrive

References:
In memory XML Build a formatted XML document in memory by using an XmlTextWriter in VB .NET
Posted by at No comments: Links to this post
Labels:

TSQL protip

SELECT T.* FROM dbo.MYTABLE T WHERE T.last_mod_date > 2008-12-10
That is not the same as
SELECT T.* FROM dbo.MYTABLE T WHERE T.last_mod_date > '2008-12-10'

The first one is going to turn the "date" into an integer and then
cast as a date which isn't quite what I was anticipating.
SELECT T.* FROM dbo.MYTABLE T WHERE T.last_mod_date > 1994

And now with that crisis solved, I'm making a cup of tea to wake up
from being a noob.

Posted by at No comments: Links to this post

OS X application idea

This is a note for me on a project that has been percolating about in
my mind. 20 years ago this May, I graduated from grade school. I
went to a parochial grade school so for me, grade school is
kindergarten through 8th grade. There's a great deal of interest and
momentum for a reunion to take place and we have had a blast scanning
old photos and sharing them via FaceBook.

What I thought would be a great idea is to write a registration/sign
in application. It would look something like the title screen of the
Brady Bunch television program, with pictures of everyone from grade
school and you click your photo to "sign in." Unbeknownst to them,
signing in will also capture the current image from iSight and then
your badge prints up with your name and a photo of you then and now.
I think that'd be pretty slick and a lot of laughs.

Posted by at No comments: Links to this post

Friday, February 13, 2009

sp_send_dbmail

<?xml version="1.0"?><DTS:Executable
xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ExecutableType="MSDTS.Package.1"><DTS:Property
DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property
DTS:Name="VersionComments"></DTS:Property><DTS:Property
DTS:Name="CreatorName">ANGBAND\BFellows</DTS:Property><DTS:Property
DTS:Name="CreatorComputerName">ANGBAND</DTS:Property><DTS:Property
DTS:Name="CreationDate" DTS:DataType="7">2/13/2009 12:16:57
PM</DTS:Property><DTS:Property
DTS:Name="PackageType">5</DTS:Property><DTS:Property
DTS:Name="ProtectionLevel">0</DTS:Property><DTS:Property
DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property
DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property
DTS:Name="VersionMajor">1</DTS:Property><DTS:Property
DTS:Name="VersionMinor">0</DTS:Property><DTS:Property
DTS:Name="VersionBuild">16</DTS:Property><DTS:Property
DTS:Name="VersionGUID">{948EA295-2AAA-4305-B442-0A70FF3B3048}</DTS:Property><DTS:Property
DTS:Name="EnableConfig">0</DTS:Property><DTS:Property
DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property
DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property
DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property
DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property
DTS:Name="DelayValidation">0</DTS:Property><DTS:Property
DTS:Name="ObjectName">SYSDEVDB</DTS:Property><DTS:Property
DTS:Name="DTSID">{6CB7F3A4-B093-4927-A729-BA53C84E9FEF}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property
DTS:Name="Retain">0</DTS:Property><DTS:Property
DTS:Name="ConnectionString">Data Source=localhost;Initial
Catalog=SYSDEVDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue"
DTS:DataType="8">&lt;Package
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;5488&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}"
mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}"
defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout"
defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout"
version="7" nextobject="5" scale="100" pagebreakanchorx="0"
pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0"
scrollleft="0" scrolltop="4133" gridx="150" gridy="150" marginx="1000"
marginy="1000" zoom="100" x="23093" y="3260" backcolor="15334399"
defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0"
PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0"
marqueeselectionmode="1" mousepointer="0" snaptogrid="0"
autotypeannotation="1" showscrollbars="0" viewpagebreaks="0"
donotforceconnectorsbehindshapes="1"
backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper
binary="010000009001b0300100065461686f6d61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1"
tooltip="Execute SQL Task" left="7557" top="5525" logicalid="3"
controlid="3" masterid="0" hint1="0" hint2="0" width="2879"
height="931" noresize="0" nomove="0" nodefaultattachpoints="0"
autodrag="1" usedefaultiddshape="1" selectable="1"
showselectionhandles="1" allownudging="1" isannotation="0"
dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1"
snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="000800003f0b0000a3030000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject"
value="{43937552-77DF-479F-83A9-69AF05D2F19A}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;ddscontrol controlprogid="MSDDS.Text" left="11176" top="5488"
logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0"
width="6000" height="1500" noresize="0" nomove="0"
nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="1"
selectable="1" showselectionhandles="1" allownudging="1"
isannotation="0" dontautolayout="1" groupcollapsed="0" tabstop="1"
visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper
binary="0002000070170000dc0500000300640000000500008008000080000000002a00010000009001b0300100065461686f6d6146004300430020002600200042004300430020006d00750073007400200062006500200065006d00700074007900200073007400720069006e0067007300200061006e00640020006e006f00740020007400680065000d000a0062006c0061006e006b0020006300680061007200610063007400650072002000280041005300430049004900200033003200290000000000"
/&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;dwd:PersistedViewPortTop&gt;4133&lt;/dwd:PersistedViewPortTop&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property
DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property
DTS:Name="ObjectName">{C52F4A9E-8D1F-49EA-891D-81B4F432424B}</DTS:Property><DTS:Property
DTS:Name="DTSID">{05B150FD-1306-4DB3-90B7-451AE338824A}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property
DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property
DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property
DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property
DTS:Name="Disabled">0</DTS:Property><DTS:Property
DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property
DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property
DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property
DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property
DTS:Name="LocaleID">1033</DTS:Property><DTS:Property
DTS:Name="TransactionOption">1</DTS:Property><DTS:Property
DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">1</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">AppendQueryError</DTS:Property><DTS:Property
DTS:Name="DTSID">{FFADC407-8E55-4CB3-BB4E-C3637F712042}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">1</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">AttachQueryResultAsFile</DTS:Property><DTS:Property
DTS:Name="DTSID">{2A3C9558-F4FD-4393-804A-3AC52165F459}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">bfellows@waddell.com</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">BlindCopyRecipients</DTS:Property><DTS:Property
DTS:Name="DTSID">{1055FB12-F10E-40A9-92B0-E63AC4A5EC17}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">Test</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Body</DTS:Property><DTS:Property
DTS:Name="DTSID">{1BF84C2F-F12B-44A7-A73E-C118FAAE9871}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">TEXT</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">BodyFormat</DTS:Property><DTS:Property
DTS:Name="DTSID">{3E61C1EF-D3DA-4FD0-B6CD-D4E2F9967154}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">bfellows@waddell.com</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">CopyRecipients</DTS:Property><DTS:Property
DTS:Name="DTSID">{0A8B5123-0DDE-4D7F-883D-A533595301C6}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">1</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">ExcludeQueryOutput</DTS:Property><DTS:Property
DTS:Name="DTSID">{44FB0875-D31E-44DB-997D-66A29EA24B7D}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">SYSDEVDB</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">ExecuteQueryDatabase</DTS:Property><DTS:Property
DTS:Name="DTSID">{22164ADD-559F-4422-A95E-D4D3A8FA6DB1}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8"></DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">FileAttachments</DTS:Property><DTS:Property
DTS:Name="DTSID">{25C775D4-B50C-41EA-8673-03A042174F75}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">Low</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Importance</DTS:Property><DTS:Property
DTS:Name="DTSID">{A682CD88-53CA-4A65-9A0E-752816D84E55}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">MailItemId</DTS:Property><DTS:Property
DTS:Name="DTSID">{62F3CDA4-E946-45F1-A850-CCD543F7A24E}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">SQLADMIN</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">ProfileName</DTS:Property><DTS:Property
DTS:Name="DTSID">{C5BBDEB7-8BEB-45E4-91ED-F24D43981A36}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">SELECT COUNT(1) AS rowcount FROM
dbo.sysdtslog90</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Query</DTS:Property><DTS:Property
DTS:Name="DTSID">{9302571A-AAE5-4AD6-A1BC-8B344483425D}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">ResultFile.txt</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryAttachmentFilename</DTS:Property><DTS:Property
DTS:Name="DTSID">{7617B509-3D98-4CAA-864D-EE5AF32EB625}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">0</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryNoTruncate</DTS:Property><DTS:Property
DTS:Name="DTSID">{B645F568-33F5-40E7-885C-63B74239F940}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">1</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryResultHeader</DTS:Property><DTS:Property
DTS:Name="DTSID">{0F450F57-5C3F-43D3-A825-AFF47FC86A22}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="17">1</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryResultNoPadding</DTS:Property><DTS:Property
DTS:Name="DTSID">{FB1984E0-19B8-40E7-88D7-0119C7403B4E}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="18">124</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryResultSeparator</DTS:Property><DTS:Property
DTS:Name="DTSID">{0DA31CBC-8656-41AA-96E6-C22313F8F325}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="3">256</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">QueryResultWidth</DTS:Property><DTS:Property
DTS:Name="DTSID">{AA33DC4C-8922-4904-B780-7CEB316898A3}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">bfellows@waddell.com</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Recipients</DTS:Property><DTS:Property
DTS:Name="DTSID">{8C7C76AE-DBAC-409D-AC8D-E325F785A42E}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">Normal</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Sensitivity</DTS:Property><DTS:Property
DTS:Name="DTSID">{17171E8A-DB1D-4E15-94B4-EBCE3BCDF917}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property
DTS:Name="Expression"></DTS:Property><DTS:Property
DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property
DTS:Name="Namespace">User</DTS:Property><DTS:Property
DTS:Name="ReadOnly">0</DTS:Property><DTS:Property
DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue
DTS:DataType="8">Test</DTS:VariableValue><DTS:Property
DTS:Name="ObjectName">Subject</DTS:Property><DTS:Property
DTS:Name="DTSID">{8D1B5671-0188-434C-96CC-6EB4329F5AAD}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property
DTS:Name="LoggingMode">0</DTS:Property><DTS:Property
DTS:Name="FilterKind">1</DTS:Property><DTS:Property
DTS:Name="EventFilter"
DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable
DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask,
Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property
DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property
DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property
DTS:Name="TaskContact">Execute SQL Task; Microsoft Corporation;
Microsoft SQL Server v9; Â(c) 2004 Microsoft Corporation; All Rights
Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property
DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property
DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property
DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property
DTS:Name="Disabled">0</DTS:Property><DTS:Property
DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property
DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property
DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property
DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property
DTS:Name="LocaleID">-1</DTS:Property><DTS:Property
DTS:Name="TransactionOption">1</DTS:Property><DTS:Property
DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property
DTS:Name="LoggingMode">0</DTS:Property><DTS:Property
DTS:Name="FilterKind">1</DTS:Property><DTS:Property
DTS:Name="EventFilter"
DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property
DTS:Name="ObjectName">Execute SQL Task</DTS:Property><DTS:Property
DTS:Name="DTSID">{43937552-77DF-479F-83A9-69AF05D2F19A}</DTS:Property><DTS:Property
DTS:Name="Description">Execute SQL Task</DTS:Property><DTS:Property
DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask,
Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property
DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><SQLTask:SqlTaskData
SQLTask:Connection="{6CB7F3A4-B093-4927-A729-BA53C84E9FEF}"
SQLTask:TimeOut="0" SQLTask:IsStoredProc="False"
SQLTask:BypassPrepare="True" SQLTask:SqlStmtSourceType="DirectInput"
SQLTask:SqlStatementSource="EXECUTE msdb.dbo.sp_send_dbmail&#xA;
@profile_name = ?&#xA;, @recipients = ?&#xA;, @copy_recipients =
?&#xA;, @blind_copy_recipients = ?&#xA;, @subject = ?&#xA;,
@body = ?&#xA;, @body_format = ?&#xA;, @importance = ?&#xA;,
@sensitivity = ?&#xA;, @file_attachments = ?&#xA;, @query =
?&#xA;, @execute_query_database = ?&#xA;,
@attach_query_result_as_file = ?&#xA;, @query_attachment_filename =
?&#xA;, @query_result_header = ?&#xA;, @query_result_width =
?&#xA;, @query_result_separator = ?&#xA;, @exclude_query_output =
?&#xA;, @append_query_error = ?&#xA;, @query_no_truncate = ?&#xA;,
@query_result_no_padding = ?&#xA;, @mailitem_id = ?"
SQLTask:ResultType="ResultSetType_None"
xmlns:SQLTask="www.microsoft.com/sqlserver/dts/tasks/sqltask"><SQLTask:ParameterBinding
SQLTask:ParameterName="0" SQLTask:DtsVariableName="User::ProfileName"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="1" SQLTask:DtsVariableName="User::Recipients"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="2"
SQLTask:DtsVariableName="User::CopyRecipients"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="3"
SQLTask:DtsVariableName="User::BlindCopyRecipients"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="4" SQLTask:DtsVariableName="User::Subject"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="5" SQLTask:DtsVariableName="User::Body"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="6" SQLTask:DtsVariableName="User::BodyFormat"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="7" SQLTask:DtsVariableName="User::Importance"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="8" SQLTask:DtsVariableName="User::Sensitivity"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="9"
SQLTask:DtsVariableName="User::FileAttachments"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="10" SQLTask:DtsVariableName="User::Query"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="11"
SQLTask:DtsVariableName="User::ExecuteQueryDatabase"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="12"
SQLTask:DtsVariableName="User::AttachQueryResultAsFile"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="13"
SQLTask:DtsVariableName="User::QueryAttachmentFilename"
SQLTask:ParameterDirection="Input" SQLTask:DataType="130"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="14"
SQLTask:DtsVariableName="User::QueryResultHeader"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="15"
SQLTask:DtsVariableName="User::QueryResultWidth"
SQLTask:ParameterDirection="Input" SQLTask:DataType="3"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="16"
SQLTask:DtsVariableName="User::QueryResultSeparator"
SQLTask:ParameterDirection="Input" SQLTask:DataType="129"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="17"
SQLTask:DtsVariableName="User::ExcludeQueryOutput"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="18"
SQLTask:DtsVariableName="User::AppendQueryError"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="19"
SQLTask:DtsVariableName="User::QueryNoTruncate"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="20"
SQLTask:DtsVariableName="User::QueryResultNoPadding"
SQLTask:ParameterDirection="Input" SQLTask:DataType="17"
SQLTask:ParameterSize="-1"/><SQLTask:ParameterBinding
SQLTask:ParameterName="21" SQLTask:DtsVariableName="User::MailItemId"
SQLTask:ParameterDirection="Output" SQLTask:DataType="3"
SQLTask:ParameterSize="-1"/></SQLTask:SqlTaskData></DTS:ObjectData></DTS:Executable><DTS:Property
DTS:Name="ObjectName">sp_send_dbmail</DTS:Property><DTS:Property
DTS:Name="DTSID">{C52F4A9E-8D1F-49EA-891D-81B4F432424B}</DTS:Property><DTS:Property
DTS:Name="Description"></DTS:Property><DTS:Property
DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property
DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Posted by at No comments: Links to this post

Thursday, February 12, 2009

For Each From Variable Enumerator

I had a scenario come up at work where I'll be downloading N files from our mainframe. I don't want to grab all the files as there are other processes that will need to use them. I'm loathe to hardcode a list as as soon as I do that, something will change. My first solution was to use the For Each Item Enumerator and it was great. I made a list with an element corresponding to each file I needed and all is well and good in the world. It enumerated through the list and I was able to download each file just fine.

Wait, something's missing. Oh yes, externally configurable. A quick question on the MSDN SSIS boards revealed that I was SOL using the For Each Item Enumerator and external configuration. There are other flavors of the enumerator and this For Each From Variable Enumerator sounded interesting.

Take a quick moment to read up on what it does. It won't take long, go on, click the link and this will wait.   http://wiki.sqlis.com/default.aspx/SQLISWiki/ForEachFromVariableEnumerator.html According to the above, the variable enumerator splits a string out by characters. That could actually work out well for my scenario: a finite list of elements, file names in this case, is defined in a variable which is externally configurable. I could use the FEFVE to iterate through the list and concatenate the values until the separator has been reached. At that point, I'd perform the logic in the special branch. In my scenario, I'd download the file.

Simple, not horribly inelegant and it answers the question from SQLIS of "where would you use it." And if merits a blog post, then obviously it wasn't that easy. I admit, the wiki page did say I needed to create 2 variables, one to provide the value and one to be the current element (Thanks Doug). What didn't click was that the second item needed to be of type Object. It's just a string so I set it as such, wired up all my logic and let it run. Dur bluh, can't cast to type string exception. I guess the wiki article wasn't just being overly cautious with the use of type Object. Dear reader, do you know what's a pain in the Integration Services? Objects. They cannot be used in Expressions, even if you cast them. My original control flow was elegant, I had a third variable that was simply the concatenation of itself plus the current character. There went my simple solution, dashed against the rocks like some Björk song.

The hour grows late and if I don't post this tonight, it'll be another few months before I get back to it so this is posted without a working solution. The challenge I have run into is that even though the local inspector window can gracefully turn that object into a character, heck it even shows it in the post from SQLIS, I'll be damned if I can get the VB script to spit it out.

A thousand words here will help show how I thought about making use out of FEFVE

From ProfessionalBlog


Package is on my skydrive

[edit 2009-02-17]I had an insight this morning that might be much better way to use a standard iterator and external configuration. In short, continue using the variable as the source but have a script step outside the loop perform a split on the separator and push that into an variable of type object. I think one of the enumerators will be able to pop iterate over a .NET arrary. Or I suppose I could dump it into a different data structure that implements IEnumerable if that's easier/better. I'll either follow up this post with the results or edit this one. Follow up post: Foreach Nodelist Enumerator[/edit]
Posted by at 1 comment: Links to this post
Labels:

Monday, February 9, 2009

SSAS learning, long overdue

I've been commenting that I should get involved with BI for nearly a
year now. This weekend at a SharePoint training conference, I went to
a session on how BI can be used in SP and I think motivation finally
clicked in. I like to think I'm a TSQL ninja, my SSIS is pretty good,
SSRS isn't my favorite thing but I can do it but I can still remember
reading through BOL about snowflake pattern in the SQL Server 2000
days and how little that made sense. I think I have a better
vocabulary to begin understanding the whys and hows of OLAP so
hopefully I can stay motivated long enough to make sense of it and see
how we can fit this technology into our infrastructure. What I think
will be the easiest "sell" would be some simple cubes and then use
Excel 2007 as the UI into it.

As I find good sites for learning, I will pop them into this blog.
This seems a pretty potent site from my cursory passes through it.
http://www.ssas-info.com/analysis-services-articles/48-uncategorized/135-learning-sql-server-analysis-services-2005-and-mdx-resources-online

Posted by at No comments: Links to this post
Subscribe to: Posts (Atom)