Manualy Create IDENTITY Column inside ControlFlow

Dear Friends... I'm having a problem...

I want to manually create the identity column for a table...

I have some dataflws, and in each dataflow I insert values in this table...

I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.

And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...

Until this step there is no problem... the problem starts here...:

I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......

D_INST_IDENTITY = D_INST_IDENTITY + COUNTER

How can I do it? I have a RowCount transform next the script component, but generates errors...

What do you think I can do it?
Thanks!!

Can you not just use an Execute SQL Task to get the current max value in the target table and store is in a variable?

-Jamie

|||

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

|||

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

|||

PedroCGD wrote:

I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...

You don't do it IN the data-flow, you do it BEFORE the data-flow

PedroCGD wrote:

Example

1. Dataflow

Return the Initial value of IDENTITY_variable from SQL Task

Refresh Identity_variable

2. Dataflow

Read Identity_variable

Refresh Identity_variable

3. Dataflow

Read Identity_variable

Refresh Identity_variable

So you put an Execute SQL Task before each data-flow. Is there a problem with doing that?

(There are actually some cleverer ways of doing it but for now - let's keep it simple.)

-Jamie

|||

PedroCGD wrote:

And In the SQL Task I use:

SELECT MAX(INST_ID)+1 FROM Instrumento

But if teh table is empty returns me an error... Is this statment that is usually used?

It really helps if, when you get an error, you tell us the error emssage.

I'm pretty sure I can guess what it is though. Try this:

SELECT ISNULL(MAX(INST_ID), 0) +1 FROM Instrumento

-Jamie

|||

Dear Jamie,

In order I have teh best performance, I avoid to use multiple SQL tasks. Imagin that I have 20 dataflows? Why I need to execute a query in database for each dataflow, if I have a counter to automatically give me the IDENTITY column? Do you think is more consistent using SQL Task?

I have found the solution to read a global variavel, and change it inside the script component transform...

I initialize in the Script Component properties the variable INST_IDENTITY as ReadWritevariables, and in order to avoid errors inside the PreExecute method when I read the value, I changed the code as you can see:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim counter As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

counter = counter + 1

Row.TesteKey = counter

End Sub

Public Overrides Sub PreExecute()

Dim vars As IDTSVariables90 = Nothing

Me.VariableDispenser.LockForRead("User::INST_IDENTITY")

Me.VariableDispenser.GetVariables(vars)

counter = CType(vars(0).Value, Integer)

vars.Unlock()

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("INST_IDENTITY").Value = counter

MyBase.PostExecute()

End Sub

Protected Overrides Sub Finalize()

MyBase.Finalize()

End Sub

End Class

I was having errors if I read the value of a ReadWriteVariables inside the PreExcute Method, so, using VariableDispenser I can do it!!

Thanks!!

|||

PedroCGD wrote:

I have found the solution to read a global variavel, and change it inside the script component transform...

Cool! That is the "other way" that I alluded to earlier.

-Jamie

Manipulating SQL server databases dynamically

Hi friends,

I have problem in sending my T-SQL statements, which i generate dynamically with the help of "user entered attributes", to SQl Server.

I need my T-SQL statements to get passed to the SQl Server ,which i enter in a "Rich text Box " in my appication that i develop using Vc# .net 2005,when i click a button which i placed in my "winform".And the queries Should be executed exectly as it is, and i should get the output back in my Winform...

But,Make sure that the queries are being obtained from a rich text box placed in the Winform...

So, please assist me regarding this issue...

R.Rajaraman

Hi,

Here is an example:

string query = txt.Text;

string connStr = "";//your connection string..

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);

System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = query;

cmd.CommandType = CommandType.Text;

System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

//continue..

Another option is to use SqlDataAdapter that will fill a dataset. If you need help with that let me know.

Regards,

|||

hi

i found your answer very usefull .....

But ,i need the queries directly to be passed from my rich text box control that i use in my winform....

please help me with this issue...

|||

Hi,

Perhaps I don't undestand what you mean. What do you mean directly?

If I got it right it is when you click the button that the query should get executed.

If so you should add the sample i sumbitted to the onclick event.

Regards,

|||

hi,

Thanks for the help guy... actually what i want to be done is as follows..

"I am going to enter some T-SQL statements in my Rich text Box that i placed in my form.And, i have a button named "Execute" in my win form. If I click on that button, the entire set of statements placed in the rich text box should get passed to SQL Server Query analyzer and my query batch should get executed.And ,i have to get back the results in my winform itself back..."

In Short, i need the functionality of a "Query Analyzer"..

Could you please help me with this issue......

|||

Hi,

First let me say that way you execute a query in the query analyzer you get for each statement (select/insert and ect.) a table. You can look at it as dataSet.

So, you need to fill a dataset with the query result and display it. In order to do that you first need to use the SqlDataAdapter and set the commands for it. Then you can use the fill method in the adapter to fill the result. An other option that you can try is using the data application block (or enterprise libraries) and you will get a method ExecuteDataSet(...).

Let me know if that helps.

Regards,

|||

This thread was moved to this forum (SQL Server Data Access) as the topic is more relevant here. The forum where it originally was posted (.NET Framewotk Inside SQL Server) deals with writing and running .NET code inside SQL Server (stored procs, funtions etc).

Niels