在ssis中，用脚本任务动态创建数据流。

 在ssis中，要使用开发环境提供的数据流组件的话，源和目标必须是指定的，必须为二者建立映射关系，而这种映射关系不可动态创建，因此，如果用户要导出的数据不确定的话，则实现导出非常困难。

可以用脚本任务编写动态的数据流组件，动态创建映射关系。

以下是代码vb.net：

Try
            ' Create a package and add a Data Flow task.
            Dim package As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()
            package.Name = "From" & _
                        Replace(Replace(Replace(Dts.Variables("SourceTable").Value.ToString, ".", ""), "]", ""), "[", "") & "To" & _
                        Replace(Replace(Replace(Dts.Variables("DestinationTable").Value.ToString, ".", ""), "]", ""), "[", "")
            package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive
            Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
            Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            thMainPipe.Name = "DFTExtractData"
            Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
            ' Add an OLE DB connection manager to the package to manage the Source database connection.
            Dim conMgrSource As ConnectionManager = package.Connections.Add("OLEDB")
            conMgrSource.ConnectionString = "Data Source=" & Dts.Variables("SourceServer").Value.ToString & ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
            '默认HDR=YES
            'conMgrSource.ConnectionString = "Data Source=C:/Documents and Settings/gxr/桌面/ssis.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=excel 8.0;"
            'conMgrSource.ConnectionString = "Data Source=C:/Documents and Settings/gxr/桌面/ssis.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
            conMgrSource.Name = "OLESource"
            conMgrSource.Description = "OLE DB connection to the " & Dts.Variables("SourceDB").Value.ToString & " database."
            ' Create and configure an OLE DB source component. 
            Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
            source.ComponentClassID = "DTSAdapter.OleDbSource.1"
            ' Create the design-time instance of the source.
            Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
            ' The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties()
            ' Specify the connection manager.
            If source.RuntimeConnectionCollection.Count > 0 Then
                source.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLESource").ID()
                source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLESource"))
            End If
            ' Set the custom properties of the source.
            'srcDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("SourceTable").Value.ToString)
            'srcDesignTime.SetComponentProperty("AccessMode", 0)
            'Dts.Variables("sql").Value = "select name,sex from [Sheet1$]"
            srcDesignTime.SetComponentProperty("AccessMode", 2)
            srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("sql").Value.ToString())

            ' Connect to the OLESource data source,
            '  and then update the metadata for the source.
            srcDesignTime.AcquireConnections(Nothing)
            srcDesignTime.ReinitializeMetaData()
            srcDesignTime.ReleaseConnections()
            ' Add an OLE DB connection manager to the package to manage the Destination database connection.
            Dim conMgrDest As ConnectionManager = package.Connections.Add("OLEDB")
            conMgrDest.ConnectionString = "Provider=SQLOLEDB.1;" & _
              "Data Source=" & Dts.Variables("DestinationServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("DestinationDB").Value.ToString & ";Integrated Security=SSPI;"
            conMgrDest.Name = "OLEDestination"
            conMgrDest.Description = "OLE DB connection to the " & Dts.Variables("DestinationDB").Value.ToString & " database."

            ' Create and configure an OLE DB destination.
            Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
            destination.ComponentClassID = "DTSAdapter.OleDbDestination.1"

            ' Create the design-time instance of the destination.
            Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

            ' The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties()
            ' Specify the connection manager.
            If destination.RuntimeConnectionCollection.Count > 0 Then
                destination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLEDestination").ID()
                destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLEDestination"))
            End If
            ' MsgBox(Dts.Variables("DestinationTable").Value.ToString)
            ' Set the custom properties of the source.
            destDesignTime.SetComponentProperty("AccessMode", 0)
            destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("DestinationTable").Value.ToString)

            'reinitialize the component
            destDesignTime.AcquireConnections(Nothing)
            destDesignTime.ReinitializeMetaData()
            destDesignTime.ReleaseConnections()
            'map the columns
            Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
            path.AttachPathAndPropagateNotifications(source.OutputCollection(0), destination.InputCollection(0))
            Dim input As IDTSInput90 = destination.InputCollection(0)
            Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
            Dim vColumn As IDTSVirtualInputColumn90
            For Each vColumn In vInput.VirtualInputColumnCollection
                Dim vCol As IDTSInputColumn90 = destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
                Dim exCol As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection(vColumn.Name)
                destDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
            Next

            Dim app As Application = New Application()
            Dim strPath As System.String
            strPath = Dts.Variables("ChildPackagePath").Value.ToString & package.Name.ToString & ".dtsx"
            app.SaveToXml(strPath, package, Nothing)

            'Set the value for the return variable to the name of the new package
            Dts.Variables("ChildPackageName").Value = package.Name.ToString & ".dtsx"

            'Run package
            package.Execute()

            'Send success result if no exceptions are raised
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            'Send failure result if exceptions are raised
            Dts.TaskResult = Dts.Results.Failure
        End Try

这个代码是由excel导入到sqlserver中，其他数据库都类似，不过源文件和目标数据库的字段必须相同，否则还是不行。