October 28, 2014 at 5:57 am
Hi,
I'm working on a meta data driven SSIS package creation tool. My experience with BIML is limited and I'm sorry if my question has a simple answer but I can't seem to find any resource that gives me the answer i'm looking for.
Where i'm stuck is in my data flow transformations area with a lookup transformation. Currently my transformations look like this:
<Transformations>
<OleDbSource Name="<#=row["SourceTableName"]#> ID's From Staged Data" ConnectionName="EDW">
<DirectInput><#=row["DataFromStagedFact"]#></DirectInput>
</OleDbSource>
<Lookup Name="<#=row["SourceTableName"]#> Details From Source" OleDbConnectionName="AdventureWorks2012">
<DirectInput><#=row["DataFromSource"]#></DirectInput>
<InputPath OutputPathName="<#=row["SourceTableName"]#> ID's From Staged Data.Output" />
<Inputs>
<Column SourceColumn="TerritoryID" TargetColumn="TerritoryID" />
</Inputs>
<Outputs>
<Column SourceColumn="Name" TargetColumn="Name" />
</Outputs>
</Lookup>
</Transformations>
What I want to be able to do is programatically workout what my columns are based on my input path, and work out what my outputs are based on my direct input query which is stored in an external table.
To further explain,
I have a query that returns a TerritoryID, this is a column in <InputPath OutputPathName="<#=row["SourceTableName"]#> ID's From Staged Data.Output" />
I have a direct input query that also returns a TerritoryID <DirectInput><#=row["DataFromSource"]#></DirectInput> and that is how i wish to perform my lookup. In other packages a lookup may be performed using a composite key.
I also what to output all columns from the <DirectInput><#=row["DataFromSource"]#></DirectInput> to use later in the data flow. I know how to hard code them as shown above, but i want to be able to work them out using code.
I've seen a few examples where this has been done where column lists have been looked up from tables, but i'm not using tables i'm using queries so my column list is not static. Hopefully this makes sense.
Basically what i want to be able to do is something along the lines of a foreach loop around something like <#=row["SourceTableName"]#> ID's From Staged Data.Output.Columns and <#=row["DataFromSource"]#>.Columns but i'm not sure if this is possible. SSIS works out the column list at design time, so I guess BIML must support it too?
Thanks for any help in advance.
Simon
October 29, 2014 at 8:34 am
October 29, 2014 at 8:38 am
Thanks for your reply! I have now sorted this but not using the URL you reference. The problem with the example you reference is that the columns are "hard coded". I needed to work them out dynamically. I'll post my solution here hopefully in the next day or two.
October 29, 2014 at 8:46 am
Would appreciate that
E
October 29, 2014 at 8:56 am
Hi,
Ok so here it is:
<Transformations>
<OleDbSource ConnectionName="EDW" Name="<#=row["SourceTableName"]#> ID's From Staged Data">
<DirectInput><#=row["DataFromStagedFact"]#></DirectInput>
</OleDbSource>
<Lookup Name="<#=row["SourceTableName"]#> Details From Source" OleDbConnectionName="AdventureWorks2012">
<DirectInput><#=row["DataFromSource"]#></DirectInput>
<InputPath OutputPathName="<#=row["SourceTableName"]#> ID's From Staged Data.Output"></InputPath>
<# string InputColumnMetaData = row["DataFromStagedFact"].ToString(); #>
<# string OutputColumnMetaData = row["DataFromSource"].ToString(); #>
<# DataTable InputColumns = ExternalDataAccess.GetDataTable(EDWDataConnection,InputColumnMetaData); #>
<# DataTable OutputColumns = ExternalDataAccess.GetDataTable(AdventureWorks2012DataConnection,OutputColumnMetaData); #>
<Inputs>
<# foreach(var icol in InputColumns.Columns){ #>
<Column SourceColumn="<#=icol#>" TargetColumn="<#=icol#>" />
<# } #>
</Inputs>
<Outputs>
<# foreach(var ocol in OutputColumns.Columns){ #>
<# foreach(var icol in InputColumns.Columns){ #>
<# if(icol != ocol){ #>
<Column SourceColumn="<#=ocol#>" TargetColumn="<#=ocol#>" />
<# } #>
<# } #>
<# } #>
</Outputs>
</Lookup>
</Transformations>
Essentially, I have queries stored in Varchar fields in a table. They are row["DataFromStagedFact"] and row["DataFromSource"]. By converting these to strings and then executing them it gives me a result set to loop through to work out my columns. I have special requirements around output columns which may be different to yours but this works for me at the moment.
It's maybe not the best way, but i'm doing a POC at the moment and it works so i'm running with it.
Hope it helps....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply