Showing posts with label lookup. Show all posts
Showing posts with label lookup. Show all posts

Friday, March 23, 2012

Help with an error message

I keep getting this error message when I try to restore a database backup that I have made.

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without it affecting my actual database. This is where the error messages are coming from.

Please help

Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

Thanks for your help

|||Hi,

look in your BOL (Books online, the Online help of SQL Server)

There is a point Restore a database and move Files which will help you

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE 'MyAdvWorks' TO
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'MyAdvWorksLog1'
TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, March 9, 2012

Help using Lookup Transformation

Hi All,

I am doing something really simple and it doesnt work, may be I am missing something, What I am trying to accomplish is to load a fact table using lookup transaformation, however my source data was different from the data in my dimension (or the datatype ) I had to use a data conversion task before my lookup , so the data flow is something like this source -> Data Conversion -> Lookup -> destination , I am getting an error at my lookup task where it says the "[Lookup [82]] Error: Row yielded no match during lookup". and then it just fails. I know for sure that there has to be matching data. donno what is it that I am missing.

Thanks

Things to look out for...

Trailing spaces on either side of the lookup (source data or lookup data). If one has trailing spaces and the other doesn't, it won't match.

CaSE. The lookups are case sensitive.|||

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

|||

db_guy wrote:

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

Redirect the errors (error output, red arrow) to a flat file source, OLE DB destination, or even a Row Count Transformation, and add a data viewer to inspect the data that does not match.|||

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

|||

db_guy wrote:

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

Add a data viewer to the red arrow connecting the lookup to the row count. (Double click on it and select data viewers, then add a new data viewer)|||

Hi,

I have attached data viewers and one thing I noticed is they behave in a weird way, sometimes they have 0 records and sometimes they have data, donno why.I have three lookups that I use , and then finally they all load the fact table

Thanks

|||Connect all lookup error flows to a Union All component, and then to a row counter. Add the data viewer between the union all and the row counter. This will let you see all of the lookup error outputs.

Or, just hook each lookup up to their own row counter and add data viewers there. You're data is getting caught up somewhere.|||

I think using the data viewers I have narrowed down the problem thanks for that, however I have another question how do I do lookup where I have a join which is something like this

INNER JOIN dbo.Dim_Time T WITH (NOLOCK)

ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(s.Fiscal_Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(s.Fiscal_Time,6,2)

The problem is I was using derived column to create/replace the stage column using the substring function , however I cannot do that twice because derived column cannot be used twice for the same column, any suggestions?

Thanks

|||

Where did you get that you can't use a derived column twice for the same column?

If you are having a problem with your lookup you can always use a Source / merge join transform, or you can go to the advanced tab of the Lookup transform and select enable memory restriction and type in your exact query with parameters and line it up that way (This is much slower than using it in the regular lookup mode)

If you were to give more info we could probably tell you the exact steps needed to complete this process.

(also, if your original question was answered, remember to mark it as such, along with all other applicable answers)

Help using Lookup

Hi

I am trying to use lookup to see if a item esists in my table ( 3 key fields ). If the lookup fails I want to insert the records. If it succeeds I have put a recordcount to catch the items that are not required. I don't think that I understand the settings for failed rows. I have tried setting the Configure Error Output to redirect, but this does not seem to work. I have the below errors.

[SQL Server Destination [151]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: component "SQL Server Destination" (151) failed the pre-execute phase and returned error code 0xC0202071.

Can someone please advise me how to set up this component to work for my application

Thanks

ADG

SQL Server Destinations only work on the machine that have the actual SQL Server installed locally. Is this the case?|||

Hi Phil

I have SQL Server 2005 Developer Edition installed on a stand alone machine at the moment. Previous two task flows use a SQL Server Destination (same table) and work OK.

I did not know that I could not use a SQL Server Destination on a network. Eventually I will migrate my solution to one of our group servers, currently I am developing the solution, or rather battling to learn SQL server. I guess that maybe I should use another type of destination once the above bug is ironed out.

Regards

ADG

|||

Bug fixed

I deleted the Look up and set it up again and all is well. Not sure what i did first time, ( I fiddled with too many settings I suspect

Help using a value from one dataset to lookup a value from a second dataset

Hi All,

I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.

I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.

Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?

There are a couple of solutions for you:

1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.

2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).

Hope that helps,

-Lukasz

Sunday, February 19, 2012

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".


Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman