Losing decimal places on Sql Svr to Sql Svr data flow

Hi,

I have a staging table that has a float [DT_R8] column that shows 4 decimal places. When I use an OLE DB Source referencing that table to go to an OLE DB Destination referencing a table with an identical column the data gets rounded to a single decimal place. Needless to say this is really messing with the values.

I can see the Scale property in the Advanced Editor for OLE DB Destination but I cannot change it. Same for the OLE DB Source.

Oh, and if I do an insert using SQL in Management Studio I have no problem getting the 4 decimal places in. For example:

Insert into table2
(Col1, Col2)
select Col1, Col2 from table1

Moves all the data and keeps the 4 decimal places.

How do I do this without losing those decimals?

Thanks
John
Do you have the SSIS datatypes set to DT_R8? Double click on the green line coming out of the OLE DB source and in the metadata tab, what's the data type set to?

(Just asking the perhaps obvious question for completeness.)|||The datatype for that column is DT_R8 in the OLE DB Source (both External and Output columns), OLE DB Destination (both External and Input columns) and in the green line metadata.

John
|||Works fine for me. Maybe you've got some old metadata stuck somewhere. Try putting a Data Viewer on the pipeline at various points (if there are various points) to see what it looks like in flight. Maybe you need to delete and recreate some components or the whole data flow to get it straightened out. What method/query are you using to pull the data from the source? What type of connections are you using?
|||While recreating the data flow, I checked the view and it appears that the problem is happening just before the package executes.

Thanks for all the help.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Lookup Transformation: How can I join tables in different databases

I want to join tables that reside in different databases (same instance). The Lookup object only lets me select from one data source. Is there anyway to lookup using more than one data source? I can write a SQL query to lookup across databases.

Is this a feature that is being added to future releases?

I appreciate your help

-Marcus
Are these SQL Server databases? If so you can create a view in one database that selects data from another - thus making it appear as though the data is all in the same DB.

Voila!

-Jamie|||lol... didn't think of that one...

Thanks :)|||Still onthe lookup subject, I have noticed that if I write a simpley query in the box "User results of an SQL query" say select * from DB1.dbo.tablea, DB2.dbo.tableb, I can then click the "Build Query" button and hey presto both tables are then available for me to work with even thought they are from different databases. I do notice that the top left corner of the table boxes have an arrow.

However if I go straight to "Build Query", add my first table by right clicking and selecting "add table" there is no arrow in the top left corner of the table box. I can then modify the sql statment manually to include the table from the other database. This table then appears in the top window with an arrow in the top left corner of it's box.

Is this a bug? should there actually be an optin to add a table from an alternative database and it's missing? As detailed above I can manually add the tables and the tool recognisines them.

Has anyone else seen this? Has this been fixed in later builds? I'm using Junes.

Thanks|||

What you have observed is not a bug.

We do supply an option to add tables. But we only list tables in the current database context. Tables in other databases have to be added manually. This is the behavior in June CTP and are not changed since then.

|||

Do you know if this will be changed in future CTP's?

Thanks

|||

I do not think so.

But please feel free to open a DCR via BetaPlace.

|||What about MS Access using DAO? In DAO you use something like this.

CDaoRecordset rset(&db);
rset.Open(dbOpenSnapshot, SQLquery, dbReadOnly);

This does not allow for binding to more than one database at the time. Therefore, how can you do a join query between two tables residing in different databases?

Thank you.

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup transformation based on a csv file

My lookup data is in a csv file, not a table. Is there a way to get the Lookup transformation to use the csv file as the source 'table'? Obviously the alternative is to load the file into a SQL Server table and use that, but I want to keep it simple if possible.

The lookup component can only use OLE DB sources. You might be able to construct a query with OPENROWSET to read the text file via OLE DB, but that doesn't seem like the simple solution. You might also be able to use a Merge Join in Left Outer mode instead of a Lookup, which would let your source the right side from a flat file. You may have to add some sorting steps, though.
|||

The technique that I prefer for this situation is to have a separate package (or a separate data flow within the same package) that loads the lookup data from the source CSV into a lookup table in SQL Server (or whatever is your RDBMS of choice) and then perform the lookup in the "main" package against the database table version of the lookup data. If you ensure that the CSV to Table package is always executed first, this should solve the problem neatly.

Although this is not quite as simple as the ideal situation where the Lookup transform supported non-OLE DB data sources, it is much simpler (and much better performing) than any other alternative that I can think of.

Lookup transform not finding blank match

I am having problems with a lookup transformation. I have a row in my lookup table for blank ('') source data. If I test the join using SQL the match is made, but the Lookup transform doesn't consider it a match and sends it to error output. Is there a property that I don't have set correctly or something else I am forgetting?

Check that you are not using empty string lookup against spaced "empty" strings or vice versa.

|||

I created the lookup table (dimension) and prepared the source table so I do know they are both empty strings.

Let me give a little more information. I have a package that runs prior to the fact table load (lookups). The package runs through the source and adds any dimension records that are not present. The problem arises when the lookup encounters an empty string from the source and it is unable to match on the empty string record already in the dim table, so it sends the record to the error output to be written to the dim table. But the write fails because the field is a primary key in the dim table and because the record exists it raises an error. In other words the Lookup transform can't see the empty string record, but the Write Transform can.

I hope that this explanation is understandable.

Dave

|||

Dave,

I've noticed when I pull back a string from tables, the string is padded with spaces to the full size of the field.

Try two things to see if one of these might be your problem:

1) After you pull in your columns from the source, add a derived column that concatenates a single character (like 'a') before and after the column that is return the empty string. Use a data viewer after this column, to see if the column looks like this: 'aa' or this 'a a'.

If it's the latter, change your derived column to trim the string, or trim the column when pulling it from the source.

2) In your initial lookup where you are trying to see if the empty string ('') exists, try using a SQL statement that selects the columns you are looking up. In your SQL statement, concatenate a single character in the same manner as step 1 to the empty string column and add it to your output. Then use a data viewer after this column to see what the column looks like.

If it's 'a a', trim your column in your SQL statement before returning it.

Good luck!

Jessica

|||

Like Jessica said, use the dataviewer to check the data.

SSIS is using .net string compare on strings during lookup process, which think '' and ' ' is different, but sql is ignoring trailing empty spaces during select joins.

If your oledb command insert statement check if the data exist before insert, then you may always insert ' ' into the database but always ignore the ''.

|||

Check out

http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx

especially the comments at the end - you're not alone :-)

Cheers/Kristian

Lookup Transform

Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?The lookup must select against your table with demo_id in it... We need more information here... If you can't find demo_id in your lookup table, and you need it to be there, well then you probably have the wrong table defined in your lookup.|||

Not sure what your problem is. In general, Lookup transform can be basedon a table or on a query; if you don't see an specifc column in the Column is because that column is not on that table/query.

Could you provide more details on how you have set up your package.

BTW, Have you considered to just include the lookup table as a part of the OLE DB Source query and not use the lookup transform at all?

|||Ihave a select customer.salary, customer.occupation in source DB. These should be Lookup columns matched with the id from dim_demography. How?|||The source has nothing to do with the lookup.

In the lookup, you need to specify a lookup table or query. When that's done, then you map columns from the dataflow (your source) to columns in the lookup table. You'll have to have an ID column coming from the source query though.

I'm sorry, but your response didn't say anything more clear than your original post.|||How would you do if you had a demo_id in db Destination, which should be matched with the values salary and occupation in Dim_demographic (containing columns id, salary, occupation) or de db Source sql customer.salary, customer.occupation?|||Please provide your schema:

Table1:
Column1
Column2
....

Table2:
Column1
Column2
...

We'll go from there.|||OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr

Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id

Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...
|||

curiousss wrote:

OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr
Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id
Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...

Use a lookup transform with a query like:

Take the output of the OLE DB Source component to a Lookup transform. Inside of the lookup write a query like:

Select dim_demographic_id, salary, occupation
From Dim_demographic

Then in the columns tab draw a line to join Salary and occupation; in the bottom part, choose id from the dropdown list to bring the dim_demographic_id (from dim_demographic) to the pipe line. Now when you connect the pipeline to the destination component you should have that extra column; all you have to do is to create the mapping between thedestination column demo_id and the column in the pipeline.

BTW, it is better to provide a query inside the lookup tranform than select the tablename from the list...it save resources(memory) improvng performance.

|||Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".|||The OLE DB destination preview shows you what's in the destination table, not what's in the data flow. You have nothing in your table, hence nothing shows up in the preview.|||

curiousss wrote:

Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Ok Few things here:

The preview in the OLE DB Components shows data that is already in that table; if this is your initial load, the table is empty, then the preview will show nothing.

The first message you in the execution error is warning you about duplicates in the Dim_Demographics; meaning, there is more than one row for a combination of salary and occupation. This is treated as a warning as SSIS will use any of those values; so be careful on that. Provide a query and joins that retrieves unique values.

The other 2 messages are actually errors; check the data types of the source and destination for salesperson_is column; they have to be the same.

|||Hi!
Thank you.. its getting late...wouldn't this eliminate duplicates?
SELECT DISTINCT id, salary, occupation
From Dim_demographic|||by the way.. is there any logic/rule/order when changing data types (just shows error now)

Purchase Source both salesperson_id are DT_18

Purchase Destination salesperson_id Input DT_18
Purchase Destination salesperson_id external DT_14

...Dim_salesperson salesperson_id is DT-14|||

I don't think the Distinct will eliminate the duplicates as each row (I guess) has a unique Id value.

To convert data types there is a Data conversion tranform in the toolbox of the data flow.

Lookup task with NOLOCK

I have an SSIS package that unpivots data - each row from the source becomes 20-24 rows in the destination db. In the dataflow it looks up some type-data IDs before inserting into the destination table. The whole process flies through the data at an incredible rate, however I find that the final commit on data insertion takes a very long time, and two of the lookup tasks remain yellow throughout. It appears that there may be a contention issue because the final table has FKs to those lookup tables.

In T-SQL it is possible to do SELECT..... WITH (NOLOCK), but when I modified the SQL statement of the lookup task by adding this clause, it caused an error "Incorrect syntax near the keywork 'with'."

Is there any way around this problem? THNX!

PhilSky

You could split the data-flow into 2, using raw files to pass data between them. This would alleviate blocking problems (if indeed that is the issue).

Incidentally, I have a request for enhancements to the LOOKUP component that means this would be even easier. Feel free to vote for these enhancements here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=58d0100b-aa34-4191-ae03-7655543614a1 and leave a comment as well if you could.

-Jamie

|||I've tried that and it works fine what is your SQL statement you are trying to use. The other option is to change the transaction isolation level for the dataflow

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.
|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?
Thank you

|||

He's saying that you could either do it in source extract query like this

Code Blockselect

....
,
case

when col1='small' then 's'

when col1='medium' then 'm'

end

from
....

|||

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")
|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:
Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.

|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Blockcol1==""small"" ? ""s"" : col1 || col1==""medium"" ? ""m"" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.

|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?

Thank you

|||

He's saying that you could either do it in source extract query like this

Code Block

select

....

,

case

when col1='small' then 's'

when col1='medium' then 'm'

end

from

....

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.|||

I'll try it out and let you know how it went!

Lookup table, extracting values

I need some help with the following...

My data source has some columns I have to 'translate' first and then insert into my destination table.

Example Source data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

has to become

1 'Y' 'P'

2 'N' 'D'

I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest

Example:

col_name vl_source vl_dest

size 'Small' 'Y'

size 'Big' 'N'

height 'Tall' 'P'

... and so on, I believe you get the point

How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?

You either need to do it in your source when you do the query, or do n lookups or embed the information in a derived column component.

Unfortunately there is no Multi lookup component.

|||

what do you exactly mean by 'embed the information in a derived column component'? Could you elaborate?

Thank you

|||

He's saying that you could either do it in source extract query like this

Code Block

select

....

,

case

when col1='small' then 's'

when col1='medium' then 'm'

end

from

....

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

|||You can use multiple lookup transformations in a row to get what you need. Create that physical table and then it'll work. Add a lookup for each column you want to translate.

Then in each lookup, you'll filter your SQL query against "col_name". So for exampe, "select vl_source, vl_dest from conversion_table where col_name = 'size'")|||

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

|||

Sara4 wrote:

Thank you, Phil, that's exactly what I've done.

It'd be great if derived column could 'take' select statements so it could be used instead of n lookups in this case...

Per the example above, you can do CASE logic inside the derived column, but then it'll be harder to maintain that your table approach.|||

Anthony Martin wrote:

or embeded in the derived column component like this

Code Block

col1=="small" ? "s" : col1 || col1=="medium" ? "m" : col1

Thank you for the explanation. We need to keep our decodifying dynamic (in the future small may become smallx and some new values could be entered - storing the values in a derived column would mean modifying the SSIS too).

It seems that for now the only (quick) solution is creating as many lookups as there are columns that need to be decodified.

Thank you all!

|||Sara,
You can unpivot your data as well and run it through one lookup.

So your data:

key size height

1 'Small' 'Tall'

2 'Big' 'Short'

Would become:
key column value
1 'size' 'Small'
1 'height' 'Tall'
2 'size' 'Big'
2 'height' 'Short'

This would allow you to use one lookup table. Then you'd need to pivot your data back, using the returned values from the lookup.

Takes more time to setup, but it could be worth it for you.|||

I'll try it out and let you know how it went!

LOOKUP component does not have AlwaysUseDefaultCodePage property

Is there any reason for this?
It doesn't make sense to me. After all, we're hitting OLE DB sources with it - if the OLE DB Source component has this property, why not LOOKUP component?

-JamieHi Jamie,

The problem here is that this particular property was added very late to the OLEDB source, furthermore, the lookup component handles its sql statement quite differently from the oledb source so it wasn't trivial to add it (not that it was trivial for the oledb source either its just that the oledb source was written in a way that was considerably more straightforward to add it than for the lookup).

HTH,
Matt|||OK Matt, thanks.

I've raised something via the product feedback centre about this for the future.

-Jamie

Lookup as Inner join ?

Hello all.....

I got a doubt whether we can use Lookup as an inner join.
If I have duplicate keys in lookup source....can I get the duplicate rows also into the destination.

Ex:
source:
c1 c2 c3

1 a1 b1
2 a2 b2
3 a3 b3
lookup table:
c1 c4 c5

1 c1 d1
2 c21 c22
2 c23 c24
3 c3 c3

noe the result should be:
c1 c2 c3 c4 c5
-- -- -- -- --
1 a1 b1 c1 d1
2 a2 b2 c21 c22
2 a2 b2 c23 c24
3 a3 b3 c3 c3

in msdn I read the look up will get the first match.....is it possible to get all matches?

Thank you

No, you can't get multiple matches from the Lookup. You should use the Merge Join component to do that.
|||

Dear friend,

use the merge Join, and you can specify LEFT, INNER or RIGHT JOIN!

Helped?

regards!

|||

yes I know about the merge join componant. but for that I need to sort the data before joining. and till now I used my source table which has 6 million records only once and with the multicast I generated all the neccessary fact tables. and to generated one more facts table I need to make an inner join with another table. that's why I asked whether there is any setting where we can get all the matches. I can also use the merge join here....and to sort this 6 M records in the middle of the package is taking all the RAM and time.

I can't sort them in the starting because this table is alredy sorted with some other columns for merge join

input.

anyways thank you for your help. If it's not possible with lookup...I need to do something else. I think I need to read the table again with needed keys sorted.

Thank you.

Looking for DTS example

Hi all,
I am trying to automate calling a SQL Copy Data task. I need to provide the
source database dynamically at the least. I am using MSDE in a C3 program.
Does anyone have any examples or sites they can point me to? I am at a loss
after much searching.
ThanksMe too.
Apparently exporting a DTS to VB gives you the option of actually
controlling the DTS batch more, but you need Visual Basic installed (dunno
if it'll run in a VBA environment).
I've tried a DTS storage file & metadata services, the DTS storage file is
in binary and I can't get metadata services working just now...
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"S" <spamaway@.hotmail.com> wrote in message
news:OG5F71TqDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am trying to automate calling a SQL Copy Data task. I need to provide
the
> source database dynamically at the least. I am using MSDE in a C3
program.
> Does anyone have any examples or sites they can point me to? I am at a
loss
> after much searching.
> Thanks
>