I am creating a text file from another text file and want to copy all the columns except one. This column needs to use it's value to lookup a value in a db that is mapped to the other value and replace it in the new file with the mapped value. I've never used the lookup functionality and can't find any examples. (the documentation is no help)
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DTS lookup - How to change value with lookup
- Thread starter nuVBer
- Start date
I've just had a similar experience, and although I only muddled through, I might be able to help.
From what I can think, you need to use an "Execute SQL Task". Look at the "Parameters" button in there, and you get the option of using global variables as your input or output.
Create a global variable (look in the package properties) and don't worry too much about the data type - I wanted to use a date, but it would only accept a string, but seemed to work anyway (!)
Use your Execute SQL task to run the "lookup a value in a db" (and set the global variable) and then choose a workflow of onSuccess to do the data transform task, using a query, and using the global variable where needed.
I'm sorry if this is impossible to understand, but I hope it gets you started. I know exactly what you mean about the examples - there aren't any, and it's really difficult just to know where to start.
From what I can think, you need to use an "Execute SQL Task". Look at the "Parameters" button in there, and you get the option of using global variables as your input or output.
Create a global variable (look in the package properties) and don't worry too much about the data type - I wanted to use a date, but it would only accept a string, but seemed to work anyway (!)
Use your Execute SQL task to run the "lookup a value in a db" (and set the global variable) and then choose a workflow of onSuccess to do the data transform task, using a query, and using the global variable where needed.
I'm sorry if this is impossible to understand, but I hope it gets you started. I know exactly what you mean about the examples - there aren't any, and it's really difficult just to know where to start.
Another possible error (sorry), instead of a data transform task, you will need to look at Data Driven Query tasks.
This is just the way I have done it, but it seems a very long and complicated way. If any experts have opinions on this, I'd like to hear them!
This is just the way I have done it, but it seems a very long and complicated way. If any experts have opinions on this, I'd like to hear them!
- Thread starter
- #6
Thanks Katy44,
I appreciate your response, but I don't understand why the lookup function won't work in this case. That is what I thought this was designed for. I must be missing something. I looked at your response on the "Data Driven Queries" question and that looks like what I need. I tried using something similat, but it didn't work. I'll keep playing with it, but if I am on the wrong track, please let me know so I don't waste my time. Thanks again!
I appreciate your response, but I don't understand why the lookup function won't work in this case. That is what I thought this was designed for. I must be missing something. I looked at your response on the "Data Driven Queries" question and that looks like what I need. I tried using something similat, but it didn't work. I'll keep playing with it, but if I am on the wrong track, please let me know so I don't waste my time. Thanks again!
Katy44 is correct that you should use a "data driven query" and also a connection for your database where the lookup will be performed.
My concern is that you are using 2 text files (Source/Destination). I've had problems in the past going from text file to text file using the Data Driven Query. I'm not sure it can be done because you need to define both the source and destination and the DDQ has problems recognizing the destination if it's a text file. I'm not sure it was designed to be used this way.
As ugly as it can be, I've use an ActiveX script with the FSO (File System Object) to perform such data conversions when needed. Of course that nullifies the use of the lookup and would require some ADO processing to get the necessary values from the database.
Probably not the answer you wanted to hear.
Hopefully there are other forum members that have a different approach?
My concern is that you are using 2 text files (Source/Destination). I've had problems in the past going from text file to text file using the Data Driven Query. I'm not sure it can be done because you need to define both the source and destination and the DDQ has problems recognizing the destination if it's a text file. I'm not sure it was designed to be used this way.
As ugly as it can be, I've use an ActiveX script with the FSO (File System Object) to perform such data conversions when needed. Of course that nullifies the use of the lookup and would require some ADO processing to get the necessary values from the database.
Probably not the answer you wanted to hear.
Hopefully there are other forum members that have a different approach?
Katy44 is on the right track with the Data Driven Query and the additional connection for the database lookup.
My concern is that you are going from text file to text file. I've had problems doing this with a DDQ since it has a problem using a text file for the destination. I don't believe it was designed for that.
In the past, I've used an ActiveX script and FSO (File System Object) to perform this type of function. Unfortunately you cannot use lookup in this scenerio and will need to use ADO to get the values from the database.
Probably not what you wanted to hear.
Hopefully someone else in this forum has come across a better way to handle this. This is all I can offer for now.
My concern is that you are going from text file to text file. I've had problems doing this with a DDQ since it has a problem using a text file for the destination. I don't believe it was designed for that.
In the past, I've used an ActiveX script and FSO (File System Object) to perform this type of function. Unfortunately you cannot use lookup in this scenerio and will need to use ADO to get the values from the database.
Probably not what you wanted to hear.
Hopefully someone else in this forum has come across a better way to handle this. This is all I can offer for now.
- Thread starter
- #10
Perhaps I misunderstood what a data driven query is. I did get the process to work and I suppose that is what I used not knowing what it was called. I created the lookup witha simple query:
SELECT SAP
FROM account_conversion$
WHERE (Acct = ?)
and created the connection to the db and created a new text file from the existing one. I really just copied all the columns except for the one I needed to convert and used the lookup to do that substitution and it worked great. Thanks for all the help. This is a very helpful forum.
SELECT SAP
FROM account_conversion$
WHERE (Acct = ?)
and created the connection to the db and created a new text file from the existing one. I really just copied all the columns except for the one I needed to convert and used the lookup to do that substitution and it worked great. Thanks for all the help. This is a very helpful forum.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question