Read Write Variable Cannot access before PostExecute

I have a for each loop on a directory of files, each file has to be imported with a unique surrogate key added. For this I am selecting the max id that exists in the target table and assigning that value to a variable.

Within a script transformation I am copying this value to a variable declared in a script task, and incrementing it on each row processed. Obviously I now want to write this value back out to the higher scoped variable so it is available for the next file.

If I make the variable read write it is not even available for reading until the PostExecute. Is this correct or have I missed something?

To work round this I have created a second higher scoped variable that I can write to in the PostExecute and the other variable is passed in as read only and added another script task to update the variable values.Philip is a colleague of mine and we've just been taking a look at this.

The workaround is to use a multiflatfile adapter because the metadata of the files is identical.

Philip's requirement to be able to read a ReadWriteVariable in PreExecute() is, I feel, a valid one. Is there a reason that this cannot be done?

-Jamie|||You can actually access write-able variables anywhere you want just not the ones on the read/write list. The component has a VariableDispenser that you can lock variables for read and/or write and use them as you will. The limitation we place is only for the ones you specify in the ReadWriteVariables line and this was done to keep locking to a minimum. If we gave access during row processing then because we don't know the usage we would need to keep the variable locked during the entire ProcessInput call. If some other transform needed this variable as well then we have concurrency issues. This way the user can lock variables for write but has to do it explicitly so that it can be unlocked explicitly as well and hopefully the locking time can be kept to a minimum since the script author is controlling the locking.

HTH,
Matt

read varbinary(max)

Hi,
I'm working with visual c++ and usign MFC ( ODBC ) . I have a problem
with varbinary(max) type of sql server 2005 express. I can read a
varbinary (n ) but when i try to read a varbinary(max) fails.
CDBVariant cvarValor;
m_tablaDeConsulta.GetFieldValue ( campo,
cvarValor );
I use this code to read varbinary fields. The only differrence betwen
max a normal varbinary is the precision. When i read a varbinary
precision is 0.
Someone can help me?
thanks.AKS ha escrito:

> Hi,
> I'm working with visual c++ and usign MFC ( ODBC ) . I have a problem
> with varbinary(max) type of sql server 2005 express. I can read a
> varbinary (n ) but when i try to read a varbinary(max) fails.
> CDBVariant cvarValor;
> m_tablaDeConsulta.GetFieldValue ( campo,
> cvarValor );
> I use this code to read varbinary fields. The only differrence betwen
> max a normal varbinary is the precision. When i read a varbinary
> precision is 0.
> Someone can help me?
> thanks.
At least i can read a varbinary. The code
// get data size
void* buffer = malloc ( 1 );
SQLINTEGER tam = 0;
tam = m_tablaDeConsulta.GetData ( m_tablaDeConsulta.m_pDatabase,
m_tablaDeConsulta.m_hstmt, ind+1, SQL_C_BINARY, buffer, 0, SQL_C_BINARY
);
free (buffer );
// alloc data buffer
CDBVariant cvarValor;
buffer = m_tablaDeConsulta.GetDataBuffer( cvarValor, SQL_C_BINARY,
&tam, SQL_C_BINARY, tam);
// read data
m_tablaDeConsulta.GetData ( m_tablaDeConsulta.m_pDatabase,
m_tablaDeConsulta.m_hstmt, ind+1, SQL_C_BINARY, buffer, tam,
SQL_C_BINARY );

Read package variable in script component

For an SCD surrogate generator, I want to read a package variable that I've populated with the MAX () surrogate to begin my surrogate seed at. Does anyone have a piece of code sample to do this?

In your script component assign the variable in the properties of the component either to the readonly list or readWrite List. You can then access the variable in the script component with

Variables.myVariable

Where myVariable is the name of your variable.

Read large binary data from Sql Server 2005

Hi

I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:

SELECT imageData , MIMETypeFROMPicturesWHERE(imageTitle = @.imageTitle)

And then using an .aspx page to Response.Write the data:

Using conn As New sql.SqlConnection
conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString

Dim getLogoCommand As New sql.SqlCommand
getLogoCommand.CommandType = Data.CommandType.StoredProcedure
getLogoCommand.CommandText = "GetPicture"
getLogoCommand.Connection = conn

Dim imageTitleParameter As New sql.SqlParameter("@.imageTitle", Data.SqlDbType.NVarChar, 200)
imageTitleParameter.Value = Request("imageTitle")
imageTitleParameter.Direction = Data.ParameterDirection.Input

getLogoCommand.Parameters.Add(imageTitleParameter)

conn.Open()

Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
logoReader.Read()
If logoReader.HasRows = True Then
Response.Clear()
Response.ContentType = logoReader("MIMEtype").ToString()
Response.BinaryWrite(logoReader("imageData"))
End If

End Using

conn.Close()

End Using

Can anyone please help me with this?!

Even though you are using SQL Server 2005... the "compatibility mode" is set to "80" (meaning 8.0... meaning SQL Server 2000).

So, until you run "EXEC sp_dbcmptlevel 90"... your MAX will == 8000 :) (you'll only have to run this once btw)

|||

Thanks for your reply! I can see <binary data> when I'm just watching the table in SQL Server Studio Manager but still not through a stored procedure. I, however, can get the data with:

SELECT * FROM Pictures WHERE (imageTitle = @.imagetitle ) as a "text" command on the .aspx page.

So like that i'm able to actually show anything bigger then 8 kB on the page, but now I can't do anything with the stored procedure like, when there is no data for that imageTitle I can't make it return another value.

When I use

---

conn.Open()

Using pictureReader As sql.SqlDataReader = getLogoCommand.ExecuteReader
pictureReader.Read()
If pictureReader.HasRows = True Then
Response.Clear()
Response.ContentType = pictureReader("MIMEtype").ToString()
Response.BinaryWrite(pictureReader("imageData"))
End If

End Using

---

The pictureReader.HasRows = True even when there is no binary data. Is there anyway to get the page not to do anything when there is no data in a row?

Thanks