July 28, 2009 at 3:13 pm
I'm sorry but the second INSERT into Table1 has me totally confused. First, there is a typo as both instances of Table1 are aliased a. Second, it doesn't appear to me that you will get any columns with a null value in your where clause as it is also the join criteria for the insert and the ID column was populated with the first INSERT statement.
July 28, 2009 at 3:14 pm
The second INSERT statement in your example shows Table1 being outer joined back to itself on it's key column. An outer self-join on the key column with the IS NULL exclusion in the WHERE clause will produce no results. Is this a typo or does your query really look like this?
Is Table1 used outside of this process? What for?
I think to better help you, it's best if you show some sample data. Show me what your staging table looks like (table DDL & sample data), what Table1 looks like (Table DDL & sample data), and what you would expect to happen based off of the sample data.
July 28, 2009 at 3:27 pm
Lynn Pettis (7/28/2009)
I'm sorry but the second INSERT into Table1 has me totally confused.
I'm glad I'm not alone! 🙂
July 28, 2009 at 3:29 pm
Im sorry its a typo.. I just replace the names on word editor.. the first 2 queries will be like this:
-- it will insert the id on Table1 if doesnt exist
INSERT Table1(ID)
SELECT b.ID
FROM BulkTable(staging table)b with(nolock)
left outer join Table1 a with(nolock) on a.ID = b.ID
WHERE a.ID is null
-- it will insert the id and indentity data type on Table2 if doesnt exist
INSERTTable2 (ID, ID2)
SELECTa. ID, 0
FROM Table1 a with(nolock)
left outer join Table2 b with(nolock) on a.ID = b.ID
WHERE a.ID is null
July 28, 2009 at 3:30 pm
Im lucky Im not alone 😛
July 28, 2009 at 3:38 pm
OK, so back to the question of why? What is the purpose of doing this, are you using this data elsewhere?
We're getting close by the way.....
July 28, 2009 at 3:42 pm
Now you added another table to the mix. Is Table2 used else where in this procedure?
July 29, 2009 at 8:26 am
I’m sorry for the delay. What I need to do is to insert information into the Destination table coming from the BulkTable, Table1 and MSO Table (third query). Because I need to join those 3 tables, I need first to add the data that doenst exist in my Table1 (first query). Table 1 and Destination Table has an one to many relationship and the Bulk table contain all the many relationship that it will join with the Table 1.
Don’t pay attention to query 2 because we have a one to one relationship there and we are changing some entities. (this one doesn’t make sense for now).
Am I explaining everything correctly ??? Let me know guys, I really appreciate your help.
July 29, 2009 at 10:21 am
So back to the question at hand, how to duplicate your SP logic within SSIS.
This on is pretty simple. You'll need a data flow with a flat file source adapter configured to read in your flat file contents. I imagine that you've already got this since you are using SSIS to get the data staged.
Now, you need a way to simulate your outer join where you are inserting data into your table Table1 only if it does not exist. A great way to do this would be to use a lookup transformation. Send the red output arrow (normally the error output) to a OLE DB Destination component configured to insert rows into Table1. Upon connecting the red arrow to the destination, you'll be prompted to change the error output behavior. Configure the output to redirect the rows on error. The default behaviour of the lookup trnasformation is to error out when the lookup value is not found, by changing the behavior to redirect non-existant rows, you enable yourself to do whatever you want with those rows. In this case, you want to insert them into Table1.
Now for the destination table insert. Pretty simple, you can just add another data flow to your package. Configure it to read in the same flat file. Configure it to use the same lookup transformation, only this time, you'll want to use the green arrow. Configure the lookup to add the columns from Table1 that you need for your insert statement into the data flow. This is done by 'checking' the columns that you need. Drag the green arrow and connect it to a OLE DB Destination component that you configure to insert rows into your destination table. You should see as you are mapping the columns that your column values from Table1 exist in the data flow and you are able to use them in your insert.
Keep in mind that you'll need a lookup transformation for each of your inner joins in your destination table's INSERT statement. This will allow you to 'get' the column values from those tables that need to be part of the data flow to build your destination insert component mappings.
Make sense? Feel free to try this and then post additional questions.
July 29, 2009 at 12:44 pm
Since you have 400K lines inserted everyday. I would consider doing WEEKLY logical PARTIONS. that way, each partition would have only 2.4Million Lines on a average. Follow these guide lines:
CREATE 52 New filegroups apart from the Primary Group. Lets call them = JAN1, JAN2, JAN3, JAN4, FEB1,FEB2,FEB3,FEB4,......, DEC1,DEC2,DEC3,DEC4.
Make sure these partitions are spread over mulitple physical partitions (Like D:,E:,F:,G:,....).
Now, just use the timestamp to determine the week and place the data in the corresponding logical partition.
let me know, if you have any questions.
July 29, 2009 at 12:44 pm
Since you have 400K lines inserted everyday. I would consider doing WEEKLY logical PARTIONS. that way, each partition would have only 2.4Million Lines on a average. Follow these guide lines:
CREATE 52 New filegroups apart from the Primary Group. Lets call them = JAN1, JAN2, JAN3, JAN4, FEB1,FEB2,FEB3,FEB4,......, DEC1,DEC2,DEC3,DEC4.
Make sure these partitions are spread over mulitple physical partitions (Like D:,E:,F:,G:,....).
Now, just use the timestamp to determine the week and place the data in the corresponding logical partition.
let me know, if you have any questions.
July 29, 2009 at 3:05 pm
thank you John,
I will star working on it first thing in the morning. I really want to make it works 🙂
I already have am OLE DB Destination that connects to the Bulk Table.. I will try everything else you mentioned in your post and I will let you know..
Regards
July 29, 2009 at 3:07 pm
Thank you for the advice..
now, I have a question.. if I improve my SSIS package. Is a reason to still make a partition on my table? or not?
Regards,
July 29, 2009 at 3:09 pm
July 30, 2009 at 6:55 am
Good Morning!
I really got confuse. I'm sure this is pretty easy, but ITs new for me. Here is a print screen of my SSIS. I already have the FlatFile and OLE DB Connection to the Bulk Table. I don't understand how I should connect the lookup and other OLE DB ?? .. Can u guide me ?
Thank you so much 🙂
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply