I have the following tables in access and MS SQL Server I would like to split the access table up into two tables and import the data into SQL Server. I tried creating a SSIS package to import the data but I am not sure how to split up the table. Any suggestions on how to split up the table in SSIS or other ways besides SSIS to import the table would be appreciated. Thanks, Access Table: Table Date Driver ProductCode Quantity MS SQL Server Tables: Table 1 PK ID Driver Date Table 2 FK Table1_ID ProductCode Quantity

I would suck the data out of the MS Access table to a staging table in SQL Server and generate an ID to each record that will be subsequently used. Off that staging table I would build a DFT with 2 destinations that would merely have a mapping of one set of columns (e.g. Date & Driver) populating Table1 and the other mapped to populate Table2 both having the same ID column used. Am I clear enough?Arthur My Blog

You could try my normaliser component which does exactly what you're asking for http://consultingblogs.emc.com/jamiethomson/archive/2009/04/23/normaliser-component-v2-ssis.aspx @jamiethttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

I would suck the data out of the MS Access table to a staging table in SQL Server and generate an ID to each record that will be subsequently used. Off that staging table I would build a DFT with 2 destinations that would merely have a mapping of one set of columns (e.g. Date & Driver) populating Table1 and the other mapped to populate Table2 both having the same ID column used. Am I clear enough? Arthur My Blog Why not just generate the ID in the dataflow? It'd save dropping it to disk.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

I would suck the data out of the MS Access table to a staging table in SQL Server and generate an ID to each record that will be subsequently used. Off that staging table I would build a DFT with 2 destinations that would merely have a mapping of one set of columns (e.g. Date & Driver) populating Table1 and the other mapped to populate Table2 both having the same ID column used. Am I clear enough? ... Why not just generate the ID in the dataflow? It'd save dropping it to disk. http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me Agree, a good idea, but I am thinking can be a bit complex (Script Component) or an extra effort to explore for some developers. So just in case here is an example of such: http://www.sql-server-performance.com/faq/ssis_auto_increment_p1.aspx Arthur My Blog

I found a solution. I created a OLE DB source to pull in the data for the master table and i used a script component to add a PK to it then inserted it in the SQL Database. Then I pulled the Details Data from Access and used the Lookup Transformation to match the Date and Driver to get the correct FK for the Details then inserted them into the details table. The only problem I am having is it runs pretty slow but it seems to work. Any other suggestions would be appreciated. Thanks for the replies.

My guess the slowness is due to the use of lookups. Is that true? Where does the package spend its time? If yes, try to use Full Cache Mode if the data is not too large. Then try the partial cache.Arthur My Blog