Connect to a DataSet Variable as a Source?

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

MilwaukeeCharlie wrote:

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

Charlie,

You're absolutely correct, there is no source adapter for these and with good reason too.

There is a workaround though:

Recordsets instead of raw files

(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx)

Another option (and the one I would use for performance reasons as explained here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) is to use raw files

By the way, the correct nomenclature is "components", not "widgets"

Good luck!

-Jamie

Confusion over datasets, lists, first(

I'm trying to build a report in VS2005. The main dataset (DSCases) for
the report is just "Select * from LawCases where Docket = ?"
It should only return one case. So I dragged a few fields onto the
report body, and VS kept making the expression First(Fields!...)
instead of just Fields!. I don't know why but I don't know much about
SSRS so that isn't too surprising.
I used a list with that dataset as the DataSetName and then I could
get rid of the Firsts. Fine.
However I need some other tables mixed in, so I created another
dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
which should get me the various attorneys who worked on the case. Then
I added a list inside the main list, set the DataSetName to the
DSAttorneys, and VS insists on using First again. And, indeed, I do
get the first attorney, but I need them all. It won't compile without
the First though.
I hoped that the inside list would understand that it would repeat
those fields for each record. I looked at filters and grouping but
can't get it to work as I expect.
Do I need to use subreports instead of a list?As you saw you can't just put a field on the layout surface. It has to be
bound to a list or a table. If you know there is only going to be one record
returned then drag and drop the fields and use the First aggregate. Then
drag the list over (or the table object, whichever works best). Otherwise
use a subreport.
What is happening is RS does not know it is a single row, how can it. So for
a list within a list it would need to be joining the data (like a
master-detail). For instance a list of orders and a list of items for each
order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:0i3pr39o6so20jdih2k8p1lrbpsep6mt37@.4ax.com...
> I'm trying to build a report in VS2005. The main dataset (DSCases) for
> the report is just "Select * from LawCases where Docket = ?"
> It should only return one case. So I dragged a few fields onto the
> report body, and VS kept making the expression First(Fields!...)
> instead of just Fields!. I don't know why but I don't know much about
> SSRS so that isn't too surprising.
> I used a list with that dataset as the DataSetName and then I could
> get rid of the Firsts. Fine.
> However I need some other tables mixed in, so I created another
> dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
> which should get me the various attorneys who worked on the case. Then
> I added a list inside the main list, set the DataSetName to the
> DSAttorneys, and VS insists on using First again. And, indeed, I do
> get the first attorney, but I need them all. It won't compile without
> the First though.
> I hoped that the inside list would understand that it would repeat
> those fields for each record. I looked at filters and grouping but
> can't get it to work as I expect.
> Do I need to use subreports instead of a list?|||On Wed, 20 Feb 2008 15:06:49 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>As you saw you can't just put a field on the layout surface. It has to be
>bound to a list or a table. If you know there is only going to be one record
>returned then drag and drop the fields and use the First aggregate. Then
>drag the list over (or the table object, whichever works best). Otherwise
>use a subreport.
>What is happening is RS does not know it is a single row, how can it. So for
>a list within a list it would need to be joining the data (like a
>master-detail). For instance a list of orders and a list of items for each
>order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Thanks. There will be quite a few different detail records for each
master (at least four other tables) so I can't even think of how to
join them all. Subreports certainly seems the way to go. I'll give it
a shot.

configuring dataset dynamically

HI ,

I am using SQL Server 2005 Reporting Services. I have many parameters to pass to the dataset. Is there a way to change the dataset dynamically based on the parameters selected?

Suppose If param1 is selected, I want to use dataset1 and if param 2 is selected. I want to use dataset2 and so on... in my reports.

Any help is greately appreicated!

Thanks in advance!

Have you tried to use an expression in your dataset "Query String" window?

Example:

iif(param1 <> nothing,exec proc1,exec proc2)

|||

It is better to handle it in your stored procedure which takes all necessary parameters.

Shyam

|||

Hi Simone,

I have handled this problem in my stored procedure on SQL Server. I have created separate procedures and created a master procedure and called other sub procedures inside this master procedure based on the parameters selected on the UI. I couldn't think of this approach until you suggested to handle it in dataset expression. Since I have many parametes to handle, dataset expression didn't work for me but the idea helped me to figure out other solution.

Thank you so much for your help!

|||

Hi Shyam Sundar,

I have tried it in dataset expression as Simone mentioned and didn't work and tried in stored procedure. I agree it was better to handle it in stored procedure.

Thank you very much for suggestion.

|||Can you pls mark my post as answer?|||I did. Thanks again for your help.

configuring dataset dynamically

HI ,

I am using SQL Server 2005 Reporting Services. I have many parameters to pass to the dataset. Is there a way to change the dataset dynamically based on the parameters selected?

Suppose If param1 is selected, I want to use dataset1 and if param 2 is selected. I want to use dataset2 and so on... in my reports.

Any help is greately appreicated!

Thanks in advance!

Have you tried to use an expression in your dataset "Query String" window?

Example:

iif(param1 <> nothing,exec proc1,exec proc2)

|||

It is better to handle it in your stored procedure which takes all necessary parameters.

Shyam

|||

Hi Simone,

I have handled this problem in my stored procedure on SQL Server. I have created separate procedures and created a master procedure and called other sub procedures inside this master procedure based on the parameters selected on the UI. I couldn't think of this approach until you suggested to handle it in dataset expression. Since I have many parametes to handle, dataset expression didn't work for me but the idea helped me to figure out other solution.

Thank you so much for your help!

|||

Hi Shyam Sundar,

I have tried it in dataset expression as Simone mentioned and didn't work and tried in stored procedure. I agree it was better to handle it in stored procedure.

Thank you very much for suggestion.

|||Can you pls mark my post as answer?|||I did. Thanks again for your help.