Help on Migrating from 2000 to 2005- SSIS package

Hi,

I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,

- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables

Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow

-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )

-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client

pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not

Thanks for ur help
Niru

You should be able to build just one package. You might need as many data-flows as there are destination tables. it depends on the situation.

Use precedence constraints to ensure that the data-flows are executed in the correct order according to RI declared on the destination tables.

If you ask more specific questions then more specific answers can be provided.

-Jamie

|||

Thanks Jamie for the clarifying ..yes i think i should create different dataflows instead of different packages .

Here is the scenario which iam working now

I have a table...i have to map some of those fields to the existing 5 tables.

fileds should map like this

source table Destination fileds with tables
(contract)

contract_code --> contract_code (Incident)

Date --> DateId (Dates)
TimeId (Times)

Duration --> Duration (Session)

Ended --> Completion_state(Session) will have options like 'yes','no','unknown'
Problem(session) -- need to set 'true' if completion_state is 'No'

From --> Number(Audit) -- Adding source if it is not existed
Id (Audit) -- giving id if you added target in the above column

To --> Number(Audit) -- Adding Traget if it is not existed
Id (Audit) -- giving id if you added target in the above column

[I will differentiate the difference betweeen 'from' and 'TO' columns in session tables seperatley by refernce]

I think you got some idea what iam trying to do ..like this i have 9-10 tables .

I apperciate if can help me out in this .

Thanks

Niru

Help on error trying to migrate cube AS2000 to AS2005

Hi, all.
I'm using Migrate Database wizard to migrate my cubes from AS2000 to SSAS2005.
It begin to work and I see in the processing window Data sources, Dimensions, Cubes but it finish with the error:

"My OLAP Server;............ Cannot parse the FromClause property."

Someone can help me?
thanks.

Hi,

In AS2000, the FromClause and JoinClause string properties (and sometime an additional filter) were used to specify the SQL source for objects. In AS2005 the DataSourceView object was introduced (with the System.Data.DataSet that has DataTables, DataColumns, DataRelations).

The migration wizard is parsing the FromClause and JoinClause properties, to generate the DataSourceView. Sometimes the parsing of those SQL strings fails and this is the error you have.

Some reasons for parsing failures are legitimate (for example you can manually create an object with DSO and set invalid FromClause and/or JoinClause properties), other reasons are defects in the migration wizard (for example a certain valid SQL syntax was overlooked in the parser).

To find out what is happening in your case, we'll need to see the FromClause. If the FromClause doesn't contain any private information, you can either post it here or send it to me at adumitrascu at hotmail dot com, so I can take a look.

I'll post a message in a bit with a sample DSO code on how to get the FromClause.

Adrian Dumitrascu

|||Dim s As New DSO.Server
Dim db As DSO.Database
Dim c As DSO.Cube

s.Connect "localhost"
Set db = s.MDStores("the name of the database")
Set c = db.Cubes("the name of the cube")

'print out these properties:
' c.FromClause
' c.JoinClause
' c.SourceTable
' c.SourceTableAlias
' c.SourceTableFilter

s.CloseServer
|||

Thank you, Adrian.

I will try to use your sample.

But my database has about 10 cubes; how I can identify on which cube fails the migration wizard ?

by

|||

The error message should specify the object path for which the FromClause could not be parsed. It might be a Dimension, Cube, Partition, MiningModel or MiningColumn. Also, in migration wizard, the error should appear associated with the object (if not, then we'll need to discover it from the path string).

|||

the message "Cannot parse the FromClause property." is preceded from

"My OLAP Server;xxxxx;zzzzz:"

where "xxxxx" is the name of the database that contains my cubes and "zzzzz" is one of the dimensions.

Is "zzzzz" the object to which you refer ?

thank.

|||

Yes, the "zzzzz" is the object; the fragment "My OLAP Server;xxxxx;zzzzz" is a path for an object, starting with the server name, then database name.

|||

Analyzing the "FromClause property" it is emerged that the table to which the dimension refers has a name like:

"￡tablename".

The parser in the migration wizard doesn't support the '￡' character at the start of the table name in dbo"."￡tablename". It would support it in the table name, but not as the start.

Migration wizard will need to be fixed, but meanwhile, a work-around is to create a renamed view on top of "dbo"."￡tablename" and use that view in AS2000.

Thank you Adrian.

Help on error trying to migrate cube AS2000 to AS2005

Hi, all.
I'm using Migrate Database wizard to migrate my cubes from AS2000 to SSAS2005.
It begin to work and I see in the processing window Data sources, Dimensions, Cubes but it finish with the error:

"My OLAP Server;............ Cannot parse the FromClause property."

Someone can help me?
thanks.

Hi,

In AS2000, the FromClause and JoinClause string properties (and sometime an additional filter) were used to specify the SQL source for objects. In AS2005 the DataSourceView object was introduced (with the System.Data.DataSet that has DataTables, DataColumns, DataRelations).

The migration wizard is parsing the FromClause and JoinClause properties, to generate the DataSourceView. Sometimes the parsing of those SQL strings fails and this is the error you have.

Some reasons for parsing failures are legitimate (for example you can manually create an object with DSO and set invalid FromClause and/or JoinClause properties), other reasons are defects in the migration wizard (for example a certain valid SQL syntax was overlooked in the parser).

To find out what is happening in your case, we'll need to see the FromClause. If the FromClause doesn't contain any private information, you can either post it here or send it to me at adumitrascu at hotmail dot com, so I can take a look.

I'll post a message in a bit with a sample DSO code on how to get the FromClause.

Adrian Dumitrascu

|||Dim s As New DSO.Server
Dim db As DSO.Database
Dim c As DSO.Cube

s.Connect "localhost"
Set db = s.MDStores("the name of the database")
Set c = db.Cubes("the name of the cube")

'print out these properties:
' c.FromClause
' c.JoinClause
' c.SourceTable
' c.SourceTableAlias
' c.SourceTableFilter

s.CloseServer
|||

Thank you, Adrian.

I will try to use your sample.

But my database has about 10 cubes; how I can identify on which cube fails the migration wizard ?

by

|||

The error message should specify the object path for which the FromClause could not be parsed. It might be a Dimension, Cube, Partition, MiningModel or MiningColumn. Also, in migration wizard, the error should appear associated with the object (if not, then we'll need to discover it from the path string).

|||

the message "Cannot parse the FromClause property." is preceded from

"My OLAP Server;xxxxx;zzzzz:"

where "xxxxx" is the name of the database that contains my cubes and "zzzzz" is one of the dimensions.

Is "zzzzz" the object to which you refer ?

thank.

|||

Yes, the "zzzzz" is the object; the fragment "My OLAP Server;xxxxx;zzzzz" is a path for an object, starting with the server name, then database name.

|||

Analyzing the "FromClause property" it is emerged that the table to which the dimension refers has a name like:

"￡tablename".

The parser in the migration wizard doesn't support the '￡' character at the start of the table name in dbo"."￡tablename". It would support it in the table name, but not as the start.

Migration wizard will need to be fixed, but meanwhile, a work-around is to create a renamed view on top of "dbo"."￡tablename" and use that view in AS2000.

Thank you Adrian.

sql

Help Needed ! , Data Migration from MS Access2003 to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.

Need Help!

Thanks

Mahesh

Hi Mahesh,

SSMA is a right tool for migration

http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en and refer discussion at TechNet for troubleshooting http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.access.sqlupsizing&cat=en_us_FB980B1A-C148-9C19-560F-548B843A67AB&lang=en&cr=us

and refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

help me to solve this

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.