I am having a headache working on a huge database which contains 120000 records. the big problem is that the database contains duplicates of the "serial no" of products. Another important field is the "date_purchased". How do I remove the duplicates and retain the serial no with the latest date purchased? I have managed to remove duplicates but the program does not remove on the basis of the date_purchased.Hi,
i found some info's about duplicates on planet-source-code.com as i had same problems:
see attached file (too big to quote here)|||How about doing it in 2 stages ie:-
SELECT DISTINCT SerialNo,Max(DatePurchased) From tbl GROUP BY SerialNo
Then inner Join it to
SELECT * FROM tbl
U could create 1 Statement as a Permanent View or Do it all in a stored procedure & #tmp table
Should work -
U'l prob find it can all be done in one Statement - Some1 Post & I'll C which way is faster
GW|||Or a refinement on GWilley's code
SELECT DISTINCT SerialNo, Max (DISTINCT DatePurchased)
From tbl??
Group By SerialNo
I ran this on something similar on my db (table with over 700,000 records) and it seemed to work correctly.|||you do not need DISTINCT when you use GROUP BY -- groups are distinct by definition
save the serial numbers and max dates in a working table, then delete all the rows that don't have a match in the working table
select serial, max(date_purchased) as maxdate
into keepthese
from yourtable
group by serial
delete from yourtable
where not exists
( select 1
from keepthese
where serialno = yourtable.serial
and maxdate = yourtable.date_purchased )
rudy
http://r937.com/
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
Duplicate Record Problem
I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
My first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.In SQL Server duplicates are eliminated by using Unique constraint or Index on the index you can add IGNORE_DUP_KEY option and make it all inserts because that option will not affect update statements. And they are very slow but the UNION operator also eliminates duplicates by applying implict Distinct. Run a search for all of the above in SQl Server BOL(books online). Hope this helps.|||For an import approach, I'd suggest inserting the records into atemporary table first. Then select out the unique records and insertthem into your destination table.
For the above query, would something like this work?
SELECT DISTINCT
PatientID,
MRNumber,
PatientFirstName,
PatientLastName,
PatientDOB,
PatientSex,
NewPatient,
...etc, etc, etc...
FROM
(
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr,tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr,tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
) AS SQ|||
The other option of using a temporary table during the DTS package is viable, but once again, I can't seem to get the query right. I think what I want is something like:
Insert Into tblAppointments AppointmentKey, PatientFirstName, PatientLastName, ...VALUES (Select DISTINCT AppointmentKey, PatientFirstName, PatientLastName, ...)
but once again, I can't seem to get the syntax correct. Any help would be greatly appreciated. Thanks.|||Thanks for your response. Actually, the tblAppointments table is the one with the duplicates, and yes, the IDs are duplicated as well.
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
My first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.In SQL Server duplicates are eliminated by using Unique constraint or Index on the index you can add IGNORE_DUP_KEY option and make it all inserts because that option will not affect update statements. And they are very slow but the UNION operator also eliminates duplicates by applying implict Distinct. Run a search for all of the above in SQl Server BOL(books online). Hope this helps.|||For an import approach, I'd suggest inserting the records into atemporary table first. Then select out the unique records and insertthem into your destination table.
For the above query, would something like this work?
SELECT DISTINCT
PatientID,
MRNumber,
PatientFirstName,
PatientLastName,
PatientDOB,
PatientSex,
NewPatient,
...etc, etc, etc...
FROM
(
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr,tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr,tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
) AS SQ|||
I have re-written this a bit to remove the CROSS JOIN syntax which I think is less efficient than the INNER JOIN that is implied in the WHERE clause:
SELECT A.PatientID, D.MRNumber, A.PatientFirstName, A.PatientLastName, A.PatientDOB, A.PatientSex,
A.NewPatient, A.HomePhone, A.WorkPhone, A.Insurance1, D.Ins1CertNmbr, A.Insurance2,
D.Ins2CertNmbr, A.Insurance3, D.Ins3CertNmbr, A.ApptDate, A.ApptTime
FROM tblAppointments A INNER JOIN tblPTDEMO2 D on A.PatientID = D.MRNumber
WHERE A.Insurance1 = 'MED'
AND A.ApptTypeID <> 'MTG'
AND A.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By A.ApptDate
Now, I assume that the duplicates are in thetblPTDEMO2table. Tell me more about these: are the IDs duped?
The other option of using a temporary table during the DTS package is viable, but once again, I can't seem to get the query right. I think what I want is something like:
Insert Into tblAppointments AppointmentKey, PatientFirstName, PatientLastName, ...VALUES (Select DISTINCT AppointmentKey, PatientFirstName, PatientLastName, ...)
but once again, I can't seem to get the syntax correct. Any help would be greatly appreciated. Thanks.|||Thanks for your response. Actually, the tblAppointments table is the one with the duplicates, and yes, the IDs are duplicated as well.
Thursday, March 22, 2012
duplicate data
Hello all,
I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database.
I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added.
Can anyone help?
ThanksCould you provide us the DDL?|||Well lets see...
I am not an expert in database programming but I am learning...
Ok,
here is some psuedocode for a general idea
create procedure insertData
(
var 1,
var 2,
var 3
)
set nocount on
as
insert into <table> values <1, 2, 3>
select * from <table>
for(i < list) --perform test and search the database
(
array < list> or perhaps a resursion statement
if (list == var1, var2, var3)
return -1
else return 0
)|||you don't have to check for duplicates, the database can do this for you automatically
just declare a unique constraint on the column(s) that you want to be unique
vwalah!
:)|||well thanks,
that was easy!|||There are several ways to prevent duplicate data from being inserted into a table. One of them would be using a UNIQUE constraint.
But, before you get into the solution I'd like to ask you to provide us with the real table structure, some sample data, as well as the business rules you are trying to enforce. All this because there's also the chance that you are using an inadequate table design to fullfill your needs.|||This is just one table with the fields: year, month, product type, product amount
Now the table does not have a unique field, product type is a listing of only five categories such as: apples, pears, peaches, grapes, and oranges
So really there is no unique field.|||my opinion: year, month, product type are unique, and should be defined as the primary key
vwalah! no need to check for dupes, the database will do it for you :)|||But you could have data stating the same year, month, and product type, right?|||since this is just one table why not set all of the fields as a primary key?|||*shakes head*
I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database.
I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added.
Can anyone help?
ThanksCould you provide us the DDL?|||Well lets see...
I am not an expert in database programming but I am learning...
Ok,
here is some psuedocode for a general idea
create procedure insertData
(
var 1,
var 2,
var 3
)
set nocount on
as
insert into <table> values <1, 2, 3>
select * from <table>
for(i < list) --perform test and search the database
(
array < list> or perhaps a resursion statement
if (list == var1, var2, var3)
return -1
else return 0
)|||you don't have to check for duplicates, the database can do this for you automatically
just declare a unique constraint on the column(s) that you want to be unique
vwalah!
:)|||well thanks,
that was easy!|||There are several ways to prevent duplicate data from being inserted into a table. One of them would be using a UNIQUE constraint.
But, before you get into the solution I'd like to ask you to provide us with the real table structure, some sample data, as well as the business rules you are trying to enforce. All this because there's also the chance that you are using an inadequate table design to fullfill your needs.|||This is just one table with the fields: year, month, product type, product amount
Now the table does not have a unique field, product type is a listing of only five categories such as: apples, pears, peaches, grapes, and oranges
So really there is no unique field.|||my opinion: year, month, product type are unique, and should be defined as the primary key
vwalah! no need to check for dupes, the database will do it for you :)|||But you could have data stating the same year, month, and product type, right?|||since this is just one table why not set all of the fields as a primary key?|||*shakes head*
Duplex printing in reporting services
I have been working on this problem for days. I have a report that is grouped
and will be printed duplex (front and back). There is a page break after each
group. I want to ensure that the start of a new group does not end up printed
on the back side of an old group. For example:
and will be printed duplex (front and back). There is a page break after each
group. I want to ensure that the start of a new group does not end up printed
on the back side of an old group. For example:
Page 1 (printed on front side of paper): group 1
Page 2 (printed on back side of paper): more group 1
Page 3 (printed on front side of paper): more group 1
Page 4 (printed on on back side of paper): group 2 < This is a problem
I need:
Page 1 (front side): group 1
Page 2 (back side): more group 1
Page 3 (front side): more group 1
Page 4 (back side): <This Page Intentionally Left Blank>
Page 5 (front side): group 2
Issues that I've run into include the fact that SSRS renders the entire body
first, before rendering the header/footer. This prevents the body of the
report from knowing which page it will end up on when it is rendered (and is
also why you cannot reference Globals!PageNumber from the body).
The restriction above also prevents using a variable in custom code to know
when to generate a page break. If you set this variable in the header/footer
then the body will never see it as the entire body is rendered first and will
therefore only see the initial state of the variable.
I have seen this issue posted in a number of places,
but no one ever has a solution to this (except switching back to
Crystal Reports). Have any MVPs ever addressed this issue? I am
really hoping someone can offer a good solution or work-around.
Duplex printing in reporting services
I have been working on this problem for days. I have a report that is grouped
and will be printed duplex (front and back). There is a page break after each
group. I want to ensure that the start of a new group does not end up printed
on the back side of an old group. For example:
and will be printed duplex (front and back). There is a page break after each
group. I want to ensure that the start of a new group does not end up printed
on the back side of an old group. For example:
Page 1 (printed on front side of paper): group 1
Page 2 (printed on back side of paper): more group 1
Page 3 (printed on front side of paper): more group 1
Page 4 (printed on on back side of paper): group 2 < This is a problem
I need:
Page 1 (front side): group 1
Page 2 (back side): more group 1
Page 3 (front side): more group 1
Page 4 (back side): <This Page Intentionally Left Blank>
Page 5 (front side): group 2
Issues that I've run into include the fact that SSRS renders the entire body
first, before rendering the header/footer. This prevents the body of the
report from knowing which page it will end up on when it is rendered (and is
also why you cannot reference Globals!PageNumber from the body).
The restriction above also prevents using a variable in custom code to know
when to generate a page break. If you set this variable in the header/footer
then the body will never see it as the entire body is rendered first and will
therefore only see the initial state of the variable.
I have seen this issue posted in a number of places,
but no one ever has a solution to this (except switching back to
Crystal Reports). Have any MVPs ever addressed this issue? I am
really hoping someone can offer a good solution or work-around.
Monday, March 19, 2012
Dumb question
Simple question from a simpleton. I'm working through Microsoft Press SQL Server 2005 Reporting Services Step by Step. It references a database rs2005sbsDW, which as far as I can tell was not included with either the sample databases on the SQL Server (standard edition) install disk or the Step by Step book. Where the heck is it? What am I missing.
Trying to reinstall the sample databases from the SQL Server install disk's tells me I have everything installed already.
Thanks in advance.
Hi,
Did you follow the instructions on page XV? The files will be installed via a setup.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||I did thanks. I was using the book a bit too literally. The instructions for specifying the database in the connection manager just suggest typing the database name in or using the drop down box, neither which accepted or display the rs2005sbsDW database. I assumed it just wasn't there. Once I knew where is was, it became obvious to use the browse option in connection manager instead.
Thanks.
|||Please I need the datasource rs2005sbsDW my email is edupuebla@.yahoo.com.Thanks
Sunday, March 11, 2012
Dumb question
Simple question from a simpleton. I'm working through Microsoft Press SQL Server 2005 Reporting Services Step by Step. It references a database rs2005sbsDW, which as far as I can tell was not included with either the sample databases on the SQL Server (standard edition) install disk or the Step by Step book. Where the heck is it? What am I missing.
Trying to reinstall the sample databases from the SQL Server install disk's tells me I have everything installed already.
Thanks in advance.
Hi,
Did you follow the instructions on page XV? The files will be installed via a setup.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||I did thanks. I was using the book a bit too literally. The instructions for specifying the database in the connection manager just suggest typing the database name in or using the drop down box, neither which accepted or display the rs2005sbsDW database. I assumed it just wasn't there. Once I knew where is was, it became obvious to use the browse option in connection manager instead.
Thanks.
|||Please I need the datasource rs2005sbsDW my email is edupuebla@.yahoo.com.Thanks
Friday, March 9, 2012
Dual Instances In onSQL Server 2000 Enterprise Manager
I am working on a site's SQL Server 2000 database on a W2k3 machine . I went into Enterprise Manager and saw that their database resides on a named instance. I did not see the default instance listed so I registered that using windows authentication. I noticed that the default instance had a user database that had the same name as the user database on the named instance that I was to work on. I looked at the properties of the databases and saw that on both the default and named instances of SQL Server that the Data Files and Log Files for the user database point to the same location.
Is this a problem? Can anyone see any issues with this? Does this mean that someone can simply connect to the named or the default instance of the SQL Server and connect to the same database?
Kirk
That is the not possible if one instance is already using those database files, then the other instance will not be able to use or even attach the database. Try to refresh the Databases pane of Enterprise Manager on the both the instancesWednesday, March 7, 2012
DTSX package continues to throw errors when working with large dataset.
I have a dataset that is between 40-50K records that has to go through a process that is pre-defined. SSIS works just fine with the smaller sets even up to 20K but this job keeps blowing up saying something along the lines of cannot write to recordset destination. Does this make sense to anyone? The sever is a 2 processor with 2GB of ram. Physical memory usage spikes to about 1.6GB during the run but the processor never really gets above 30% usage. Does this product just not scale yet?
I'd be interested to see the full error message. Depending on how wide the buffer is, trying to stuff that many rows into a variable may be a bit optimistic, just because of the memory requirements. The SSIS pipeline is great for large volumes, and the buffer design means you don't have to load all data into memory at once, but I suspect you are actually forcing just that by using the Recordset Destination. Why do you want to do this?
I'd be interested to see the full error message. Depending on how wide the buffer is, trying to stuff that many rows into a variable may be a bit optimistic, just because of the memory requirements. The SSIS pipeline is great for large volumes, and the buffer design means you don't have to load all data into memory at once, but I suspect you are actually forcing just that by using the Recordset Destination. Why do you want to do this?
Sunday, February 26, 2012
DTSrun in Stored Procedure
Hi,
I'm writing a stored procedure to run a dts package and I've successfuly
got this working using my sotred proc and the syntax of dtsrun is
correct.
However, I'm trying to pass a variable to the DTSrun command and this is
where I'm having the problem
the code for the proc is:
Declare @.partcode nvarchar(255)
EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '
Everytime I run it I get this error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '+'.
can someone help me with this please?
M3ckon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You'll to build the command string before executing xp_cmdshell. For
example:
DECLARE @.Command varchar(1000)
SET @.Command = 'DTSRun /S "server" /U "user" /P "password" /N
"XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '
EXEC master..xp_cmdshell @.Command
--
Hope this helps.
Dan Guzman
SQL Server MVP
"m3ckon" <anonymous@.devdex.com> wrote in message
news:407eb7e2$0$206$75868355@.news.frii.net...
> Hi,
> I'm writing a stored procedure to run a dts package and I've successfuly
> got this working using my sotred proc and the syntax of dtsrun is
> correct.
> However, I'm trying to pass a variable to the DTSrun command and this is
> where I'm having the problem
> the code for the proc is:
> Declare @.partcode nvarchar(255)
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "XMLStockCheck" /A "oPartCode":"8"="' + @.partcode + '" /W "0" '
> Everytime I run it I get this error:
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '+'.
> can someone help me with this please?
> M3ckon
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
DTSRun Fails while executing from COM
Hi,
i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute.
Function ExeDTSRun(DTSName, mKey As String)
Dim mDTS, spServerName, spUid, spPwd As String
spUid = "sa"
spPwd = "test"
spServerName = "server"
mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0"""
Shell mDTS, vbNormalFocus
ExeDTSRun = "Successfully Run DTS : " & DTSName
End Function
Regards
AdiDo you get an error?|||First guess: Does the account running the IIS thread have permission (NT permission) to execute DTSRUN.EXE. Also, if the DTS package is accessing resources other than on the local machine, you will need to grant permission for that as well.
i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute.
Function ExeDTSRun(DTSName, mKey As String)
Dim mDTS, spServerName, spUid, spPwd As String
spUid = "sa"
spPwd = "test"
spServerName = "server"
mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0"""
Shell mDTS, vbNormalFocus
ExeDTSRun = "Successfully Run DTS : " & DTSName
End Function
Regards
AdiDo you get an error?|||First guess: Does the account running the IIS thread have permission (NT permission) to execute DTSRUN.EXE. Also, if the DTS package is accessing resources other than on the local machine, you will need to grant permission for that as well.
Friday, February 24, 2012
DTS: How can I process each row in result set to access properties on another package obje
(SQL Server 2000, SP3a)
Hello all!
I have a DTS package that I'm working with, in which I have a query that I want to invoke
on a target SQL Server that will return a handful of rows. For each row, I want to set
some package properties (on another object in the package). What would be the best
approach to this? I thought that I might use the "Transform Data Task", even though I
don't really have a "Destination", per se (that is, I want to process each "Source" record
via an ActiveX script).
However, when I try and do this, I seem to be getting an error when I execute that
"Transform Data Task" step (something akin to "Execution Cancelled by User").
Is there some other way that I should approach this?
Regards,
John PetersonTake a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>|||Thanks, Dan -- but I can't seem to get my head around your suggestion. Basically, what I
want is to be able to specify a Source Query that would return a bunch of rows. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a "Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specify
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package that I'm working with, in which I have a query that I
> want to invoke
> > on a target SQL Server that will return a handful of rows. For each row,
> I want to set
> > some package properties (on another object in the package). What would be
> the best
> > approach to this? I thought that I might use the "Transform Data Task",
> even though I
> > don't really have a "Destination", per se (that is, I want to process each
> "Source" record
> > via an ActiveX script).
> >
> > However, when I try and do this, I seem to be getting an error when I
> execute that
> > "Transform Data Task" step (something akin to "Execution Cancelled by
> User").
> >
> > Is there some other way that I should approach this?
> >
> > Regards,
> >
> > John Peterson
> >
> >
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrote:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of rows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > Take a look at the DynamicProperties task. This will allow you to set
DTS
> > properties based query that returns a scalar value. You'll need to
specify
> > a separate query for each property.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package that I'm working with, in which I have a query
that I
> > want to invoke
> > > on a target SQL Server that will return a handful of rows. For each
row,
> > I want to set
> > > some package properties (on another object in the package). What
would be
> > the best
> > > approach to this? I thought that I might use the "Transform Data
Task",
> > even though I
> > > don't really have a "Destination", per se (that is, I want to process
each
> > "Source" record
> > > via an ActiveX script).
> > >
> > > However, when I try and do this, I seem to be getting an error when I
> > execute that
> > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > User").
> > >
> > > Is there some other way that I should approach this?
> > >
> > > Regards,
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of existing DTS
objects, rather than having to write a lot of code. Alas, it seems like it's not quite
the case, even though DTS seems uniquely qualified to do this type of thing (almost).
Since it can use a Connection to issue a query on that remote server and process the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Query Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX transformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> Sorry, but I don't understand what you mean by <withOUT doing anything to a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > > Take a look at the DynamicProperties task. This will allow you to set
> DTS
> > > properties based query that returns a scalar value. You'll need to
> specify
> > > a separate query for each property.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package that I'm working with, in which I have a query
> that I
> > > want to invoke
> > > > on a target SQL Server that will return a handful of rows. For each
> row,
> > > I want to set
> > > > some package properties (on another object in the package). What
> would be
> > > the best
> > > > approach to this? I thought that I might use the "Transform Data
> Task",
> > > even though I
> > > > don't really have a "Destination", per se (that is, I want to process
> each
> > > "Source" record
> > > > via an ActiveX script).
> > > >
> > > > However, when I try and do this, I seem to be getting an error when I
> > > execute that
> > > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > > User").
> > > >
> > > > Is there some other way that I should approach this?
> > > >
> > > > Regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you can
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
http://www.amazon.co.uk/exec/obidos/ASIN/0672320118/qid=1086594526/sr=1-1/ref=sr_1_2_1/202-5145180-8774263
Regards,
Paul Ibison
Hello all!
I have a DTS package that I'm working with, in which I have a query that I want to invoke
on a target SQL Server that will return a handful of rows. For each row, I want to set
some package properties (on another object in the package). What would be the best
approach to this? I thought that I might use the "Transform Data Task", even though I
don't really have a "Destination", per se (that is, I want to process each "Source" record
via an ActiveX script).
However, when I try and do this, I seem to be getting an error when I execute that
"Transform Data Task" step (something akin to "Execution Cancelled by User").
Is there some other way that I should approach this?
Regards,
John PetersonTake a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>|||Thanks, Dan -- but I can't seem to get my head around your suggestion. Basically, what I
want is to be able to specify a Source Query that would return a bunch of rows. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a "Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specify
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I have a DTS package that I'm working with, in which I have a query that I
> want to invoke
> > on a target SQL Server that will return a handful of rows. For each row,
> I want to set
> > some package properties (on another object in the package). What would be
> the best
> > approach to this? I thought that I might use the "Transform Data Task",
> even though I
> > don't really have a "Destination", per se (that is, I want to process each
> "Source" record
> > via an ActiveX script).
> >
> > However, when I try and do this, I seem to be getting an error when I
> execute that
> > "Transform Data Task" step (something akin to "Execution Cancelled by
> User").
> >
> > Is there some other way that I should approach this?
> >
> > Regards,
> >
> > John Peterson
> >
> >
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrote:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of rows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > Take a look at the DynamicProperties task. This will allow you to set
DTS
> > properties based query that returns a scalar value. You'll need to
specify
> > a separate query for each property.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I have a DTS package that I'm working with, in which I have a query
that I
> > want to invoke
> > > on a target SQL Server that will return a handful of rows. For each
row,
> > I want to set
> > > some package properties (on another object in the package). What
would be
> > the best
> > > approach to this? I thought that I might use the "Transform Data
Task",
> > even though I
> > > don't really have a "Destination", per se (that is, I want to process
each
> > "Source" record
> > > via an ActiveX script).
> > >
> > > However, when I try and do this, I seem to be getting an error when I
> > execute that
> > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > User").
> > >
> > > Is there some other way that I should approach this?
> > >
> > > Regards,
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of existing DTS
objects, rather than having to write a lot of code. Alas, it seems like it's not quite
the case, even though DTS seems uniquely qualified to do this type of thing (almost).
Since it can use a Connection to issue a query on that remote server and process the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Query Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX transformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> Sorry, but I don't understand what you mean by <withOUT doing anything to a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
> > want is to be able to specify a Source Query that would return a bunch of
> rows. Then, for
> > each row, I want to invoke some ActiveX snippet withOUT doing anything to
> a "Destination".
> > I don't see that it's too easy with DTS...
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > > Take a look at the DynamicProperties task. This will allow you to set
> DTS
> > > properties based query that returns a scalar value. You'll need to
> specify
> > > a separate query for each property.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> > > > (SQL Server 2000, SP3a)
> > > >
> > > > Hello all!
> > > >
> > > > I have a DTS package that I'm working with, in which I have a query
> that I
> > > want to invoke
> > > > on a target SQL Server that will return a handful of rows. For each
> row,
> > > I want to set
> > > > some package properties (on another object in the package). What
> would be
> > > the best
> > > > approach to this? I thought that I might use the "Transform Data
> Task",
> > > even though I
> > > > don't really have a "Destination", per se (that is, I want to process
> each
> > > "Source" record
> > > > via an ActiveX script).
> > > >
> > > > However, when I try and do this, I seem to be getting an error when I
> > > execute that
> > > > "Transform Data Task" step (something akin to "Execution Cancelled by
> > > User").
> > > >
> > > > Is there some other way that I should approach this?
> > > >
> > > > Regards,
> > > >
> > > > John Peterson
> > > >
> > > >
> > >
> > >
> >
> >
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you can
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
http://www.amazon.co.uk/exec/obidos/ASIN/0672320118/qid=1086594526/sr=1-1/ref=sr_1_2_1/202-5145180-8774263
Regards,
Paul Ibison
Wednesday, February 15, 2012
DTS to import data
Hi All,
I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?I think you'll need to get the provider of the Excel file to at least include the field even if it doesn't have any data. Then, if in your destination table you can specify that columns allow NULL values, when you receive a file that doesn't have any data for a particular column in your destination table, it won't matter.
You could also set a default value on those columns. Are you working with a native Excel file or a CSV file?
Lempster
I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?I think you'll need to get the provider of the Excel file to at least include the field even if it doesn't have any data. Then, if in your destination table you can specify that columns allow NULL values, when you receive a file that doesn't have any data for a particular column in your destination table, it won't matter.
You could also set a default value on those columns. Are you working with a native Excel file or a CSV file?
Lempster
DTS to Excel Export - Changing Format
Hi,
I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.
The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.
But when I open this file that decimal field is displayed as a string column and its left aligned.
Is there any way to fix this problem?
Thanks,I've never seen this happen. You aren't wrapping you values in quotes are you when you export them.|||No, I am not wrapping values in quotes.
I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.
The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.
But when I open this file that decimal field is displayed as a string column and its left aligned.
Is there any way to fix this problem?
Thanks,I've never seen this happen. You aren't wrapping you values in quotes are you when you export them.|||No, I am not wrapping values in quotes.
Subscribe to:
Posts (Atom)