Script task: Bindingsource

I used a binding source in my script task codes to filter the data table. when i used that, even if i declare it (dim bs as bindingsource), i'm still having an error - "type bindingsource is not defined".

cherrie

Hi, bindingsource is part of the forms name space. Does your code include

Imports System.Windows.Forms

Script Component: Keyword not supported: ''provider''

Hi,

I have a script component that accesses the database with this codes:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim cn As SqlConnection

Dim SQLCmd As SqlCommand

Dim dr As SqlDataReader

Dim NextKey As Integer = 0

cn = New SqlConnection(Connections.CONNofficetestdb.ConnectionString.ToString)

cn.Open()

SQLCmd = New SqlCommand("select count(*) from accounttrans", cn)

dr = SQLCmd.ExecuteReader

While dr.Read()

NextKey = NextKey + 1

End While

End Sub

But I encounter a problem once I ran it:

Keyword not supported: 'provider'

How do I resolve this?

thanks a lot.

cherriesh

Since you are using the SQL Server connection object, you should not have a Provider defined in your connection string. You only have to supply the Provider in the connection string when you are using OLEDB.

Script Component as Source

I want to use Script Component as Source, but I don't know how to code the output rows. Someone can give me some clue or some sample codes? Thanks in advance.

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

|||Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

|||

jwelch wrote:

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

Sort of... He's doing some other stuff in there than a basic script component source...

For a basic sample:
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/|||

SSIS_NewMan wrote:

Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

The control flow has a transfer objects task. Does that not work for you?|||But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.
|||

SSIS_NewMan wrote:

But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.

Well, you should be using the OLE DB source and destinations, not scripts. But either way you have a big problem. That is, using one data flow to handle this won't likely work too well unless all of your source tables are exactly the same format and are going to tables that have exactly the same format (among ALL of them)

If SourceTableA has 3 columns, all varchars, and SourceTableB has 4 columns, all integers, SSIS will not be able to handle that.

Changing metadata is not within the scope of SSIS' ability to handle.|||Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.
|||Hi Phil Brammer,
Could you give me some advice about this problem?

Thanks,
|||

SSIS_NewMan wrote:

If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table?

I am afraid the short answer is YES. There have been some success reported on this forum about building the packages pragmatically. You may want to research on that

|||

SSIS_NewMan wrote:

Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.

To echo Rafael, if you're performing data checks/edits when transferring the data, then yes, I believe your best option is to build a data flow for each source table.|||Thanks for your reply. I will try to get the best solution.