Multiple Columns in Rowset?

#1

Ok, I am getting this project pinned down, just need some more help. My
SQL Task uses a simple query,

SELECT ExcelLink
FROM Cardholder

I would like to know if it would be possible to return 2 columns of data,

SELECT CardholderID, ExcelLink
FROM Cardholder

and then use the ExcelLink to change the path to the Excel file (already
done) and the CardholderID to be inserted using the transform into a
column named CardholderID in the destination table.

Example recordset returned with above query,

CardholderID | ExcelLink
dlaing | \\server\reports\creditcardlog\dlaing\dlaing.xls
jdoe | \\server\reports\creditcardlog\jdoe\jdoe.xls
dsmith | \\server\reports\creditcardlog\dsmith\dsmith.xls

The DTS package loops through, changing the Excel file source path, then
runs a transform, then loops back (until the recordset is EOF or BOF).
I would like to make the transform use the CardholderID for each record
inserted by the transform in each loop cycle.

For example,

Dlaing's excel file holds 40 records, therefore for each record
inserted, dlaing would need to be inserted into the CardholderID column
on the destination. Then when the loop gets to Jdoe's ExcelLink, it
inserts jdoe as the CardholderID for however many records are there.

Is this possible?

Thanks again!
Drew Laing

#2

Sure

This would be a 1 row rowset right.

Simply output as a rowset instead of a row value in the ExecuteSQL task

This article will hopefully explain more what I mean

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Allan

"Drew" <dr00bert (AT) nospamgmail (DOT) com> wrote

Quote:

Ok, I am getting this project pinned down, just need some more help. My
SQL Task uses a simple query,

SELECT ExcelLink
FROM Cardholder

I would like to know if it would be possible to return 2 columns of data,

SELECT CardholderID, ExcelLink
FROM Cardholder

and then use the ExcelLink to change the path to the Excel file (already
done) and the CardholderID to be inserted using the transform into a
column named CardholderID in the destination table.

Example recordset returned with above query,

CardholderID | ExcelLink
dlaing | \\server\reports\creditcardlog\dlaing\dlaing.xls
jdoe | \\server\reports\creditcardlog\jdoe\jdoe.xls
dsmith | \\server\reports\creditcardlog\dsmith\dsmith.xls

The DTS package loops through, changing the Excel file source path, then
runs a transform, then loops back (until the recordset is EOF or BOF).
I would like to make the transform use the CardholderID for each record
inserted by the transform in each loop cycle.

For example,

Dlaing's excel file holds 40 records, therefore for each record
inserted, dlaing would need to be inserted into the CardholderID column
on the destination. Then when the loop gets to Jdoe's ExcelLink, it
inserts jdoe as the CardholderID for however many records are there.

Is this possible?

Thanks again!
Drew Laing

#3

I have already read and used a great deal from that tutorial. My DTS
package uses some code snippets from this tutorial. The issue is that I
need to have a multi column, multi row, rowset. This is so that I can
use the ExcelLink to change the path of the Excel (Source), and also
CardholderID so I can use it in the transform.

As before, here is a little test data from this query,

SELECT CardholderID, ExcelLink
FROM Cardholder

CardholderID | ExcelLink
dlaing | \\server\reports\creditcardlog\dlaing\dlaing.xls
jdoe | \\server\reports\creditcardlog\jdoe\jdoe.xls
dsmith | \\server\reports\creditcardlog\dsmith\dsmith.xls

I would like to put these rows and columns into a recordset, the loop
through and use the ExcelLink for the path to the Excel Source file, and
use CardholderID for the transform, to be inserted into the destination.

So it would work like this,

The SQL Task pulls up the test data above,
For the first row, the CardholderID = dlaing and the ExcelLink =
\\server\reports\creditcardlog\dlaing\dlaing.xls,
So the ExcelLink will be used for the path to the Source, and
The CardholderID will be inserted into the Destination for each row.

Does this make anymore since? When I get to work, I will post the
structures for both Source and Destination, that may help a little more.

Thanks,
Drew

Allan Mitchell wrote:

Quote:

Sure

This would be a 1 row rowset right.

Simply output as a rowset instead of a row value in the ExecuteSQL task

This article will hopefully explain more what I mean

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Allan

#4

OK

You use the article to loop over a global variable rowset.
You assign the retrieved column values into GVs

In the middle of the loop you put a Transform Data task.

On each iteration of the loop you change the DataSource property of the
Excel file connection and in your SourceSQLStatement for the Transform Data
Task you would use a query like this

SELECT <col list> FROM <table> WHERE CardHolderID = ?

You map the GV that holds the relevant column data for the CardholderID to
the ? in the parameters section of the Transform Data Task. (Button on front)

Make sense?

Allan

"Drew" wrote:

Quote:

I have already read and used a great deal from that tutorial. My DTS
package uses some code snippets from this tutorial. The issue is that I
need to have a multi column, multi row, rowset. This is so that I can
use the ExcelLink to change the path of the Excel (Source), and also
CardholderID so I can use it in the transform.

As before, here is a little test data from this query,

SELECT CardholderID, ExcelLink
FROM Cardholder

CardholderID | ExcelLink
dlaing | \\server\reports\creditcardlog\dlaing\dlaing.xls
jdoe | \\server\reports\creditcardlog\jdoe\jdoe.xls
dsmith | \\server\reports\creditcardlog\dsmith\dsmith.xls

I would like to put these rows and columns into a recordset, the loop
through and use the ExcelLink for the path to the Excel Source file, and
use CardholderID for the transform, to be inserted into the destination.

So it would work like this,

The SQL Task pulls up the test data above,
For the first row, the CardholderID = dlaing and the ExcelLink =
\\server\reports\creditcardlog\dlaing\dlaing.xls,
So the ExcelLink will be used for the path to the Source, and
The CardholderID will be inserted into the Destination for each row.

Does this make anymore since? When I get to work, I will post the
structures for both Source and Destination, that may help a little more.

Thanks,
Drew

Allan Mitchell wrote:
Sure

This would be a 1 row rowset right.

Simply output as a rowset instead of a row value in the ExecuteSQL task

This article will hopefully explain more what I mean

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Allan

#5

Ok here is my situation,

SOURCE (Excel File)
Has 9 columns, varying number of rows

PONum
PODate
Vendor
Description
Amount
CCCObjectCode
Complete
Carryover
Credit

DESTINATION (SQL Server)
Has 9 columns

UID incrementing int for primary key
CardholderID
PONum
PODate
Vendor
Description
Amount
CCCObjectCode
Complete
Carryover
Credit

I have the transform set up to insert the records from Excel to SQL Server.
The only column that isn't inserted is CardholderID. I would like to use
the SQL Task to get the CardholderID and insert it into the Destination.

Thanks,
Drew

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

OK

You use the article to loop over a global variable rowset.
You assign the retrieved column values into GVs

In the middle of the loop you put a Transform Data task.

On each iteration of the loop you change the DataSource property of the
Excel file connection and in your SourceSQLStatement for the Transform
Data
Task you would use a query like this

SELECT <col list> FROM <table> WHERE CardHolderID = ?

You map the GV that holds the relevant column data for the CardholderID to
the ? in the parameters section of the Transform Data Task. (Button on
front)

Make sense?

Allan

"Drew" wrote:

I have already read and used a great deal from that tutorial. My DTS
package uses some code snippets from this tutorial. The issue is that I
need to have a multi column, multi row, rowset. This is so that I can
use the ExcelLink to change the path of the Excel (Source), and also
CardholderID so I can use it in the transform.

As before, here is a little test data from this query,

SELECT CardholderID, ExcelLink
FROM Cardholder

CardholderID | ExcelLink
dlaing | \\server\reports\creditcardlog\dlaing\dlaing.xls
jdoe | \\server\reports\creditcardlog\jdoe\jdoe.xls
dsmith | \\server\reports\creditcardlog\dsmith\dsmith.xls

I would like to put these rows and columns into a recordset, the loop
through and use the ExcelLink for the path to the Excel Source file, and
use CardholderID for the transform, to be inserted into the destination.

So it would work like this,

The SQL Task pulls up the test data above,
For the first row, the CardholderID = dlaing and the ExcelLink =
\\server\reports\creditcardlog\dlaing\dlaing.xls,
So the ExcelLink will be used for the path to the Source, and
The CardholderID will be inserted into the Destination for each row.

Does this make anymore since? When I get to work, I will post the
structures for both Source and Destination, that may help a little more.

Thanks,
Drew

Allan Mitchell wrote:
Sure

This would be a 1 row rowset right.

Simply output as a rowset instead of a row value in the ExecuteSQL task

This article will hopefully explain more what I mean

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Allan

#6

Hi Drew,

"Drew" wrote:

Quote:

I have the transform set up to insert the records from Excel to SQL
Server. The only column that isn't inserted is CardholderID. I would
like to use the SQL Task to get the CardholderID and insert it into the
Destination.

when you have the ID stored in a GV, then you can assign an additional
ActiveX Script transform to the destination row in your transform task,
where you assign the GV to the destination row.
This will insert the ID with each row of the imported data.
I Think there should be also an example for that on www.sqldts.com

BTW: already thought about a good book for DTS? It looks like you're
jumping from zero right into the advanced feature section ;-)

Helge

#7

I do need a book... my problem is that my vocabulary on this topic is
lacking. I am having problems getting the thoughts and ideas out of my head
and into a question. I want to thank Helge, Allan and Darren for helping me
out with this project. It will be a great application when I am done...

Thanks,
Drew

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Quote:

Hi Drew,

"Drew" wrote:
I have the transform set up to insert the records from Excel to SQL
Server. The only column that isn't inserted is CardholderID. I would
like to use the SQL Task to get the CardholderID and insert it into the
Destination.

when you have the ID stored in a GV, then you can assign an additional
ActiveX Script transform to the destination row in your transform task,
where you assign the GV to the destination row.
This will insert the ID with each row of the imported data.
I Think there should be also an example for that on www.sqldts.com

BTW: already thought about a good book for DTS? It looks like you're
jumping from zero right into the advanced feature section ;-)

Helge

#8

As Helge has kindly pointed out.

You have the Global Variable with the guy's details.

You can now map the GV to the destination column

How?

In the transformations of the datapump task

Highlight the destination column
Make sure no source columns selected
Now hit New | Active X Script

Now in the script itself do

DTSDestination("Card Holder ID") = DTSGlobalVariables("Cardholder ID").Value

When you go back and look at the transforms you will see a line appearing
out from the top of the transforms box not attached to a source going to your
destination.

HTH

Allan
www.sqldts.com

"Helge C. Rutz" wrote:

Quote:

Hi Drew,

"Drew" wrote:
I have the transform set up to insert the records from Excel to SQL
Server. The only column that isn't inserted is CardholderID. I would
like to use the SQL Task to get the CardholderID and insert it into the
Destination.

when you have the ID stored in a GV, then you can assign an additional
ActiveX Script transform to the destination row in your transform task,
where you assign the GV to the destination row.
This will insert the ID with each row of the imported data.
I Think there should be also an example for that on www.sqldts.com

BTW: already thought about a good book for DTS? It looks like you're
jumping from zero right into the advanced feature section ;-)

Helge

#9

Hi Allan,

"Allan Mitchell" wrote:

Quote:

As Helge has kindly pointed out.

You have the Global Variable with the guy's details.

You can now map the GV to the destination column

How?

In the transformations of the datapump task

Highlight the destination column
Make sure no source columns selected
Now hit New | Active X Script

Now in the script itself do

DTSDestination("Card Holder ID") = DTSGlobalVariables("Cardholder
ID").Value

When you go back and look at the transforms you will see a line appearing
out from the top of the transforms box not attached to a source going to
your
destination.

jeah, exactly what I meant, and described shortly, haven't I?
Do you mean I should write longer and more detailed answers?
Damn, I'm to sluggard for that ;-)

Helge

#10

I was very confused, now that I took a step back, loaded up sample 298 and
made some changes. I thought the Rowset only held 1 column, which was where
my problems were coming in. As I see now, the rowset will hold as many
columns as you tell it to (I'm sure there is a hit on resources if there is
a bunch of records).

We'll see what I can do today...

Thanks again!
Drew

"Helge C. Rutz" <news050424 (AT) nurfuerspam (DOT) de> wrote

Quote:

Hi Drew,

"Drew" wrote:
I have the transform set up to insert the records from Excel to SQL
Server. The only column that isn't inserted is CardholderID. I would
like to use the SQL Task to get the CardholderID and insert it into the
Destination.

when you have the ID stored in a GV, then you can assign an additional
ActiveX Script transform to the destination row in your transform task,
where you assign the GV to the destination row.
This will insert the ID with each row of the imported data.
I Think there should be also an example for that on www.sqldts.com

BTW: already thought about a good book for DTS? It looks like you're
jumping from zero right into the advanced feature section ;-)

Helge