Programmatically create data sources

Is there a way to do this? It does not appear as part of the standard SSIS API.

Thanks.

The DataSource and DataSourceCollection are part of the SSAS API with the latter having Add and AddNew methods. Not quite sure how to get the project context to create them, but never looked either. They are the same data sources in SSIS and SSAS projects, just do a View Code on them for each project, they are the same XSD etc.|||

Noa,

You can create anything programmatically (in a .NET application) that you can create in the BI Studio. Here is an example:

I'll create a new FlatFile ConnectionManager and it will have two columns, Name (40 chars long and Age, 3 chars long)

Dim cmflatFileNew As ConnectionManager = myPackage.Connections.Add("FLATFILE")
cmflatFileNew.Properties("ConnectionString").SetValue(cmflatFileNew, "C:\aFile.dat")
cmflatFileNew.Properties("Format").SetValue(cmflatFileNew, "FixedWidth")
cmflatFileNew.Properties("DataRowsToSkip").SetValue(cmflatFileNew, 0)
cmflatFileNew.Properties("ColumnNamesInFirstDataRow").SetValue(cmflatFileNew, False)
cmflatFileNew.Properties("Name").SetValue(cmflatFileNew, "FlatFileConnection New")
cmflatFileNew.Properties("RowDelimiter").SetValue(cmflatFileNew, vbCrLf)
cmflatFileNew.Properties("TextQualifier").SetValue(cmflatFileNew, """")

Dim ffNew As RuntimeWrap.IDTSConnectionManagerFlatFile90 = Nothing
ffNew = TryCast(cmflatFileNew.InnerObject, RuntimeWrap.IDTSConnectionManagerFlatFile90)

Dim newCol1 As RuntimeWrap.IDTSConnectionManagerFlatFileColumn90
Dim newName1 As RuntimeWrap.IDTSName90
newCol1 = ffNew.Columns.Add()
newCol1.ColumnType = "FixedWidth"
newCol1.DataType = RuntimeWrap.DataType.DT_STR
newCol1.ColumnWidth = 40
newCol1.MaximumWidth = 40
newName1 = TryCast(newCol1, RuntimeWrap.IDTSName90)
newName1.Name = "Name"

Dim newCol2 As RuntimeWrap.IDTSConnectionManagerFlatFileColumn90
Dim newName2 As RuntimeWrap.IDTSName90
newCol2 = ffNew.Columns.Add()
newCol2.ColumnType = "FixedWidth"
newCol2.DataType = RuntimeWrap.DataType.DT_I4
newCol2.ColumnWidth = 3
newCol2.MaximumWidth = 3
newName2 = TryCast(newCol2, RuntimeWrap.IDTSName90)
newNme2.Name = "Name"

If you have any more questions, post here and I can reply.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||

Thank you Mark.

I know how to create connection managers, as it is part of the standard API.

Data sources are design time components - part of the SSIS project and has no impact on programmatically creating packages.

Can you provide an example of accessing the SSIS project and adding a data source to it?

|||

Ahh, I see what you are asking about.

Actually - I don't know of a way to create a .ds file programmatically. I bet it can be done though. I do know that to add it to your project you will have to edit the dtproj file which is xml. That shouldn't be too dificult.

Sorry for the misunderstanding.

Mark

http://spaces.msn.com/mgarnerbi