Hi,
It's not really possible to get data "out" of the bulk insert operation,
for example the assigned ids. And as the bulk insert processes the rows in
batches rather than individually, it's a bit of a mismatch for what you are
trying to do.
I sometimes find it easier to assign the ProductIds in an etl operation
rather than using the database identities, by querying for the next valid
productId on startup, then just incrementing it. There's a bulk operation
property that tells sql server its okay to insert into the identity column.
That avoids the need to query to get the ids back out after the insert.
Then you can use the batch operation for everything chained as in your
example, or a branching operation that leads to 4 different bulk inserts
(one for products, one for assignment, one for custom fields and one for
shipper).
HTH
Miles
On Friday, December 14, 2012 4:10:28 AM UTC, Chad Angerer wrote:
>
> I am having some trouble doing multiple operations out of a partial
> branching operation. I'm not entirely sure how to approach this problem.
>
> I have a csv file of products that I need to import into my database. I
> also need to assign these products to categories and assign custom fields
> to them. So I need to get the products into the
> database first so I have a productid.
>
> Right now I am running 4 separate EtlProcesses
>
> 1) Insert Products
> 2) Assign products to categories
> 3) Assign custom fields
> 4) Assign drop shipping information
>
> I'd like to combine this into one operation for all products marked as
> "insert" in my branch.
>
> My InsertProducts operation looks something like this.
>
> Register(new ProductJoinSourceToDestination().Left(new
> ReadImportProductsFromFile<ImportProductRecord>(string.Format("{0}\\{1}",
> _localFilePath, _localFileName))).Right(new
> ConventionInputCommandOperation(_connectionString)
> {
> Command = "SELECT sku FROM products"
> }));
> Register(new
> MapImportProductRecordToProductCartSchema(_connectionString));
> Register(new BranchingOperation()
> .Add(Partial
> .Register(new Filter{Action = "insert"})
> .Register(new
> SqlBulkInsertProducts(_connectionString, _destinationTable))) <---
> SqlBulkInsertOperation
> .Add(Partial
> .Register(new Filter{Action = "update"})
> .Register(new
> BatchUpdateProduct(_connectionString)))
> );
>
> I'd like to do something more like the following. This would allow me to
> optimize the sql I am using immensely as I wouldn't have to check if a
> product was already assigned a category, custom fields, etc before doing
> the insert. (This data should not be updated but the ETL process)
> Register(new BranchingOperation()
> .Add(Partial
> .Register(new Filter{Action = "insert"})
> .Register(new
> SqlBulkInsertProducts(_connectionString, _destinationTable)) <---
> SqlBulkInsertOperation
> .Register(new AssignProducts()) <---
> SqlBatchOperation
> .Register(new AssignCustomFields()) <---
> SqlBatchOperation
> .Register(new AssignDropShipperInfo())) <---
> Sql BatchOperation
> .Add(Partial
> .Register(new Filter{Action = "update"})
> .Register(new
> BatchUpdateProduct(_connectionString)))
> );
>
> Right now if I try to do this, only SqlBulkInsertProducts is run. The rest
> of the operations are skipped.
>
> What is a better way to handle this scenario?
>
--
You received this message because you are subscribed to the Google Groups
"Rhino Tools Dev" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
