| Groups | Blog | Home |
sql server dts : Inserting to a Partitioned view in SSIS
schema. This was originally implemented in SQL 2000. I upgraded to SQL 2005 Standard Edition and am now trying to write an SSIS package to load data from text files into these tables. Since each file would load into only one partition, I would like to dynamically determine which underlying table to insert. I don't know how to do this. I would like to take advantage of the metadata the SQL Server Destination component stores to do BULK copies. I tried inserting directly into the partitoned view, but SSIS gives me the error "Views referencing tables on multiple servers are not updatable in the edition of this SQL Server". The tables all exist on the same server. Is there 1) a way to dynamically declare the table in a SQL Server Destination component? or 2) figure out why SQL Server thinks these tables exist on different servers? I'd prefer 1, but I'd take 2 or any other advice anyone has for me. Thank you, Dan Barkman DBA
OK, I answered my own question. I believe you have to use the OLE DB
Destination to use a variable for the table name. I am worried about the performance hit of this, however. If anyone knows how to use a variable for a table name in the SQL Server Destination, I'd appreciate it. Also, does anyone know how severe the performance hit is between using the OLE DB and SQL Server Destinations? Dan Barkman DBA Redwood Trust, Inc. [quoted text, click to view] "daniel_barkman" wrote:
> I have a Partitoned view that UNIONs 65 underlying tables with the same > schema. This was originally implemented in SQL 2000. I upgraded to SQL 2005 > Standard Edition and am now trying to write an SSIS package to load data from > text files into these tables. > > Since each file would load into only one partition, I would like to > dynamically determine which underlying table to insert. I don't know how to > do this. I would like to take advantage of the metadata the SQL Server > Destination component stores to do BULK copies. > > I tried inserting directly into the partitoned view, but SSIS gives me the > error "Views referencing tables on multiple servers are not updatable in the > edition of this SQL Server". The tables all exist on the same server. > > Is there > 1) a way to dynamically declare the table in a SQL Server Destination > component? > or > 2) figure out why SQL Server thinks these tables exist on different servers? > > I'd prefer 1, but I'd take 2 or any other advice anyone has for me. > > Thank you, > Dan Barkman > DBA
The ETL does sit on the same server being inserted to, and all tables in the
partitioned view are local. Is there anywhere I can look to see why SQL Server thinks the tables may be on another server? I believe this is the only way I can use the SQL Server Destination component, since you seem to be agreeing that I cannot designate a target SQL table at runtime for a SQL Server Destination. Is my understanding correct? [quoted text, click to view] "Allan Mitchell" wrote:
> Hello daniel_barkman, > > > The SQL Server destination is special in that it moves within the SQL Server > memory space and is very very quick. That said the ETL must sit on the same > server as the SQL Server into which you are inserting to do this. As to > how severe the difference will be, well that is one of those "It depends" > questions. > > I would test and see. > > > > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > OK, I answered my own question. I believe you have to use the OLE DB > > Destination to use a variable for the table name. > > > > I am worried about the performance hit of this, however. > > > > If anyone knows how to use a variable for a table name in the SQL > > Server Destination, I'd appreciate it. Also, does anyone know how > > severe the performance hit is between using the OLE DB and SQL Server > > Destinations? > > > > Dan Barkman > > DBA > > Redwood Trust, Inc. > > "daniel_barkman" wrote: > > > >> I have a Partitoned view that UNIONs 65 underlying tables with the > >> same schema. This was originally implemented in SQL 2000. I upgraded > >> to SQL 2005 Standard Edition and am now trying to write an SSIS > >> package to load data from text files into these tables. > >> > >> Since each file would load into only one partition, I would like to > >> dynamically determine which underlying table to insert. I don't know > >> how to do this. I would like to take advantage of the metadata the > >> SQL Server Destination component stores to do BULK copies. > >> > >> I tried inserting directly into the partitoned view, but SSIS gives > >> me the error "Views referencing tables on multiple servers are not > >> updatable in the edition of this SQL Server". The tables all exist on > >> the same server. > >> > >> Is there > >> 1) a way to dynamically declare the table in a SQL Server Destination > >> component? > >> or > >> 2) figure out why SQL Server thinks these tables exist on different > >> servers? > >> I'd prefer 1, but I'd take 2 or any other advice anyone has for me. > >> > >> Thank you, > >> Dan Barkman > >> DBA > >> Redwood Trust, Inc. > >
I tried inserting directly into the partitoned view, but SSIS gives me the
error "Views referencing tables on multiple servers are not updatable in the edition of this SQL Server". I take this to mean SQL thinks my tables are on multiple servers. But they aren't - they're all local - as is my SSIS environment. Dan [quoted text, click to view] "Allan Mitchell" wrote:
> Hello daniel_barkman, > > Why do you think SSIS thinks the tables do not reside locally to the package. > Remember if it is you on your wkstn executing the package then the SQL Server > has to be there as well. > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > The ETL does sit on the same server being inserted to, and all tables > > in the partitioned view are local. Is there anywhere I can look to see > > why SQL Server thinks the tables may be on another server? I believe > > this is the only way I can use the SQL Server Destination component, > > since you seem to be agreeing that I cannot designate a target SQL > > table at runtime for a SQL Server Destination. > > > > Is my understanding correct? > > > > "Allan Mitchell" wrote: > > > >> Hello daniel_barkman, > >> > >> The SQL Server destination is special in that it moves within the SQL > >> Server memory space and is very very quick. That said the ETL must > >> sit on the same server as the SQL Server into which you are inserting > >> to do this. As to how severe the difference will be, well that is > >> one of those "It depends" questions. > >> > >> I would test and see. > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> OK, I answered my own question. I believe you have to use the OLE DB > >>> Destination to use a variable for the table name. > >>> > >>> I am worried about the performance hit of this, however. > >>> > >>> If anyone knows how to use a variable for a table name in the SQL > >>> Server Destination, I'd appreciate it. Also, does anyone know how > >>> severe the performance hit is between using the OLE DB and SQL > >>> Server Destinations? > >>> > >>> Dan Barkman > >>> DBA > >>> Redwood Trust, Inc. > >>> "daniel_barkman" wrote: > >>>> I have a Partitoned view that UNIONs 65 underlying tables with the > >>>> same schema. This was originally implemented in SQL 2000. I > >>>> upgraded to SQL 2005 Standard Edition and am now trying to write an > >>>> SSIS package to load data from text files into these tables. > >>>> > >>>> Since each file would load into only one partition, I would like to > >>>> dynamically determine which underlying table to insert. I don't > >>>> know how to do this. I would like to take advantage of the metadata > >>>> the SQL Server Destination component stores to do BULK copies. > >>>> > >>>> I tried inserting directly into the partitoned view, but SSIS gives > >>>> me the error "Views referencing tables on multiple servers are not > >>>> updatable in the edition of this SQL Server". The tables all exist > >>>> on the same server. > >>>> > >>>> Is there > >>>> 1) a way to dynamically declare the table in a SQL Server > >>>> Destination > >>>> component? > >>>> or > >>>> 2) figure out why SQL Server thinks these tables exist on different > >>>> servers? > >>>> I'd prefer 1, but I'd take 2 or any other advice anyone has for me. > >>>> Thank you, > >>>> Dan Barkman > >>>> DBA > >>>> Redwood Trust, Inc. > >
Hello daniel_barkman,
The SQL Server destination is special in that it moves within the SQL Server memory space and is very very quick. That said the ETL must sit on the same server as the SQL Server into which you are inserting to do this. As to how severe the difference will be, well that is one of those "It depends" questions. I would test and see. -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > OK, I answered my own question. I believe you have to use the OLE DB > Destination to use a variable for the table name. > > I am worried about the performance hit of this, however. > > If anyone knows how to use a variable for a table name in the SQL > Server Destination, I'd appreciate it. Also, does anyone know how > severe the performance hit is between using the OLE DB and SQL Server > Destinations? > > Dan Barkman > DBA > Redwood Trust, Inc. > "daniel_barkman" wrote: > >> I have a Partitoned view that UNIONs 65 underlying tables with the >> same schema. This was originally implemented in SQL 2000. I upgraded >> to SQL 2005 Standard Edition and am now trying to write an SSIS >> package to load data from text files into these tables. >> >> Since each file would load into only one partition, I would like to >> dynamically determine which underlying table to insert. I don't know >> how to do this. I would like to take advantage of the metadata the >> SQL Server Destination component stores to do BULK copies. >> >> I tried inserting directly into the partitoned view, but SSIS gives >> me the error "Views referencing tables on multiple servers are not >> updatable in the edition of this SQL Server". The tables all exist on >> the same server. >> >> Is there >> 1) a way to dynamically declare the table in a SQL Server Destination >> component? >> or >> 2) figure out why SQL Server thinks these tables exist on different >> servers? >> I'd prefer 1, but I'd take 2 or any other advice anyone has for me. >> >> Thank you, >> Dan Barkman >> DBA >> Redwood Trust, Inc.
Hello daniel_barkman,
Why do you think SSIS thinks the tables do not reside locally to the package. Remember if it is you on your wkstn executing the package then the SQL Server has to be there as well. -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > The ETL does sit on the same server being inserted to, and all tables > in the partitioned view are local. Is there anywhere I can look to see > why SQL Server thinks the tables may be on another server? I believe > this is the only way I can use the SQL Server Destination component, > since you seem to be agreeing that I cannot designate a target SQL > table at runtime for a SQL Server Destination. > > Is my understanding correct? > > "Allan Mitchell" wrote: > >> Hello daniel_barkman, >> >> The SQL Server destination is special in that it moves within the SQL >> Server memory space and is very very quick. That said the ETL must >> sit on the same server as the SQL Server into which you are inserting >> to do this. As to how severe the difference will be, well that is >> one of those "It depends" questions. >> >> I would test and see. >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> OK, I answered my own question. I believe you have to use the OLE DB >>> Destination to use a variable for the table name. >>> >>> I am worried about the performance hit of this, however. >>> >>> If anyone knows how to use a variable for a table name in the SQL >>> Server Destination, I'd appreciate it. Also, does anyone know how >>> severe the performance hit is between using the OLE DB and SQL >>> Server Destinations? >>> >>> Dan Barkman >>> DBA >>> Redwood Trust, Inc. >>> "daniel_barkman" wrote: >>>> I have a Partitoned view that UNIONs 65 underlying tables with the >>>> same schema. This was originally implemented in SQL 2000. I >>>> upgraded to SQL 2005 Standard Edition and am now trying to write an >>>> SSIS package to load data from text files into these tables. >>>> >>>> Since each file would load into only one partition, I would like to >>>> dynamically determine which underlying table to insert. I don't >>>> know how to do this. I would like to take advantage of the metadata >>>> the SQL Server Destination component stores to do BULK copies. >>>> >>>> I tried inserting directly into the partitoned view, but SSIS gives >>>> me the error "Views referencing tables on multiple servers are not >>>> updatable in the edition of this SQL Server". The tables all exist >>>> on the same server. >>>> >>>> Is there >>>> 1) a way to dynamically declare the table in a SQL Server >>>> Destination >>>> component? >>>> or >>>> 2) figure out why SQL Server thinks these tables exist on different >>>> servers? >>>> I'd prefer 1, but I'd take 2 or any other advice anyone has for me. >>>> Thank you, >>>> Dan Barkman >>>> DBA >>>> Redwood Trust, Inc
Hello daniel_barkman,
What edition of SQL Server are you running (EE or DE?)? it looks like something thinks it is a DPV. This wouldn't be an SSIS thing this is an engine thing as you should get the same problem using other tools Can you select from the DPV/PV? -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > I tried inserting directly into the partitoned view, but SSIS gives me > the error "Views referencing tables on multiple servers are not > updatable in the edition of this SQL Server". > > I take this to mean SQL thinks my tables are on multiple servers. But > they aren't - they're all local - as is my SSIS environment. > > Dan > > "Allan Mitchell" wrote: > >> Hello daniel_barkman, >> >> Why do you think SSIS thinks the tables do not reside locally to the >> package. Remember if it is you on your wkstn executing the package >> then the SQL Server has to be there as well. >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> The ETL does sit on the same server being inserted to, and all >>> tables in the partitioned view are local. Is there anywhere I can >>> look to see why SQL Server thinks the tables may be on another >>> server? I believe this is the only way I can use the SQL Server >>> Destination component, since you seem to be agreeing that I cannot >>> designate a target SQL table at runtime for a SQL Server >>> Destination. >>> >>> Is my understanding correct? >>> >>> "Allan Mitchell" wrote: >>> >>>> Hello daniel_barkman, >>>> >>>> The SQL Server destination is special in that it moves within the >>>> SQL Server memory space and is very very quick. That said the ETL >>>> must sit on the same server as the SQL Server into which you are >>>> inserting to do this. As to how severe the difference will be, >>>> well that is one of those "It depends" questions. >>>> >>>> I would test and see. >>>> >>>> -- >>>> >>>> Allan Mitchell >>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>> http://www.sqldts.com >>>> | >>>> http://www.konesans.com >>>>> OK, I answered my own question. I believe you have to use the OLE >>>>> DB Destination to use a variable for the table name. >>>>> >>>>> I am worried about the performance hit of this, however. >>>>> >>>>> If anyone knows how to use a variable for a table name in the SQL >>>>> Server Destination, I'd appreciate it. Also, does anyone know how >>>>> severe the performance hit is between using the OLE DB and SQL >>>>> Server Destinations? >>>>> >>>>> Dan Barkman >>>>> DBA >>>>> Redwood Trust, Inc. >>>>> "daniel_barkman" wrote: >>>>>> I have a Partitoned view that UNIONs 65 underlying tables with >>>>>> the same schema. This was originally implemented in SQL 2000. I >>>>>> upgraded to SQL 2005 Standard Edition and am now trying to write >>>>>> an SSIS package to load data from text files into these tables. >>>>>> >>>>>> Since each file would load into only one partition, I would like >>>>>> to dynamically determine which underlying table to insert. I >>>>>> don't know how to do this. I would like to take advantage of the >>>>>> metadata the SQL Server Destination component stores to do BULK >>>>>> copies. >>>>>> >>>>>> I tried inserting directly into the partitoned view, but SSIS >>>>>> gives me the error "Views referencing tables on multiple servers >>>>>> are not updatable in the edition of this SQL Server". The tables >>>>>> all exist on the same server. >>>>>> >>>>>> Is there >>>>>> 1) a way to dynamically declare the table in a SQL Server >>>>>> Destination >>>>>> component? >>>>>> or >>>>>> 2) figure out why SQL Server thinks these tables exist on >>>>>> different >>>>>> servers? >>>>>> I'd prefer 1, but I'd take 2 or any other advice anyone has for >>>>>> me. >>>>>> Thank you, >>>>>> Dan Barkman >>>>>> DBA >>>>>> Redwood Trust, Inc.
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 |