I have created a SSIS package transfering tables from on DB to another. The problem is that i can transfer 297 tables. If i try to transfer 298 it fails What I do is that I in a Execute SQL Task SELECT TOP 297 [table_name] -- or 298 FROM INFORMATION_SCHEMA.TABLES (The reason why i want to pass it as a tables collection is that in the final version i do not want to transfer all tables in the source DB) and pass it to a script task using the following code. The Assembly i use is Microsoft.SqlServer.TransferSqlServerObjectsTask.dll version 10.0.1600.22 Has anybody experienced anything similar? Has knowledge on if it is a version specific issue? or already has a simple workaround that i can implement. Guess I can do the last part myself splitting the transfer in chunks of about 200 tables. But there might be a more beautiful solution out there And splitting might introduce another problem on handling ForeignKey contraints but i haven't tested that yet Thanks in advance /M Dim pkg As Package = New Package() Dim SourceServer As String = Dts.Variables("SourceServer").Value.ToString Dim TargetServer As String = Dts.Variables("TargetServer").Value.ToString Dim SourceDatabase As String = Dts.Variables("SourceDatabase").Value.ToString Dim TargetDatabase As String = Dts.Variables("TargetDatabase").Value.ToString ' Add a ConnectionManager to the Connections collection. Dim cmSource As ConnectionManager = pkg.Connections.Add("SMOServer") cmSource.Name = "SMOSourceServer" cmSource.ConnectionString = "SqlServerName=" + SourceServer + ";UseWindowsAuthentication=True;UserName=;" Dim cmTarget As ConnectionManager = pkg.Connections.Add("SMOServer") cmTarget.Name = "SMOTargetServer" cmTarget.ConnectionString = "SqlServerName=" + TargetServer + ";UseWindowsAuthentication=True;UserName=;" 'cmTarget. 'create sql server object task to move tables Dim xfr As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask") Dim xfrTask As TaskHost = CType(xfr, TaskHost) xfrTask.Properties("CopyData").SetValue(xfrTask, True) xfrTask.Properties("CopySchema").SetValue(xfrTask, True) xfrTask.Properties("ExistingData").SetValue(xfrTask, ExistingData.Replace) xfrTask.Properties("CopyAllTables").SetValue(xfrTask, False) Dim oleDA As New OleDbDataAdapter Dim Tables As StringCollection = New StringCollection() Dim dt As New DataTable Dim row As DataRow oleDA.Fill(dt, Dts.Variables("TableList").Value) 'create a stringcollection of tables For Each row In dt.Rows Tables.Add(row(0).ToString) 'MsgBox(row(0).ToString) Next xfrTask.Properties("TablesList").SetValue(xfrTask, Tables) xfrTask.Properties("SourceConnection").SetValue(xfrTask, cmSource.Name) xfrTask.Properties("SourceDatabase").SetValue(xfrTask, SourceDatabase) xfrTask.Properties("DestinationConnection").SetValue(xfrTask, cmTarget.Name) xfrTask.Properties("DestinationDatabase").SetValue(xfrTask, TargetDatabase) xfrTask.Properties("CopyPrimaryKeys").SetValue(xfrTask, True) xfrTask.Properties("CopyIndexes").SetValue(xfrTask, True) xfrTask.Properties("CopyTriggers").SetValue(xfrTask, True) 'Execute the transfer Dts.TaskResult = pkg.Execute()

What is the error you get when it fails?Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

Currently i have not errorhandler in the script task. But i just found out that it was me being plain stupid. I left out an important filter in SELECT [table_name] FROM INFORMATION_SCHEMA.TABLES I was selecting VIEWs as well .... so adding TABLE_TYPE = 'BASE TABLE' did the trick No bug in the tables collection.