|
|
You're in the
sql server dts
group:Retrieving Data from a database, filtered by a local Table
sql server dts:
On Wed, 29 Jun 2005 22:59:10 +0200, muc80339@mnet-online.de wrote: > Hello, > i want to get data from a table, but only the data that is specified > (dynamically) with a filter that is stored in a local table. > > SQL-Statement > SELECT * FROM external_table1 > INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 > > How do I get this in my dts-package if i only can have one reference to > external_table1? > > Thankx. > > Frank
A number of ways really but here is my preferred way
Use an ExecuteSQL task to get those filter values and put them into Global Variables. With those variables if your source provider accepts it you can now use the values in the SourceSQL Statement property of the datapump task by using this method 1. Use ? as a placeholder in the statement (select ... FROM ... WHERE col = ?) and using the parameters button map onto the ? , a global variable If not then you can still do this 1. Use an active Script task to dynamically build the SourceSQLStatement. Global Variables and SQL statements in DTS (http://www.sqldts.com/default.aspx?205) -- 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] <muc80339@mnet-online.de> wrote in message news:d9v22s$98l$1@svr7.m-online.net... > Hello, > i want to get data from a table, but only the data that is specified > (dynamically) with a filter that is stored in a local table. > > SQL-Statement > SELECT * FROM external_table1 > INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 > > How do I get this in my dts-package if i only can have one reference to > external_table1? > > Thankx. > > Frank >
Hello,
i want to get data from a table, but only the data that is specified (dynamically) with a filter that is stored in a local table. SQL-Statement SELECT * FROM external_table1 INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 How do I get this in my dts-package if i only can have one reference to external_table1? Thankx. Frank
I will try today with the global variable, I think the upload is not
possible because the source system is a SAP System. "Ross Presser" <rpresser@NOSPAMgmail.com.invalid> schrieb im Newsbeitrag news:j0zm98knmzl2.1gmlhcmwi7w56.dlg@40tude.net... [quoted text, click to view] > On Wed, 29 Jun 2005 22:59:10 +0200, muc80339@mnet-online.de wrote: > >> Hello, >> i want to get data from a table, but only the data that is specified >> (dynamically) with a filter that is stored in a local table. >> >> SQL-Statement >> SELECT * FROM external_table1 >> INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 >> >> How do I get this in my dts-package if i only can have one reference to >> external_table1? >> >> Thankx. >> >> Frank > > Upload your local table to a #temp table?
Thnx.
I did as you described, but I have now the problem that my Table contains more than one value. What do I have to do with: SELECT.... WHERE col IN ? It reports Access Violation... [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:u5peVEPfFHA.1148@TK2MSFTNGP12.phx.gbl... > A number of ways really but here is my preferred way > > Use an ExecuteSQL task to get those filter values and put them into Global > Variables. > > With those variables if your source provider accepts it you can now use the > values in the SourceSQL Statement property of the datapump task by using > this method > > 1. Use ? as a placeholder in the statement (select ... FROM ... WHERE col = > ?) and using the parameters button map onto the ? , a global variable > > If not then you can still do this > > 1. Use an active Script task to dynamically build the SourceSQLStatement. > > Global Variables and SQL statements in DTS > (http://www.sqldts.com/default.aspx?205) > > -- > > > > 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 > > > <muc80339@mnet-online.de> wrote in message > news:d9v22s$98l$1@svr7.m-online.net... > > Hello, > > i want to get data from a table, but only the data that is specified > > (dynamically) with a filter that is stored in a local table. > > > > SQL-Statement > > SELECT * FROM external_table1 > > INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 > > > > How do I get this in my dts-package if i only can have one reference to > > external_table1? > > > > Thankx. > > > > Frank > > > >
You can't do it like this
You will need to build up your statement then if you want to use IN Here is an article theat will get you cooking Global Variables and SQL statements in DTS (http://www.sqldts.com/default.aspx?205) -- 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] "Frank Mayer" <frankmayer@gmx.de> wrote in message news:da0p9i$9nk$1@news.mch.sbs.de... > Thnx. > I did as you described, but I have now the problem that my Table contains > more than one value. > What do I have to do with: > > SELECT.... WHERE col IN ? > > It reports Access Violation... > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:u5peVEPfFHA.1148@TK2MSFTNGP12.phx.gbl... >> A number of ways really but here is my preferred way >> >> Use an ExecuteSQL task to get those filter values and put them into >> Global >> Variables. >> >> With those variables if your source provider accepts it you can now use > the >> values in the SourceSQL Statement property of the datapump task by using >> this method >> >> 1. Use ? as a placeholder in the statement (select ... FROM ... WHERE >> col > = >> ?) and using the parameters button map onto the ? , a global variable >> >> If not then you can still do this >> >> 1. Use an active Script task to dynamically build the >> SourceSQLStatement. >> >> Global Variables and SQL statements in DTS >> (http://www.sqldts.com/default.aspx?205) >> >> -- >> >> >> >> 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 >> >> >> <muc80339@mnet-online.de> wrote in message >> news:d9v22s$98l$1@svr7.m-online.net... >> > Hello, >> > i want to get data from a table, but only the data that is specified >> > (dynamically) with a filter that is stored in a local table. >> > >> > SQL-Statement >> > SELECT * FROM external_table1 >> > INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 >> > >> > How do I get this in my dts-package if i only can have one reference to >> > external_table1? >> > >> > Thankx. >> > >> > Frank >> > >> >> > >
Hello,
thanks. Problem is still: sSQLStatement = "SELECT PSPNR, PSPID, POST1, POSKI, POSID FROM PROJ WHERE PSPID IN (SELECT Filter_Projects FROM Flt_Proj)" OR sSQLStatement = "SELECT PSPNR, PSPID, POST1, POSKI, POSID FROM PROJ WHERE PSPID IN ('" & DTSGlobalVariables("FilterProject") & "')" The GlobalVariable contains a rowset of a SQL Statement that is executed before... Errors ( I Think) Possibility 1 the sub-query does not contain data --> no datarecords chosen Possibility 2 Error-Message Type mismatch [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:u5lpB3afFHA.2472@TK2MSFTNGP15.phx.gbl... > You can't do it like this > > You will need to build up your statement then if you want to use IN > > Here is an article theat will get you cooking > > Global Variables and SQL statements in DTS > (http://www.sqldts.com/default.aspx?205) > > -- > > > > 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 > > > "Frank Mayer" <frankmayer@gmx.de> wrote in message > news:da0p9i$9nk$1@news.mch.sbs.de... > > Thnx. > > I did as you described, but I have now the problem that my Table contains > > more than one value. > > What do I have to do with: > > > > SELECT.... WHERE col IN ? > > > > It reports Access Violation... > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > news:u5peVEPfFHA.1148@TK2MSFTNGP12.phx.gbl... > >> A number of ways really but here is my preferred way > >> > >> Use an ExecuteSQL task to get those filter values and put them into > >> Global > >> Variables. > >> > >> With those variables if your source provider accepts it you can now use > > the > >> values in the SourceSQL Statement property of the datapump task by using > >> this method > >> > >> 1. Use ? as a placeholder in the statement (select ... FROM ... WHERE > >> col > > = > >> ?) and using the parameters button map onto the ? , a global variable > >> > >> If not then you can still do this > >> > >> 1. Use an active Script task to dynamically build the > >> SourceSQLStatement. > >> > >> Global Variables and SQL statements in DTS > >> (http://www.sqldts.com/default.aspx?205) > >> > >> -- > >> > >> > >> > >> 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 > >> > >> > >> <muc80339@mnet-online.de> wrote in message > >> news:d9v22s$98l$1@svr7.m-online.net... > >> > Hello, > >> > i want to get data from a table, but only the data that is specified > >> > (dynamically) with a filter that is stored in a local table. > >> > > >> > SQL-Statement > >> > SELECT * FROM external_table1 > >> > INNER JOIN internal_table2 ON table1.datafield1 = table2.datafield2 > >> > > >> > How do I get this in my dts-package if i only can have one reference to > >> > external_table1? > >> > > >> > Thankx. > >> > > >> > Frank > >> > > >> > >> > > > > > >
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 |