Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.

Data integration challenge. Will SSIS help?

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

One possible option is creating Stored Procedures using the managed code ( thats created by mapforce using c#). And using SSIS to execute those SPs and exact data.

Performance you can achive using SSIS will be much better than that with Biztalk.|||

JGP wrote:

Hi,

We have a requirement to do ETL on large EDI batch files into SQL Server. For this we have to map several X12 schemas - which are fairly complex - into relational schema. Right now they use Biztalk but that is not a good option for batch processing of large EDI files. Performance is really bad.

Is this possible using SSIS? Are there any 3rd party tools which might integrate with SSIS to make this easier or can do this as a stand-alone?

I know Mapforce 2005 from Altova supports X12 mapping and even generates C# code for the mapping. Anyone has experience in using Mapforce generated C# code in SSIS?

Looking forward to any insights.

Thanks,
JGP

This is script source component or custom source adapter territory. I recommend Don Farmer's book "Rational Guide To Scripting" which contains a step-by-step guide to building a script source component.

-Jamie|||Anyone have any real-world experiences doing something like this? Any links or samples you can share?

Thanks,
JGP|||Are you using biztalk 2002 or 2004

Given the complexity of EDI files and the parsing of these, I don't think SSIS is the mechanism.
You would need a custom adapter, and it would be complicated.
EDI generally feeds a business process which is what Biztalk fits.

My view of SSIS is that is the process of flows of data not of transactions of data which is how I view EDI.

There is of course a blur between the 2 and so it depends on what your process is, and what you are doing with the EDI files.

Personally I would stick with biztalk. Not sure if there is anything new/improved in 2006 that might help|||Its actually one of our clients...they're currently using Biztalk 2000.

Couldn't the complexity be reduced by using Mapforce 2005 which now supports EDI? What are your thoughts on using that with SSIS?

The process is basically doing ETL of EDI flat files into relational schema with little bit of validation and code cleansing. In Biztalk they use vbscript functiods which are a real hit on performance...especially since the EDI flat files are fairly large.

Thanks,
JGP|||I am sorry but I have no experience of Mapforce. I would suggest looking at Biztalk 2004 or even 2006, Biztalk 2000 was awful at handling large files.

I always rate using the same toolset, Biztalk, SSIS all use Visual Studio. It might be over kill but using the Biztalk as the mapper. However looking at mapforce it might also be suitable, not sure what the interface is to it.

It does depend on what the vbscript is doing, it might be easier doing it in Biztalk. You also need to be outputing data that can be consumed by SSIS, a flat file or an XML file with data at one level i.e. not hiearchical.|||Did you have any luck with this? I'm trying to use SSIS for the same thing, now, and could sure use an example.