Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

problem with images

I have a problem with images being displayed in a report when I select the
image source coming from the web.
The URL I am specifing is
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
In the layout i can see the image, in the preview i cannot nor in the
deployed report
when i navigate to this page in a browser the image is shown ok
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
I have downloaded service pack 1 which is supposed to resolve this problem
Can anyone help?
Thanks
RichardWell after much searching through the net for similar problems I finally
found the answer.
My page
http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
returns a progressive jpeg with the content type image/pjpeg
Report services cannot handle this content type. As soon as I changed it to
image/jpeg all works well
Microsoft - This is a bug and it took me at least 8 hours of stress and hair
pulling!
Thanks
Richard
"Richard Wilde" <XXXXinfo@.rippo.co.ukXXXX> wrote in message
news:OJ9IOykHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> I have a problem with images being displayed in a report when I select the
> image source coming from the web.
> The URL I am specifing is
> http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
> In the layout i can see the image, in the preview i cannot nor in the
> deployed report
> when i navigate to this page in a browser the image is shown ok
> http://localhost/Scimitar/media/thumbnail.aspx?lmediaid=1742
> I have downloaded service pack 1 which is supposed to resolve this problem
> Can anyone help?
> Thanks
> Richard
>

problem with identifier lengths when connecting Analysis Services to Oracle

Hi All,

I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:

Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0

Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.

I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.

Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?

Any help would be appreciated.

Kind regards,

Miles

You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>

<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

Restart the server/tools

Note that this is not a supported technique but should unblock you...

HTH,

Akshai

|||

Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)

For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.

Kind regards,

Miles

|||Dear All,

I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.

The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||

Hi 4lb3rt,

Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.

Cheers,

Miles

|||Dear Miles,

I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?

Thanks in advance.sql

problem with identifier lengths when connecting Analysis Services to Oracle

Hi All,

I'm currently trying to process a cube that is built off a DSV that points to an Oracle 10g data source. However, I keep getting errors akin to the following:

Error 1 OLE DB error: OLE DB or ODBC error: An error occurred while preparing the query " SELECT "Transaction Type" "CstOrdsTransaction_x0020_Type0_0" FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"" for execution against OLE DB provider "MSDAORA.1" for linked server "(null)". ; 42000. 0 0

Some attributes process successfully and others don't. I have noticed that the error occurs only when the generated identifier that it uses to alias the column exceeds 30 characters in length, eg "CstOrdsTransaction_x0020_Type0_0". When I paste the query that Analysis Services is trying to run into PL/SQL Developer, it gives me a "ORA-00972: identifier is too long" error.

I have already ripped through all the tables in my DSV changing the "logical name" in the XML to something shorter (eg "CstOrds" instead of "Customer_Orders") - however, if possible I'd like to avoid the hassle of having to shorten all my column names just to get around this restriction. Then I would have to edit them in my dimension attributes to give them "friendly" names for the user to see, which is a lot of hassle.

Is there any good way to control the identifiers that Analysis Services uses to alias the column names when it processes my cube, such that it doesn't exceed Oracle's 30 character limit for identifiers?

Any help would be appreciated.

Kind regards,

Miles

You can change the Oracle cartridge - orcl7.xsl - (it’s in 2 locations, for both server and tools) to use less characters (like 15, for example). See these settings:

<mssqlcrt:limit-table-identifier-length>29</mssqlcrt:limit-table-identifier-length>

<mssqlcrt:limit-column-identifier-length>29</mssqlcrt:limit-column-identifier-length>

Restart the server/tools

Note that this is not a supported technique but should unblock you...

HTH,

Akshai

|||

Brilliant, thanks for the help Akshai. I've made the change and my cube's processing now... I guess I'll know in a few hours whether or not it worked, as the data I'm processing is huge and the source DB is a couple of continents away at the other end of a VPN ;-)

For anyone else experiencing the same issue - the locations of orcl7.xsl in a default installation of SQL server 2005 are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Thanks again for the suggestion Akshai - I knew there must have been a sneaky trick to get around this rather than the horribly clunky XML hacking I was doing in my DSV, Cubes and Dimensions as a workaround.

Kind regards,

Miles

|||Dear All,

I'm having the same problem with Miles. After I edit orcl7.xsl (I change from 30 to 55), and when I try to design the aggregations for the cube it works. But when I want to process the cube an error message appear "Identifier too long", so now I can't process my cube. Before I change the orcl7.xsl I can process the cube. I'm developing the cube using SSAS 2005 64 bit on 64 bit server (x64). The Oracle on the same server with SSAS. I'm already install the oracle client.

The locations of orcl7.xsl in a default installation of SQL server 2005 64 bit are;

c:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\Cartridges\orcl7.xsl

for the server, and;

c:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\orcl7.xsl

for visual studio.

Can you please help me ? I'm looking forward to hear from you. Thanks in advance.
|||

Hi 4lb3rt,

Quick question - do you have a SQL server (or other) data source in your project as well as the oracle one? I did, and that was actually my problem. It seems that my DSV was using the SQL server connection as it's default. Therefore it was actually running all the oracle queries as distributed queries through SQL server. Not only did that make it all horribly slow, but it also caused the error described in the above post. The eventual solution was to get rid of the SQL connection altogether as I didn't need that any more, and ensure that the oracle connection was the default for the DSV. Hope this helps in your case.

Cheers,

Miles

|||Dear Miles,

I don't have another data source in my project except Oracle. FYI, I'm using "Oracle Provider for OLE DB" for the data source provider. Can you please help me ?

Thanks in advance.|||I did not have this problem (identifier too long) until I tried to design aggregations with multiple partitions. None of my Oracle data exceeds the 30 character limit, so I'm kinda lost. It is new however, since I added some additional columns to my sql for the dsv and the partitions, but they do not exceed 30! I can process, I just can't design aggregations and I sure would like to. If anyone has any more ideas, let me know. AS 2005.

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with DTS Source using Pervasive SQL 2000 (Btrieve) ODBC driver

I am trying to use a DTS package to extract data from an old Pervasive SQL 2000 (Btrieve) data source, and copy into an MS SQL Server table.

I have installed the Pervasive ODBC driver and got linked tables in Access working. I can see the data.

When I create the DTS package, it shows a list of the tables in the drop-down, but when I hit the Preview button I get an unspecified error. I can finish setting up the DTS package, the new MS SQL Sever table gets created, and the field transformations all look correct. However, when I execute the package it fails. In the logs, I get an unspecified error, the only error code I get in log is: 80074005.

Please help.

Thanks!

This is a SSIS forum. Try the DTS newsgroup instead: http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.dts

-Jamie

|||

Carson,

Feel free to email me at tcarcieri@.rihousing.com and I can walk you through what I did. In addition, you wouldn't happen to know if there is a way to only install Pervasive 2000i ODBC drivers on a server would you? I do not want to install the whole pervasive app to do so nor do I want to buy drivers.

THanks,
Tony

Problem with datatypes...

Dear Friends,

I have a problem with datatypes....

The error is "Cannot convert between unicode and non-unicode..."

The column in source is nvarchar(32) and in the destination is varchar(32)... ok...

But in the DataReader Source in the SQLCommand I've converted the column into varchar(32) with

convert(varchar(32), Equities.Equities_Name) as CL_FOLDER_ID

Do you have any tip?

Thanks!

Look at the actual output column for the Source, and you will see that that is DT_WSTR, the unicode string type in SSIS. The DR source does not support non-unicode strings, everything becomes a .Net string in the ADO.Net provider world, and and then SSIS will always treat these as DT_WSTR. If you used the OLE-DB Source through, you get support for both DT_STR and DT_WSTR, mapped from nvarchar and varchar respectively.

So in summary since your source will always give you DT_WSTR, you will need to use a conversion transform inside the pipeline, see Derived Column or Data Conversion, either will do.

|||

Darren,

I used the conversion, but I i tryied to use in SQL command, but if it's not possible, OK... I will use the conversion transform...

Thanks!!