Asp.net / sql help: input

Showing posts with label input. Show all posts

Thursday, March 22, 2012

Asynchronus Transformation Component

Hi all,

I am missing something simple. I have added a new Transformation Script, put in my code to read the input rows, defined my outputs. I have tried to change the SynchonousInputId to 0, but I only get the option of None or input "Input 0" (91). What have I missed?

Set the SynchonousInputId to None.

The SynchonousInputId of "None" is synonymous with zero (0). The script transformation editor dropdown for the SynchonousInputId changed between service packs between saying "0" in the earlier case, to "None" in subsequent service packs. Both 0 and "None" for the SynchonousInputId property mean, "this is a async script transform".

Asynchronous Outputs on Script Component Best practice

If you have an output that is not synchronous with the input what is the best way of processing the data.

I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.

The CreateNewOutputRows Dequeues the class instances and creates buffer rows.

Is there a better solution?

ArrayList? I've seen asynch components that cache data in an ArrayList.

-Jamie

|||The problem is having two threads, one putting data into a container and one taking it off. I think the queue is the best solution.

Tuesday, March 20, 2012

Associating Data from Other Tables

Morning, all.

I have an input record that contains the following fields:

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

And I have a table, that contains the following fields

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

Okay. The kicker here is that in the table, the header_id's are already defined, but the details are blank. I need to insert the details where the header_id's of the input equal the header_id's of the table.

Is there a loop or something? I can *almost* see it in my head if I was just writing a SQL Query, but it involves a cursor, and I'm hoping SSIS will save me some of the aggravation...

Any ideas?
Isn't this just an update?

UPDATE TABLE
SET header_detail_1 = input_header_detail1,
header_detail_2 = input_header_detail2
WHERE header_id_1 = input_header_id_1
AND header_id_2 = input_header_id_2
AND header_id_3 = input_header_id_3

You could achieve this with an OLE DB Command transformation.|||Oh, hey! Yeah! Thanks!

Jim Work
|||You could also load the flat file (or whatever source you may have) to a staging table and then issue a batch update -- which would be faster than using the OLE DB Command to perform your updates...

To perform the batch update inside SSIS, you'd use an Execute SQL task in the control flow after you've loaded the staging table with a data flow.

Inside that Execute SQL task, your query would be something like:

UPDATE destination_table a, staging_table b
SET a.column1 = b.column1, a.column2 = b.column2
WHERE a.key1 = b.key1
AND a.key2 = b.key2
AND a.key3 = b.key3

It's real similar to the above query, except that you're using native SQL to perform the update rather than SSIS.

Monday, March 19, 2012

Assistance with Stored Procedure

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @.IDNum

I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@.nc.ngb.army.mil.

Thanks in advance.

TodTake a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod|||Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:3f803253$0$195$75868355@.news.frii.net...
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that
have
> the same sort of information?
> Thanks for the response,
> Tod
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||[posted and mailed]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to_table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_table_sp @.list ntext AS

DECLARE @.pos int,
@.textpos int,
@.listpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @.textpos = 1, @.listpos = 1, @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SELECT @.chunklen = 4000 - datalength(@.leftover) / 2
SELECT @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos, @.chunklen))
SELECT @.textpos = @.textpos + @.chunklen

SELECT @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SELECT @.str = rtrim(ltrim(substring(@.tmpstr, 1, @.pos - 1)))
EXEC insert_str_to_number @.str, @.listpos
SELECT @.listpos = @.listpos + 1
SELECT @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SELECT @.pos = charindex(' ', @.tmpstr)
END

SELECT @.leftover = @.tmpstr
END

IF ltrim(rtrim(@.leftover)) <> ''
EXEC insert_str_to_number @.leftover, @.listpos
go

-- This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @.str nvarchar(200),
@.listpos int AS

DECLARE @.number int,
@.orgstr nvarchar(200),
@.sign smallint,
@.decimal decimal(10, 0)

SELECT @.orgstr = @.str

IF substring(@.str, 1, 1) IN ('-', '+')
BEGIN
SELECT @.sign = CASE substring(@.str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @.str = substring(@.str, 2, len(@.str))
END
ELSE
SELECT @.sign = 1

IF @.str LIKE '%[0-9]%' AND @.str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@.str) <= 9
SELECT @.number = convert(int, @.str)
ELSE IF len(@.str) = 10
BEGIN
SELECT @.decimal = convert(decimal(10, 0), @.str)
IF @.decimal <= convert(int, 0x7FFFFFFF)
SELECT @.number = @.decimal
END
END

IF @.number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@.listpos, @.sign * @.number)
ELSE
RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
10, -1, @.listpos, @.orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_names_iterproc @.ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @.ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_number. For many purposes it would be sufficient to have
the test

@.str NOT LIKE '%[^0-9]%' AND len(@.str) BETWEEN 1 AND 9

which checks that @.str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See if the following link helps..
http://tinyurl.com/6iil

--
-- Anith|||Tod,

Try this:

create procedure ListEmployees
@.IDNum char(1024)
as begin
set @.IDNum = ' ' + replace(@.IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @.IDNum like ('% ' + ltrim(str(IDNumber)) + ' %')
end

Shervin

Wednesday, March 7, 2012

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun