February 26, 2009 at 6:40 am
Hi there. I have been using SSIS 2005 for a number of years, but up to now have been manually creating SSIS packages using data flow tasks. I now have a need to be able to programmatically create the data flow tasks (using VB) based on metadata held in an SQL2005 table.
Everything is going fine, except that I have been unable to develop a working prototype of the slowly changing dimension task. My problem has been that I have been update to find the correct code to set the tablename property and then assign columns to business key, historical, or changing attribute.
Does anyone have a working example of how to do this, or even a code snippet that will show me how to doe this. I have put my code below FYI. I would appreciate any comments or suggestions that you might have.
Thanks in advance
Sub dataflowscd(ByVal Package, ByVal ConnectionName, ByVal ConnectionNameDest, ByVal DestTableName, ByVal TaskNo)
'----------------------------------------------------------------------------------------------------------------
'--create a slowly changing dimension task
'--Declare slowly changing dimension dataflow task
'The Application object will be used to obtain the CreationName
' of a PipelineComponentInfo from its PipelineComponentInfos collection.
Dim scd As Executable = Package.Executables.Add("DTS.Pipeline.1")
Dim thMainPipescd As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(scd, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTaskscd As MainPipe = CType(thMainPipescd.InnerObject, MainPipe)
thMainPipescd.Name = ToString(TaskNo) + "Slowly Changing Dimension Task"
'--set the source and destination connections
Dim conMgr As ConnectionManager = Package.Connections.Item(ConnectionName)
Dim conMgrDest As ConnectionManager = Package.Connections.Item(ConnectionNameDest)
''------------------------------------------------------------------------------------------------------------------------------------
'Add task level variables
Dim tskVars As Variables = thMainPipescd.Variables
Dim mytskVar As Variable = thMainPipescd.Variables.Add("test888", False, "User", "hello")
Dim myUserTableVar As Variable = thMainPipescd.Variables.Add("UserTable", False, "User", "Currency")
' Create and configure an OLE DB source component.
Dim sourceSCD As IDTSComponentMetaData90 = dataFlowTaskscd.ComponentMetaDataCollection.New
sourceSCD.ComponentClassID = "DTSAdapter.OleDbSource"
' Create the design-time instance of the source.
Dim srcDesignSCDTime As CManagedComponentWrapper = sourceSCD.Instantiate
' The ProvideComponentProperties method creates a default output.
srcDesignSCDTime.ProvideComponentProperties()
'Assign the connection manager.
If sourceSCD.RuntimeConnectionCollection.Count > 0 Then
sourceSCD.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conMgr)
sourceSCD.RuntimeConnectionCollection(0).ConnectionManagerID = conMgr.ID 'package.Connections(conMgr2)
End If
sourceSCD.Name = "test source"
sourceSCD.ValidateExternalMetadata = "True"
'source.RuntimeConnectionCollection(0).
' Set the custom properties of the source.
srcDesignSCDTime.SetComponentProperty("AccessMode", 0)
srcDesignSCDTime.SetComponentProperty("OpenRowset", "dbo.TBL_SDV_SUB_DIVISION")
srcDesignSCDTime.SetComponentProperty("SqlCommand", "Select * from dbo.TBL_1")
' Connect to the data source,
' and then update the metadata for the source.
srcDesignSCDTime.AcquireConnections(Nothing)
srcDesignSCDTime.ReinitializeMetaData()
srcDesignSCDTime.ReleaseConnections()
Dim componentscd As IDTSComponentMetaData90 = dataFlowTaskscd.ComponentMetaDataCollection.New()
componentscd.ComponentClassID = "DTSTransform.SCD.1"
componentscd.Name = "SCD"
componentscd.Description = "ChangingColumnAttribute"
Dim scdDesignTime As CManagedComponentWrapper = componentscd.Instantiate
scdDesignTime.ProvideComponentProperties()
scdDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[" + DestTableName + "]")
scdDesignTime.SetComponentProperty("CurrentRowWhere", "")
scdDesignTime.SetComponentProperty("UpdateChangingAttributeHistory", "true")
scdDesignTime.SetComponentProperty("FailOnFixedAttributeChange", "true")
'scdDesignTime.SetComponentProperty("OpenRowset", "dbo.TBL_SDV_SUB_DIVISION2")
'scdDesignTime.SetComponentProperty("TableName", "[dbo].[" + DestTableName + "]")
'scdDesignTime.SetComponentProperty("OpenRowset", DestTableName)
componentscd.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conMgrDest) 'package.Connections(1))
componentscd.RuntimeConnectionCollection(0).ConnectionManagerID = conMgrDest.ID 'package.Connections(conMgr2)
'' Create the path from source to destination.
Dim pathscd As IDTSPath90 = dataFlowTaskscd.PathCollection.New
pathscd.AttachPathAndPropagateNotifications(sourceSCD.OutputCollection(0), componentscd.InputCollection(0))
'' Get the destination's default input and virtual input.
Dim inputscd As IDTSInput90 = componentscd.InputCollection(0)
Dim componentscdInputID As Integer = CInt(inputscd.ID)
Dim vInputscd As IDTSVirtualInput90 = inputscd.GetVirtualInput()
End Sub
February 26, 2009 at 1:48 pm
There is a component at Codeplex see if you can use it and no I have not used it but you can post any issues at the site so the project owner can help you.
http://www.codeplex.com/kimballscd
Kind regards,
Gift Peddie
February 28, 2009 at 7:24 pm
Thanks for the suggestion. I will look into it. I am not certain whether I am in a position to install additional software on all of the SQL Server instances that this will need to work in so I will probably still have to find a solution to my current problem.
March 1, 2009 at 6:15 pm
I do this using sql fom the table definitions.
Create an SSIS package to import the data to a staging table (xml is created from a template and the source/destination columns added in a temp table which is then bcp'd out to the .dtsx fil).
Then create an SP which takes the data in the staging table, compares it to the production table and performs the inserts/updates. The sp script is again created from the table definition and bcp'd to a file then the SP created via osql.
In this way I dn't have to do any coding for dimensions apart from the data extract. I use the same method to tranfer data across servers.
If you are interested I can probably release the (long overdue) article on this or maybe send you the unfinished version.
Cursors never.
DTS - only when needed and never to control.
March 11, 2009 at 10:46 am
Thanks for the generous offer. Personally I know how to do this using TSQL, so I don't think I need to bother you with that. I am sure that there would be lots of BI professionals who would benefit from an article on this subject.
My personal situation is that I need to find a solution to creating the SCD programmatically, because they are simple to the end users of the SSIS packages to understand (rather than being efficient ETL)
Thanks again
Mike
July 2, 2009 at 8:15 am
On the off-chance anyone else stumbles across this thread (as I did) looking for the solution, you have to get a reference to the input columns (inputscd in the example above) and then reference the CustomPropertyCollection of the column eg:
Dim props As IDTSCustomPropertyCollection90 = inputscd.CustomPropertyCollection
For Each prop As IDTSCustomProperty90 In props
If prop.Name = "ColumnType" Then
prop.Value = 1 'Business key
End If
Next
ColumnType is the property that determines whether a column is business key, historical, or changing attribute. The values you set go as follows:
0=Other
1=Key
2=Changing attribute
3=Historical attribute
4=Fixed attribute
If I get the time I'll add a full listing for setting up a SCD in my blog (see signature).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply