| Groups | Blog | Home |
sql server dts : How to check row existence while Importing data using ActiveX Scri
Hi All,
I am trying to make an Access Project using SQL Server 2000 DTS functionalities. I have to import selective columns from a table of dbase5 database into SQL Server table. Every day, the dbase5 table will have some new rows. For the first time, I want to import all rows (abt 50K rows) and subsequently, I want the ActiveX Script in DTS to check for only new rows and import them only, not the whole recordset. I am using Transform Data Task and then using "ActiveX Script" in Transformation options. What code will get this done. Any assistance or pointing to right links is highly appreciated.
You do not need to use an Active Script transform on the face of things
unless you really want to. Can you identify new rows? In the SourceSQLStatement of the DataPump task your query would identify the new rows The Row Has a Date Entered The Row has a Date Updated. You could also create a Linked Server of the dBase database and query it as though it was a SQL Server and by comparing it to your SQL Server you could import only the rows you need. Allan [quoted text, click to view] "Vishal Sood" <Vishal Sood@discussions.microsoft.com> wrote in message
news:Vishal Sood@discussions.microsoft.com: > Hi All, > > I am trying to make an Access Project using SQL Server 2000 DTS > functionalities. > > I have to import selective columns from a table of dbase5 database into SQL > Server table. Every day, the dbase5 table will have some new rows. For the > first time, I want to import all rows (abt 50K rows) and subsequently, I want > the ActiveX Script in DTS to check for only new rows and import them only, > not the whole recordset. > > I am using Transform Data Task and then using "ActiveX Script" in > Transformation options. > > What code will get this done. Any assistance or pointing to right links is > highly appreciated. > > Thanks.
Allan,
Hi. Thanks. In the dbase5 database, there is no field which uniquely identifies new rows. The only option there is to use a combination of the following 2 fields : Date and Time. This combination of 2 fields can make a unique key and I can try to retreive records which are newer than a particular date & time. I do not know how to write the code to filter on this unique key in "SourceSQLStatement of the DataPump task". Any hints are welcome. Thanks. -- Vishal Sood [quoted text, click to view] "Allan Mitchell" wrote:
> You do not need to use an Active Script transform on the face of things > unless you really want to. Can you identify new rows? In the > SourceSQLStatement of the DataPump task your query would identify the > new rows > > The Row Has a Date Entered > The Row has a Date Updated. > > > You could also create a Linked Server of the dBase database and query it > as though it was a SQL Server and by comparing it to your SQL Server you > could import only the rows you need. > > Allan > > > "Vishal Sood" <Vishal Sood@discussions.microsoft.com> wrote in message > news:Vishal Sood@discussions.microsoft.com: > > > Hi All, > > > > I am trying to make an Access Project using SQL Server 2000 DTS > > functionalities. > > > > I have to import selective columns from a table of dbase5 database into SQL > > Server table. Every day, the dbase5 table will have some new rows. For the > > first time, I want to import all rows (abt 50K rows) and subsequently, I want > > the ActiveX Script in DTS to check for only new rows and import them only, > > not the whole recordset. > > > > I am using Transform Data Task and then using "ActiveX Script" in > > Transformation options. > > > > What code will get this done. Any assistance or pointing to right links is > > highly appreciated. > >
Allan,
Thanks. Great responses and at lightening speed! Can I request you to help me out with my queries stage by stage with this database I am triyng to make. I will write one issue at a time and if you could tell me if it is right or not, and if not, why? Really appreciate your directions till now, even if you are not able to guide step by step. If the answer is yes, here is the first question :- The incoming new rowcount is going to a few hundred lines at a time. Hence, I am planning to use Lookups. Have already seen the link you sent and have got some good idea on this. The source data has only about 12 columns, whereas I want the destination table to have about 30 columns, which will be a combination of 1. default values 2. some blank as well 3. Lookup columns 4. One Primary Key column having a 3 digit Alpha code and then an incremental number of 8 digits, which will be stored in a master table. I want to know how to insert a blank column for the destination table for which there is no column in the source and also the primary key column. Trust the way forward in this is to use ActiveX Script Transformations only? -- Vishal Sood [quoted text, click to view] "Allan Mitchell" wrote:
> You can either import the whole dBase tabe to a staging table in SQL Server > and then use TSQL to do the manipulation or if the incoming rowcount from > dBase is not large you can use Lookups. > > How to Use Lookups in DTS > (http://www.sqldts.com/default.aspx?277) > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > www.SQLDTS.com - The site for all your DTS needs. > www.SQLIS.com - You thought DTS was good. here we show you the new stuff. > www.konesans.com - Consultancy from the people who know > > > "Vishal" <Vishal@discussions.microsoft.com> wrote in message > news:5832ED07-C3FC-4C1C-81AA-F0F96969A3AD@microsoft.com... > > Allan, > > > > Hi. Thanks. > > > > In the dbase5 database, there is no field which uniquely identifies new > > rows. The only option there is to use a combination of the following 2 > > fields > > : Date and Time. This combination of 2 fields can make a unique key and I > > can > > try to retreive records which are newer than a particular date & time. > > > > I do not know how to write the code to filter on this unique key in > > "SourceSQLStatement of the DataPump task". Any hints are welcome. > > > > Thanks. > > > > -- > > Vishal Sood > > > > > > "Allan Mitchell" wrote: > > > >> You do not need to use an Active Script transform on the face of things > >> unless you really want to. Can you identify new rows? In the > >> SourceSQLStatement of the DataPump task your query would identify the > >> new rows > >> > >> The Row Has a Date Entered > >> The Row has a Date Updated. > >> > >> > >> You could also create a Linked Server of the dBase database and query it > >> as though it was a SQL Server and by comparing it to your SQL Server you > >> could import only the rows you need. > >> > >> Allan > >> > >> > >> "Vishal Sood" <Vishal Sood@discussions.microsoft.com> wrote in message > >> news:Vishal Sood@discussions.microsoft.com: > >> > >> > Hi All, > >> > > >> > I am trying to make an Access Project using SQL Server 2000 DTS > >> > functionalities. > >> > > >> > I have to import selective columns from a table of dbase5 database into > >> > SQL > >> > Server table. Every day, the dbase5 table will have some new rows. For > >> > the > >> > first time, I want to import all rows (abt 50K rows) and subsequently, > >> > I want > >> > the ActiveX Script in DTS to check for only new rows and import them > >> > only, > >> > not the whole recordset. > >> > > >> > I am using Transform Data Task and then using "ActiveX Script" in > >> > Transformation options. > >> > > >> > What code will get this done. Any assistance or pointing to right links > >> > is > >> > highly appreciated. > >> >
You can either import the whole dBase tabe to a staging table in SQL Server
and then use TSQL to do the manipulation or if the incoming rowcount from dBase is not large you can use Lookups. How to Use Lookups in DTS (http://www.sqldts.com/default.aspx?277) -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - You thought DTS was good. here we show you the new stuff. www.konesans.com - Consultancy from the people who know [quoted text, click to view] "Vishal" <Vishal@discussions.microsoft.com> wrote in message news:5832ED07-C3FC-4C1C-81AA-F0F96969A3AD@microsoft.com... > Allan, > > Hi. Thanks. > > In the dbase5 database, there is no field which uniquely identifies new > rows. The only option there is to use a combination of the following 2 > fields > : Date and Time. This combination of 2 fields can make a unique key and I > can > try to retreive records which are newer than a particular date & time. > > I do not know how to write the code to filter on this unique key in > "SourceSQLStatement of the DataPump task". Any hints are welcome. > > Thanks. > > -- > Vishal Sood > > > "Allan Mitchell" wrote: > >> You do not need to use an Active Script transform on the face of things >> unless you really want to. Can you identify new rows? In the >> SourceSQLStatement of the DataPump task your query would identify the >> new rows >> >> The Row Has a Date Entered >> The Row has a Date Updated. >> >> >> You could also create a Linked Server of the dBase database and query it >> as though it was a SQL Server and by comparing it to your SQL Server you >> could import only the rows you need. >> >> Allan >> >> >> "Vishal Sood" <Vishal Sood@discussions.microsoft.com> wrote in message >> news:Vishal Sood@discussions.microsoft.com: >> >> > Hi All, >> > >> > I am trying to make an Access Project using SQL Server 2000 DTS >> > functionalities. >> > >> > I have to import selective columns from a table of dbase5 database into >> > SQL >> > Server table. Every day, the dbase5 table will have some new rows. For >> > the >> > first time, I want to import all rows (abt 50K rows) and subsequently, >> > I want >> > the ActiveX Script in DTS to check for only new rows and import them >> > only, >> > not the whole recordset. >> > >> > I am using Transform Data Task and then using "ActiveX Script" in >> > Transformation options. >> > >> > What code will get this done. Any assistance or pointing to right links >> > is >> > highly appreciated. >> > >> > Thanks.
Whilst you can use an Active Script transform, if the provider for your
source allows it I would use the following SELECT .........................,'' as MyBlankValue.............FROM............... -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.SQLIS.com - You thought DTS was good. here we show you the new stuff. www.konesans.com - Consultancy from the people who know [quoted text, click to view] "Vishal" <Vishal@discussions.microsoft.com> wrote in message news:DAD53088-6050-4642-999C-E5A7CC3A183B@microsoft.com... > Allan, > > Thanks. Great responses and at lightening speed! > > Can I request you to help me out with my queries stage by stage with this > database I am triyng to make. I will write one issue at a time and if you > could tell me if it is right or not, and if not, why? > > Really appreciate your directions till now, even if you are not able to > guide step by step. > > If the answer is yes, here is the first question :- > > The incoming new rowcount is going to a few hundred lines at a time. > Hence, > I am planning to use Lookups. Have already seen the link you sent and have > got some good idea on this. > The source data has only about 12 columns, whereas I want the destination > table to have about 30 columns, which will be a combination of > 1. default values > 2. some blank as well > 3. Lookup columns > 4. One Primary Key column having a 3 digit Alpha code and then an > incremental number of 8 digits, which will be stored in a master table. > > I want to know how to insert a blank column for the destination table for > which there is no column in the source and also the primary key column. > > Trust the way forward in this is to use ActiveX Script Transformations > only? > > -- > Vishal Sood > > "Allan Mitchell" wrote: > >> You can either import the whole dBase tabe to a staging table in SQL >> Server >> and then use TSQL to do the manipulation or if the incoming rowcount from >> dBase is not large you can use Lookups. >> >> How to Use Lookups in DTS >> (http://www.sqldts.com/default.aspx?277) >> >> -- >> >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) >> www.SQLDTS.com - The site for all your DTS needs. >> www.SQLIS.com - You thought DTS was good. here we show you the new >> stuff. >> www.konesans.com - Consultancy from the people who know >> >> >> "Vishal" <Vishal@discussions.microsoft.com> wrote in message >> news:5832ED07-C3FC-4C1C-81AA-F0F96969A3AD@microsoft.com... >> > Allan, >> > >> > Hi. Thanks. >> > >> > In the dbase5 database, there is no field which uniquely identifies new >> > rows. The only option there is to use a combination of the following 2 >> > fields >> > : Date and Time. This combination of 2 fields can make a unique key and >> > I >> > can >> > try to retreive records which are newer than a particular date & time. >> > >> > I do not know how to write the code to filter on this unique key in >> > "SourceSQLStatement of the DataPump task". Any hints are welcome. >> > >> > Thanks. >> > >> > -- >> > Vishal Sood >> > >> > >> > "Allan Mitchell" wrote: >> > >> >> You do not need to use an Active Script transform on the face of >> >> things >> >> unless you really want to. Can you identify new rows? In the >> >> SourceSQLStatement of the DataPump task your query would identify the >> >> new rows >> >> >> >> The Row Has a Date Entered >> >> The Row has a Date Updated. >> >> >> >> >> >> You could also create a Linked Server of the dBase database and query >> >> it >> >> as though it was a SQL Server and by comparing it to your SQL Server >> >> you >> >> could import only the rows you need. >> >> >> >> Allan >> >> >> >> >> >> "Vishal Sood" <Vishal Sood@discussions.microsoft.com> wrote in message >> >> news:Vishal Sood@discussions.microsoft.com: >> >> >> >> > Hi All, >> >> > >> >> > I am trying to make an Access Project using SQL Server 2000 DTS >> >> > functionalities. >> >> > >> >> > I have to import selective columns from a table of dbase5 database >> >> > into >> >> > SQL >> >> > Server table. Every day, the dbase5 table will have some new rows. >> >> > For >> >> > the >> >> > first time, I want to import all rows (abt 50K rows) and >> >> > subsequently, >> >> > I want >> >> > the ActiveX Script in DTS to check for only new rows and import them >> >> > only, >> >> > not the whole recordset. >> >> > >> >> > I am using Transform Data Task and then using "ActiveX Script" in >> >> > Transformation options. >> >> > >> >> > What code will get this done. Any assistance or pointing to right >> >> > links >> >> > is >> >> > highly appreciated. >> >> > >> >> > Thanks.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |