Scripted delete rows

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated."rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1114613428.538202.213970@.f14g2000cwb.googlegr oups.com...
> Hi,
> I need to delete rows from my user tables dependant upon there non
> existence from another table:
> delete student
> where student_id not in (select student_id from tblStudent)
> The reasons is convoluted, simplest explanation is that our operational
> system allows the change of business keys. This wreaks havoc in the
> data warehouse.
> So, I'm look for help on how I can delete rows from tables that have a
> column STUDENT_ID. I'd like the script to search for the tables, then
> perform the delete.
> I don't know where information about user tables are stored, nor how to
> loop through the results to do the delete.
> Any Ideas are appreciated.

You can use a query like this to generate a script, then review it before
executing it:

select 'delete from ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
' where not exists (select student_id from dbo.tblStudent ts where ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
'.student_id = ts.student_id)'
from
INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME = 'student_id' and
objectproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsTable') = 1

See the INFORMATION_SCHEMA views in Books Online, as well as syscolumns,
sysobjects, and "Meta Data Functions".

Simon|||Simon,
Works like a champ and I learned something new!
Thanks
Robsql

Script Transformation

A new question !

When you use a script transformation you have input rows and output rows.

Is it possible to have a different number of rows at input and output ?

Thanks

i found something about it

http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsref9/html/1c3e92c7-a4fa-4fdd-b9ca-ac3069536274.asp|||That's the idea. An asynch component, you need to explicitly add rows to the output buffer, compared to a synchronous transform where you just redirect the input row to the output one for one.|||Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.|||

Coroebus wrote:

Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.

Coroebus,

This is what you want: http://www.sqldts.com/default.aspx?307

-Jamie|||Thanks a lot one more time !|||Please note that Books Online does include a simple sample of each of the 4 types of Script components - source, destination, transformation with synchronous and with asynchronous outputs -- in the section titled "Examples of Specific Types of Script Components ."

-Doug
|||I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

|||

Coroebus wrote:

I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

Why are you calling Row.NextRow()? I stand to be corrected but if this is in the Input0_ProcessInputRow() method then you don't need to do this because that method gets called for every input row.

-Jamie

script to combine multiple rows into a single row

Hi everyone,

I really appreciate if anyone could help me with this tricky problem
that I'm having. I'm looking for a sample script to combine data in
multiple rows into one row. I'm using sqlserver. This is how data is
stored in the table.

ID Color
111 Blue
111 Yellow
111 Pink
111 Green

This is the result that I would like to have.

ID Color
111 Blue, Yellow, Pink, Green

There is no definite number of colors per ID. I have to use ID to
group these colors into one row. Therefore, ID becomes a unique key
in the table.

Appreciate your help and time. Thank you in advancelie_valerie@.yahoo.com (Valerie) wrote in message news:<f4825946.0308141505.4c29ff89@.posting.google.com>...
> Hi everyone,
> I really appreciate if anyone could help me with this tricky problem
> that I'm having. I'm looking for a sample script to combine data in
> multiple rows into one row. I'm using sqlserver. This is how data is
> stored in the table.
> ID Color
> 111 Blue
> 111 Yellow
> 111 Pink
> 111 Green
> This is the result that I would like to have.
> ID Color
> 111 Blue, Yellow, Pink, Green
> There is no definite number of colors per ID. I have to use ID to
> group these colors into one row. Therefore, ID becomes a unique key
> in the table.
> Appreciate your help and time. Thank you in advance

The short answer is that you should consider doing this in your client
application. For a longer answer, have a look at the thread "newbie
sql query question" in this group from a day or two ago.

Simonsql

Script to combine multiple rows into 1 single row

Hi,

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:

Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete

I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete

There is no definite number of materials per Company.

I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'The
Iterative Method' but my knowledge of SQL is very limited and I don't know
how to use this code to get what I need.

Can anyone help me?Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:
>
Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete
>
I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete
>
There is no definite number of materials per Company.
>
I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
'The Iterative Method' but my knowledge of SQL is very limited and I
don't know how to use this code to get what I need.

And that article covers the opposite process - unpacking the list.

Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.

Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:

SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a

o What is the datatype of Material? That is, is varchar or nvarchar?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

I hope it's not to late to get help on this one!

Here are the answers you are looking for:

1) I'm using SQL 2000
2) 40
3) nvarchar

To clarify the field names, it is 'material_name' instead of 'material' and
is 'to_company' instead of 'company_id'

Thanks!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98A19BE4CF73BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I'm working on a system migration and I need to combine data from
>multiple
>rows (with the same ID) into one comma separated string. This is how the
>data is at the moment:
>>
>Company_ID Material
>0x00C00000000053B86 Lead
>0x00C00000000053B86 Sulphur
>0x00C00000000053B86 Concrete
>>
>I need it in the following format:
>Company_ID Material
>0x00C00000000053B86 Lead, Sulphur, Concrete
>>
>There is no definite number of materials per Company.
>>
>I have read the part of
>http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
>'The Iterative Method' but my knowledge of SQL is very limited and I
>don't know how to use this code to get what I need.

>
And that article covers the opposite process - unpacking the list.
>
Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.
>
Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:
>
SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a
>
o What is the datatype of Material? That is, is varchar or nvarchar?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I hope it's not to late to get help on this one!
>
Here are the answers you are looking for:
>
1) I'm using SQL 2000
2) 40
3) nvarchar

I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.

Here is an example of a query that runs in Northwind. First run:

select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s

(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:

SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

If your maximum number is 8, you will need to add one more line.

Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.

If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products]. I tried
modifying the script but it doesn't work:

SELECT to_company,
MAX(CASE OD.rowno WHEN 1 THEN Material_Name END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + Material_Name END), '')
FROM Material__Bridge AS OD
GROUP BY OD.to_company
ORDER BY OD.to_company

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

Any ideas?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AED9C3878CYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I hope it's not to late to get help on this one!
>>
>Here are the answers you are looking for:
>>
>1) I'm using SQL 2000
>2) 40
>3) nvarchar

>
I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.
>
Here is an example of a query that runs in Northwind. First run:
>
select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s
>
(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:
>
SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID
>
If your maximum number is 8, you will need to add one more line.
>
Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.
>
If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products].

I could have done the script with product ids instead of product names
but that seemed boring.

Quote:

Originally Posted by

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.

It might be that you want to be spoon-fed a solution, but I have this funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

I totally agree with not being spoon fed! I'm sorry I came across as wanting
to be. I'll try and work out what your script is doing :o) Thanks for your
help!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AF7D09BCD42Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>Thanks for the script. The difference between the Northwind database and
>mine is that all the data I want to get access to is in one table (unlike
>Northwind where it is spread over [order details[ and [products].

>
I could have done the script with product ids instead of product names
but that seemed boring.
>

Quote:

Originally Posted by

>It says there is an invalid column name 'rowno' - I guess this is right
>because there is no column with that name in my database! However, when I
>check in Northwind, there isn't one called that there either!

>
The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.
>
It might be that you want to be spoon-fed a solution, but I have this
funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to
understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I totally agree with not being spoon fed! I'm sorry I came across as
wanting to be. I'll try and work out what your script is doing :o)

There is one thing I should have pointed out. In my query there was this
part:

(SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD

That is a *derived table*. A derived table is logically a temp table in
the query so to speak, but not materialised, and the actually computation
order can be different as long as the result is the same. Derived tables
is an enormously powerful tool to build complex queries with, and saves
you from using real temp tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script that Return the number of Rows for each Table on a DB

I Have 2 separate data bases with the same Tables and records, I create
a batch to synchronize my data every 2 hours but now I need a Script
that Return the number of Rows for each Table on each DB.
Can someone give me an Idea or the solution for this, I will really
appreciate it.This is what the code I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN ('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||This is what I have, now I need to insert the total rows per
table
IF OBJECT_ID('tempdb..#TableSummary') IS NOT NULL
DROP TABLE #TableSummary
SELECT DISTINCT TABLE_NAME AS TableName, 0 as CountOfTable
INTO #TableSummary
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME NOT IN
('dtproperties','TableSummary','AllUserT
ables')))
SELECT * FROM #TableSummary|||Run this query against each database:
SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name, dbo.sysindexes.rowcnt
FROM dbo.sysobjects INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.status = 0)
ORDER BY dbo.sysobjects.name
"imagabo" wrote:

> I Have 2 separate data bases with the same Tables and records, I create
> a batch to synchronize my data every 2 hours but now I need a Script
> that Return the number of Rows for each Table on each DB.
> Can someone give me an Idea or the solution for this, I will really
> appreciate it.
>|||This is it, Thank you so much bschaettle, I own you one.

script for comparing tables

Hi,

Can I have a script to compare the two tables. Such that I can identify the missing rows of Table A in Table B, in the absece of any know key.

Quote:

Originally Posted by ckmoied

Hi,

Can I have a script to compare the two tables. Such that I can identify the missing rows of Table A in Table B, in the absece of any know key.

If you did it by hand, how would you determine if a row of Table B does or does not exist in Table A?

Script component with multiple outputs

Hi,

I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.

If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.

In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.

Any help on this matter would be greatfully recieved.

Cheers,

Grant

There are some basica exampes of this in Books Online, http://msdn2.microsoft.com/en-us/library/849dd38a-abb5-4702-a413-882aae3980a5(SQL.90).aspx, are they any help?

Yes to the try catch idea.

|||

Here is some additional good links:

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

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

Thanks,

Greg Van

Mullem

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.

SCOPE_IDENTITY()

Trying to insert record in Parent table and return value of that rows IDENTITY, to pass back to app for use inserting child rows into other table. Looks clean against tutorial samples(http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/050207-1.aspx). Works with Insert until *** lines are added - HELP?

ERROR:

Msg 201, Level 16, State 4, Procedure qc_submitInsertQCparentReturningID_2, Line 0

Procedure or function 'qc_submitInsertQCparentReturningID_2' expects parameter '@.newQCparent_ID', which was not supplied.

Based on this Procedure:

ALTER PROCEDURE qc_submitInsertQCparentReturningID_2

(@.newQCparent_ID INT OUTPUT) ***

AS

-- Insert New QCparent RETURNING qcParentID

INSERT INTO app.dbo.a1_qcParent

([rowCreatedBy]

,[rowNotes]

,[rowLastAction]

,[rowLastActionBy]

,[rowLastActionNote])

-- Using Parameter Variables

VALUES('Anonymous Submit'

,'By qc_submitInsertQCparentReturningID'

,'Insert'

,'Guest'

,'show donald')

-- Read the qcParient_ID back for Items2fix Insert

SET @.newQCparent_ID = SCOPE_IDENTITY() ***

- Try New SPROC

EXEC qc_submitInsertQCparentReturningID_2

Thanks, unfortunately neither worked...

snippet 1 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.newQCparent_ID):

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@.newQCparent_ID".

snippet 2 Error (EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=scope_identity())

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

|||

Sorry, I got it wrong; Leave your original procedure code as it was. Change your call of the stored procedure from

Code Snippet

EXEC qc_submitInsertQCparentReturningID_2

to

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

and if you wish to see the results something like:

Code Snippet

declare @.outputParm integer

EXEC qc_submitInsertQCparentReturningID_2 @.newQCparent_ID=@.outputParm output

select @.outputParm as [@.outputParm]

|||

Parameters are by default NULLable in SQL Server. But in case of OUTPUT parameters, you will have to always pass a value. So if you want to ignore the output value you can do:

exec qc_submitInsertQCparentReturningID_2 NULL;

If you need to retrieve the output value then do:

declare @.id int;

exec qc_submitInsertQCparentReturningID_2 @.id OUTPUT;

|||

THat allowed it to run, insert was successful, but the following resulted in 9 as scope_Identity but 14 rows listed:

- Try New SPROC

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

a) I was expecting to see the same scope as the last inserted rows ID (14) ?

and

b) can I use the @.id to populate another procedure insert ?

|||Removed the GO between statements, but Scope_Identity remained 9 while rows increased|||

In the statement:

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

go

Select SCOPE_IDENTITY()

go

Select * from a1_qcParent

The SCOPE_IDENTITY() function will not work as you are thinking -- because the IDENTITY column is assigned inside of the stored procedure and what happened there is NOT in the scope of the CALLING query. As I said above, you will need to do something like

Code Snippet

declare @.id int;

exec qc_submitInsertQCparentReturningID_4 @.id OUTPUT;

Select @.id

Select * from a1_qcParent