September 8, 2010 at 10:47 am
After recently upgraded from sql 2000 to sql 2005, I ran into an issue trying to move a VB ActiveX Script DTS package to SSIS. After reading different posts I understand it to be more beneficial to build from scratch in SSIS so I was hoping to do that.
The current package script finds all patients that have had immunizations and assigns a basic ID to each patient (1,2,3,...). From that list of patients two sql queries are ran which would then produce two documents. The first document lists each patient, created ID, and a multitude of demographic data (name, address, DOB,...). The second document lists each patient, the created ID, and immunization data (lot #, brand, ...). With the second document a patient can have multiple rows, one for each immunization they had but the ID still matches the person & ID from the first document.
This is where I need some guidance. I was hoping to create a temp table for the initial list of patients and reference that list; however, I can't get temp tables to work in SSIS. Should I be using a recordset or variable to hold the list of patients, and IDs? I am afraid the only solution is to write the initial set of patients to a file; however I want to avoid creating a third file if at all possible. Or is there a different approach all together that I should try?
I am very new to SSIS and new to this forum as well. Any advice or direction would be greatly appreciated.
Thanks, Maggie
September 8, 2010 at 11:29 am
maggie.blechinger (9/8/2010)
... Should I be using a recordset or variable to hold the list of patients, and IDs? ...
The answer is yes.
Your SQL Script will return a recordset that will be stored in a variable.
Create a variable of type OBJECT to hold your recordset. In your SQL task, set the ResultSet to "Full result set". On the Result Set "tab" assign the result set to the Object variable you created.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 8, 2010 at 11:33 am
Temp tables will work in SSIS, but you have to set the "RetainSameConnection" property of your connection to True. Otherwise new connections will be used for each task and tasks subsequent to the temp table creation will not be able to see it, and/or it will be dropped.
I have also seen it recommended to use ##global temp tables in SSIS, although I have packages that use #local temp tables and they work fine, once the RetainSame Connection property is set.
September 8, 2010 at 1:29 pm
Thanks for the quick responses!!!
I was able to save the result set to an Object variable and run without errors; however, I can't figure out how to reference the variable in another SQL query. The intial query saves a list of createdID (1,2,3,..), and patientID into the object variable {User::ptList}. How would you reference that in the second query?
EXAMPLE 2nd Query:
Select v.createdID, v.person_id, p.first_name, p.last_name, p.date_of_birth
From person p, {User::ptList} v
Where person_id IN (select person_id from {User::ptList})
Not sure if I am anywhere close. It seems like when I try to reference the variable it changes the variable type from Object to String which then produces more errors. Again, your advice is SO helpful!
Thanks,Maggie
September 8, 2010 at 2:36 pm
I don't think you can reference a recordset variable from within a SQL Query - at least it's not something I have ever done. I think recordset variables are generally used for looping operations in SSIS (for each row do something), which I don't think is what you're after.
My suggestion would be to use a temp table (your original plan) to store your initial list with the assigned ID's and then join that in your other two queries. It's straightforward and you already understand it.
Optionally, if your assigned ID's always begin with 1 and your list can be sorted by some unique key field(s), you could use a CTE (Common Table Expression) query and use the Rank() function to assign an ID. Since you have to run this two times, you would have to be certain that the same ID's would be assigned each time using a guaranteed sort order. This approach would avoid the use of a temp table.
I haven't yet used a CTE in an SSIS package, but I think it can be done. Maybe someone else can weigh in on that.
September 9, 2010 at 2:14 pm
I have tried both local and global temp tables and set the RetainSame Connection property to True; however it just hangs when executed? I am so extremely confused now because I don't even have an error to refer to.
First Task- I execute SQL which creates the temp table and assigns the patients & IDs to that table.
Second Task- I execute SQL which references the temp table to run another query.
First task runs great! Second task turns yellow and sits....
Maggie
September 9, 2010 at 2:26 pm
Sorry, I forgot about this -
You also have to set Delay Validation = TRUE on any tasks that use the temp table.
See this article re: temp tables in SSIS
September 10, 2010 at 8:32 am
Just an update...
When I attempt to save initial SQL query data in a object variable, I am unable to reference that to run the next SQL Query. When I reference the object variable it turns it to a string and errors out.
In using temp tables I made sure to set Delay Validation to TRUE for all tasks and the Retain Connection is also set to True. Yet, when I try to reference the temp table (either global or local) in the second query on executing it just hangs.
I have also tried writing the initial results to a txt file but I can't figure out how to have my second query reference the contents of the file. (for each person_id in the file get all demographic info).
I have no clue where to turn next. I have tried all suggestions and for whatever reason none seem to work or I am not doing this right? Is it possible to reference query results to run another query in SSIS? Does anyone have an example maybe? HELP please!
September 10, 2010 at 9:28 am
I do not know why temp tables are not working for you. Maybe it's time you try the CTE approach? Then you wouldn't need a temp table - you would just create 2 dataflows to produce each final document. The data source would contain the CTE query.
It seems that the only reason you need a temp table is so you can create an ID. Is that assumption correct?
Is there something special about the ID, or does it just begin with 1 and increment? If we're just assigning an incrementing number, then the only thing we gotta be careful about is sorting the patient list the same in each dataflow, so if we have 2 Joe Smith's they get assigned the same ID in each dataflow.
So your CTE query in the DataSource would look something like this:
WITH ImmunizationList AS
(SELECT Rank() OVER (ORDER BY Lastname, Firstname, DOB, SSN) as PatientID,
Lastname,
Firstname,
ImmunizationDate,
etc...
FROM your tables)
SELECT lst.PatientID,
mst.Lastname,
mst.firstname,
mst.DOB,
etc...
FROM ImmunizationList lst
INNER JOIN PatientMaster mst
ON your join fields....
September 10, 2010 at 12:43 pm
I was able to get the CTE to work! What a learning experience. Thank you so much for all your help.
September 10, 2010 at 2:12 pm
How do you pass CTE values to next task. I am have same issue and trying to pass this table values to data flow task?
September 10, 2010 at 2:19 pm
What I did was create the CTE at the beginning of the execute SQL query and I was able to write my second query after the CTE within the same task and reference it.
--this is my initial query -CTE (note you need the ";" at the beginning not sure why)
; WITH ImmunizationList AS (SELECT DISTINCT person_id, Rank() OVER (ORDER BY person_id) AS ptNum
FROM person
--this is the second query that references the CTE
SELECT
lst.person_id,
lst.ptNum,
p.first_name,
p.last_name,
p.date_of_birth,
p.home_phone,
FROM ImmunizationList lst
INNER JOIN person p ON lst.person_id = p.person_id
ORDER BY lst.ptNum
It worked very similar to a temp table, which I already was using.
Hope that helps!
September 10, 2010 at 2:27 pm
Hi,
Thanks for your quick response. My first task is Execute SQL Task which returns a table. which i was passing to variable (Data Type: Object ). I am interested in passing in this variable values to Data Flow Task. My Data Flow task is to pass this value to Excel.
Thank you
September 10, 2010 at 2:34 pm
I did not end up using variables as I could not get them to work; however someone else may have better advice on that.
I did notice that I did not explain what I did very well. I did not use the "Execute SQL Task." Instead I used a "Data Flow Task" to an "OLE DB Source" to create the CTE and run the query. I believe from there you should be able to do an "Excel Destination."
September 10, 2010 at 2:44 pm
Hi,
Sounds good. I am gonna try that and see if that works out. Thank you once again.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply