SSIS API - Package creation SQL 2005

hi,

i trying make ssis package programmatically, , unfortunately has made using sql server 2005. ezapi on 2008 breeze :).

anyways, having trouble getting columns link between ole db source , destination. have followed examples on bol sql 2005, can't seem t figure out wrong code below. creates package fine, seems miss mapping of columns on destination. please :).

i should add, that both source , destination tables have exact same columns , datatypes. 

 

  ' create application      dim app as microsoft.sqlserver.dts.runtime.application = new microsoft.sqlserver.dts.runtime.application()          ' create(package)      dim pkg as package = new package()          ' -------------------- add connections ------------------------- '          ' add source connection      dim sourcecon as connectionmanager      sourcecon = pkg.connections.add("oledb")      sourcecon.connectionstring = "provider=sqloledb.1; integrated security=sspi;initial catalog=source; data source=(local);"      sourcecon.name = "source"          ' add destination connection      dim extractcon as connectionmanager      extractcon = pkg.connections.add("oledb")      extractcon.connectionstring = "provider=sqloledb.1; integrated security=sspi;initial catalog=extract; data source=(local);"      extractcon.name = "extract"              ' -------------------- build controlflow ----------------------- '          ' add dataflow      dim dataflowexec as executable = pkg.executables.add("dts.pipeline.1")      dim dataflow as taskhost = ctype(dataflowexec, taskhost)      dim dataflowmainpipe as mainpipe = ctype(dataflow.innerobject, mainpipe)      dataflow.name = "extract"          ' -------------------- build dataflow -------------------------- '          ' create source component.       dim source as idtscomponentmetadata90 = dataflowmainpipe.componentmetadatacollection.new()      source.componentclassid = "dtsadapter.oledbsource"      dim srcdesigntime as cmanagedcomponentwrapper = source.instantiate()      srcdesigntime.providecomponentproperties()      source.runtimeconnectioncollection(0).connectionmanager = dtsconvert.toconnectionmanager90(pkg.connections(1))      source.runtimeconnectioncollection(0).connectionmanagerid = pkg.connections(1).id      srcdesigntime.setcomponentproperty("accessmode", 0)      srcdesigntime.setcomponentproperty("openrowset", "[dbo].[abs_mstr]")      srcdesigntime.acquireconnections(nothing)      srcdesigntime.reinitializemetadata()      srcdesigntime.releaseconnections()          ' create destination component.      dim destination as idtscomponentmetadata90 = dataflowmainpipe.componentmetadatacollection.new()      destination.componentclassid = "dtsadapter.oledbdestination"      dim destdesigntime as cmanagedcomponentwrapper = destination.instantiate()      destdesigntime.providecomponentproperties()      destination.runtimeconnectioncollection(0).connectionmanager = dtsconvert.toconnectionmanager90(pkg.connections(0))      destination.runtimeconnectioncollection(0).connectionmanagerid = pkg.connections(0).id      destdesigntime.setcomponentproperty("accessmode", 0)      destdesigntime.setcomponentproperty("openrowset", "[dbo].[abs_mstr]")          ' create path source destination.      dim path as idtspath90 = dataflowmainpipe.pathcollection.new()      path.attachpathandpropagatenotifications(source.outputcollection(0), destination.inputcollection(0))          ' destination's default input , virtual input.      dim input as idtsinput90 = destination.inputcollection(0)      dim vinput as idtsvirtualinput90 = input.getvirtualinput          ' iterate through virtual input column collection.      for each vcolumn as idtsvirtualinputcolumn90 in vinput.virtualinputcolumncollection       destdesigntime.setusagetype(input.id, vinput, vcolumn.lineageid, dtsusagetype.ut_readonly)      next          ' save package      app.savetoxml("c:\test.dtsx", pkg, nothing)    

 

 

thanks.

 

regards,

michael

i'm afraid c#, sample @ http://www.sqlis.com/sqlis/post/flat-file-to-sql.aspx illustrates selecting , mapping columns ole-db destination.

select input columns already, haven't mapped them external metadata columns. you'll see 2 steps inside each loop compared yours. ensure external metadata current you'll need connection , call reinitializemetadata first, per code before loop.

any help?

---

http://www.sqlis.com | http://www.konesans.com

SQL Server  >  SQL Server Integration Services