Iterating the rows of a script component

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Any particular reason you aren't posting in your other thread on this topic? This is a dup.|||

No particular reason. I just thought I wasn't specific about my problem. I didn't know how to modify the title. I am in pressure to finish off a task and didn't take time to explore how i could modify the title of the post.

Thanks.

|||

Sam_res03 wrote:

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

Probably the best way is to push the data into recordset destination and then loop over them with a ForEach loop.

Hopefully this will help:

Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx)

In this example the recordset is populated by an Execute SQL Task rather than a recordsset destinaiton, but teh principle thereafter is the same I think.

-Jamie

|||

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

|||

Sam_res03 wrote:

Jamie,

Thanks a lot for your reply. I think I am almost there...So I have one one column in the recordset. Is this what I should do?

By using a for each loop container, with foreach ado enumerator I can get the id and store in a variable.

Next can I have a dataflow task in the foreach loop...

and in that dataflow task, can I access this id pass it as a variable to an sql command (in the ole db source). The sql command is a select statement with a where clause.I am unable to figure out how that can be done.

I really appreciate your response....

Thanks a lot!!

You're right so far. You have two options to use your value in your SQL statement:

1) Use '?' parameters

2) Use expressions. Some info here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

-Jamie

|||

Hi Jamie,

I took your approach and I get this error..

Error: ForEach Variable Mapping number 1 to variable cannot be applied.

I tried to use a foreach loop container. I mapped index 0 to a user variable of type integer. IN the script component I have the row object cast as an integer.

I also get this error..

Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Do you see what mistake I am doing? Can you please reply?

Thanks.

|||

What's the SQL statement? What's the type of the first column?

-Jamie

|||

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

|||

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.|||

Jon Limjap wrote:

Sam_res03 wrote:

Type of the first column is Integer.

For some reason, if I try to map it to a variable of type integer, it gives me an error. But if I try to map it to a variable of type object it doesn't complain. And with the help of msgbox scrript component i am able to see the ids. But the problem I am getting to is..if the variable is of type object, how can I create an expression with that object (in the where clause)and use it as a variable for sql command with variable.

Are you getting my problem? Please reply.

Thanks a lot for bearing with me.

Hi Sam,

You can set up another variable which uses expressions that casts the object variable to an integer variable (e.g., the expression would look like "<DT_I4> @.[User::objvariable]" for an 4-byte signed integer which maps to the standard Int32 integer). Then you can use that in your where clause.

I do have a question... what is the exact type of your integer column? If it's bigint, it really won't work with a standard Integer type... and you have to use <DT_I8> instead.

Yeah, Jon is right. If I were you I would investigate why you can't store it in an Integer variable. Try Int16, Int32 and Int64.

-Jamie

|||

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Thanks.

|||

Thanks Jon. I really appreciate your response. I will try your trick and will let you know soon.

|||

Sam_res03 wrote:

Thanks Jamie. I will investigate it. By the way, if I have a dataflow task(source and the destination) in a foreach loop container and if the number of items it is iterating is x items , does it mean that it will make x calls to the source database?

Yes.

|||

Then that is not an efficient way either making 1200 calls. The only reason i tried to do this was, I didn't want to scan a table with 600k rows and do a look up in a temp table for the needed 1200 rows. I wanted to scan only for these 1200 rows/ids. Is there a way to achieve this? If I have the ids in an object, would i be able to concatenate those ids into a string varible and use that sring variable in a where clause of a select statement? I am getting this data from an Oracle DB. Is there any limitation on using a where clause in a select statement. If so, what is the other to solve this problem?

Thanks Jamie. I really appreciate your response. Thanks a lot!!

Iterate result set inside stored procedure

Hello,

I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).

How can I process result rows inside a stored procedure?

This is what I have so far:

CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)
/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID
/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)
PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)
/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO

Hi,
You can declare a variable and append the results onto it for each row with:

DECLARE @.emails varchar(1000)

SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx

This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"

The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:

SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery

|||

Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?

Also, I get a leading comma - how do I avoid that on the first entry?

Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"

|||

I got it! Needed to assign a derived table.

Still have the comma issue, though.

/* Get email addresses of Service Responsible Parties */
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1

|||

The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''

The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email

if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.

As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)

DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail

FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
 ) subquery

|||

Just seen your post after I did one!
Excellent news that you got it working.

The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with

SET @.emails = 'recip: ' + @.emails

|||

Instead of

SELECT @.email=ISNULL(@.email,'') + ',' + {your field}

use

SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}

|||

I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).

Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail

And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.

Either way, thanks a lot for the help - it's good to go now!

IT WORKED!

Hello everyone,

i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.

Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?

thanks a lot.

Have you tried using Data Conversion transform...

you could use an expression like

IsNull(Column) ? ValueifNull : ValueifNotNull

See some examples here:

http://msdn2.microsoft.com/en-us/library/ms141184.aspx

|||Thanks a lot, the code and the article helped a lot.

I used an Derived Column Transformation to parse null values to zeros with the expression. The good thing is that i don't loose the mapping to the old columns. that's great.

Additionally this article helps to understand Derived Column Transformation:
http://msdn2.microsoft.com/en-us/library/ms141069.aspx

regards amigo|||

So, you got it!

That's great.

Issues with removing replication

SQL Server 2005 Standard replicating to an identical server.

My issue:

When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.

The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.

Please Help!

Can this be caused by a long running transaction which is not completed yet?

This posting is provided AS IS with no warranties, and confers no rights.

|||i don't think so since i tried deleting a single row and it sat there for 16 minutes. i had to kill it since no one could connect to the database. but before this situation deleting a couple hundred rows would take about 1 second.|||

Can you try to connect to "master" using 'sa' and do sp_who (sp_who2) and see "who" is accessing that database?

This posting is provided AS IS with no warranties, and confers no rights.

|||i did
USE master
EXEC sp_who

i returned 37 processes. i don't know how to display it on the forum.|||

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

|||If you want to remove replication, and you have limited resources on your box, try stopping sql server agent service first, then do the uninstall. Depending on how many rows you have in MSrepl_commands, it may take a while.|||

Before you post the result from sp_who2, can you check the "blkby" value where "DBName" is equal to the target database? I just try to understand if any process to that database is blocked by some other processes?

This posting is provided AS IS with no warranties, and confers no rights.

|||

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

|||

Greg Y wrote:

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

It

doesn't matter which command I try. And even if I try to delete

something from the GUI it fails. It's not limited to data either. If I

try to delete an object, or try to disable replication I get the same

result; SQL lockup.

|||

Madhu K Nair wrote:

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

I've tried this. SQL locks up. This only happens on 1 server. I've tried uninstalling and re-installing sql server 2005. i still get the same result.|||

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

|||

Greg Y wrote:

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

i'm actually unable to try stopping the service until this weekend. it's our main production server so i can't interrupt service. the only hardware constraints we may run into is HDD read/write. we only have a pair of 15K SCSI drives in RAID1. we plan on upgrading to 8 drives in RAID10. but other than that the servers are 4 way Intel dual cores with 8 physical cores and 8 logical cores, and 16GB of RAM.

It's transactional replication with a single publication. i'm not sure how to find the number of rows in the locations you asked for though?|||

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

|||

Greg Y wrote:

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

We've done performance monitoring analysis for other reasons and the disks weren't even a bottleneck. They are the only part of the system that was really stressed, but no issues. We even had a consulting firm come in and spend a couple months scripting out a load test which went really well.

Is it possible that corrupt data in the database could be causing this issue?

Issue selecting two rows from two columns

I'm trying to select two rows from two columns and while I can select the columns, the selection of multiple rows is not falling into place. I can pull one row into the report, but not two.

This is what I'm writing

SELECT name, number FROM table
WHERE name="variable1',''variable2'

Something is occurring around the , between variable1 and variable2. I know I doing something wrong, but can't figure it out. I can pull variable1 by itself just fine.

The specific error is:
sql: errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'variable2' at line 2
number: 1064

Any ideas would be greatly appreciated. I tried searching and could not find an answer to this problem.Found the answer

SELECT name, number FROM table
WHERE name IN('var1', 'var2', 'var3')|||SELECT name, number FROM table
WHERE name in('variable1','variable2')

Issue Loading Data from Flat File

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

Philip Coupar wrote:

I have come across a strange bug that I can't get to the bottom of.

I have a source Excel spreadsheet with approx 30000 rows in it, I have saved this out using Excel 2003 as a csv file which contains all of the source rows.

Using a Flat File Source I am only getting approx 15000 rows through, I have re-saved the spreadsheet as csv and it stops at exactly the same point. However, if I use the excel spreadsheet as the source I get all 30000 rows.

I have checked the CSV at the relevant point and can see no reason why it would stop at that particular point.

I am experiencing a similar problem with another source file handled in exactly the same way.

Has anyone else experienced this?

Philip

UPDATE: looks like this could be a problem in Excel, Used a Excel Data source and a Flat File Destination to create the CSV, and my original Data flow now uses all the data

I'm working with Philip on this. Basically it looks as though there's problem in the way that Excel saves stuff to csv. Anyone know why?

-Jamie

|||

We are experiencing the same exact problem. The CSV file imports fine using our old DTS Packages just not with the new SSIS packages we have created. I can provide sample csv files if needed.

If you have an update on this, please let me know.

|||Did you see the workaround that Philip posted (above)?

-Jamie