Hi All,
I was hoping you could help me with some questions I've got about SSIS/BID. I'm familiar with the workings of previous versions of DTS, but haven't yet dug into the newer implementation(SSIS/BID) in SS2005 so am having some difficulty determining how I need to go about getting done the things I need done. If written kind of a long description to the problem but hopefully it will give you a solid picture of what I’ve got here.
The Problem: I have a relational db and need to export the data into a flat file with fixed length fields.
Details and Background:
The db is for an online survey that collects information for unemployment benefits claims. The site collects the users SSN, and some various other information.
With the SSN, a comparison is made against the Claimants table as to whether or not the SSN already exists in the system. If so, the existing ClaimantID is returned, else an new claimant record is created, and the new ClaimantID is returned. The Claimants table only contains columns for the identity Key and the SSN.
Individual claimants can submit multiple claims, so once a claimant record is available, a new claim record is inserted into the Claims table, and returns the new ClaimID. The Claims table contains the Claim identity key, the ClaimantID, the ClaimType(a flag showing whether or not the user was preexisting in the system), and the ClaimDate.
All of the survey questions are saved in a table called Questions. Each row in the Question table has an identity key, the Question Text, and some other metadata relating to the question.
All of the answers are saved into a table called Answers. Each row in the Answers table contains the ClaimID, the Question ID, and the AnswerValue.
This is the first time I’ve dealt with a table model like this, where answer values are stored as individual data rows. I’m used to having simply a table with individual columns designated for each question/answer combo and all the data pertaining to a claim is stored on the same row. Though I do understand how this model works and have a pretty decent idea of the method I need to use to get the data back out of the db.
Basically, to get the data back out, I need to do a search for all my claims joined with their respective claimant info(SSN). Then for each claim I need to grab all the Answers records from the db with that ClaimID. Once I have the appropriate Answers recordset, I need to parse through them and lay them out into the export file, in accordance with the predetermined order that has been provided to me by my client.
Like I said, I’m familiar with SQL2000 DTS, and know how I would’ve gone about doing this, but I haven’t worked with SSIS/BID enough yet to know how to transfer that knowledge.
In the previous versions, I would have created an ActiveX script, queried my data, formed my loop of Claims, parsed the answers and written them to a string, then created the file, and written each row manually, but I know using an ActiveX script is antiquated, still available, but antiquated. I’d like to find and use more up-to-date methods.
I imagine that I will still use a similar method, but I don’t know which components to use in BID to accomplish what I need done.
Thank you for reading this far, and any help you can give is appreciated.
Ryan
I was hoping you could help me with some questions I've got about SSIS/BID. I'm familiar with the workings of previous versions of DTS, but haven't yet dug into the newer implementation(SSIS/BID) in SS2005 so am having some difficulty determining how I need to go about getting done the things I need done. If written kind of a long description to the problem but hopefully it will give you a solid picture of what I’ve got here.
The Problem: I have a relational db and need to export the data into a flat file with fixed length fields.
Details and Background:
The db is for an online survey that collects information for unemployment benefits claims. The site collects the users SSN, and some various other information.
With the SSN, a comparison is made against the Claimants table as to whether or not the SSN already exists in the system. If so, the existing ClaimantID is returned, else an new claimant record is created, and the new ClaimantID is returned. The Claimants table only contains columns for the identity Key and the SSN.
Individual claimants can submit multiple claims, so once a claimant record is available, a new claim record is inserted into the Claims table, and returns the new ClaimID. The Claims table contains the Claim identity key, the ClaimantID, the ClaimType(a flag showing whether or not the user was preexisting in the system), and the ClaimDate.
All of the survey questions are saved in a table called Questions. Each row in the Question table has an identity key, the Question Text, and some other metadata relating to the question.
All of the answers are saved into a table called Answers. Each row in the Answers table contains the ClaimID, the Question ID, and the AnswerValue.
This is the first time I’ve dealt with a table model like this, where answer values are stored as individual data rows. I’m used to having simply a table with individual columns designated for each question/answer combo and all the data pertaining to a claim is stored on the same row. Though I do understand how this model works and have a pretty decent idea of the method I need to use to get the data back out of the db.
Basically, to get the data back out, I need to do a search for all my claims joined with their respective claimant info(SSN). Then for each claim I need to grab all the Answers records from the db with that ClaimID. Once I have the appropriate Answers recordset, I need to parse through them and lay them out into the export file, in accordance with the predetermined order that has been provided to me by my client.
Like I said, I’m familiar with SQL2000 DTS, and know how I would’ve gone about doing this, but I haven’t worked with SSIS/BID enough yet to know how to transfer that knowledge.
In the previous versions, I would have created an ActiveX script, queried my data, formed my loop of Claims, parsed the answers and written them to a string, then created the file, and written each row manually, but I know using an ActiveX script is antiquated, still available, but antiquated. I’d like to find and use more up-to-date methods.
I imagine that I will still use a similar method, but I don’t know which components to use in BID to accomplish what I need done.
Thank you for reading this far, and any help you can give is appreciated.
Ryan