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

Conflict 'missing key', but the key is there !

Hello,
I use a merge replication and I can see many conflicts (in differents tables) having the same source error. The error message in Conflict viewer says:
The row was inserted at 'Publisher.DataBase' but could not be inserted at 'Subscriber.DataBase'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Table1_Table2'. The conflict occurred in database 'DataBase', table 'Table2', column 'ColKe
y'.
But I can see on Subscriber.DataBase, on Table2 that I have the row with the key that was reported as /missing' !!!
What this could means?
Does it means that SQL try to insert the new lines first in Table1 and after this insert in Table2? Is that possible if I have defined a relation between the 2 tables ?
Thanks in advance
Catalin,
firstly let me confirm:
are the two tables are articles in the same publication
have the PK and the FK been added at the publisher before synchrionization
after synchronization does the PK value make it to the subscriber (assuming
above is true)
Really the order should be OK, although there are known issues if you have
made a lot of changes
(http://support.microsoft.com/default.aspx?scid=kb;[LN];307356). The
recommended solution in the article is to increase
the -UploadGenerationsPerBatch parameter.
Regards,
Paul Ibison

Configuring the code page of a OLEDB source.

hi,

i have a number of interfaces in which i have used oledb source.

the problem i am facing is oledb source components code page is not configurable now if i want to deploy the interface in a different environment which has a database with a different collation it gives a error that oledb source needs new metadata.

has anybody faced this problem earlier.please give me a solution to this problem ..

thanks in advance.

srikanth

Could you change the query to use cast around any varchar columns to force them the same or do you want to keep the collation for accents etc?

Try changing the AlwaysUseDefaultCodePage property to true?