importing to swl server from a flat file PK problem

I am trying to import into sql server from a .csv flat file i made in excel. The problem is the Primary Key. It is set to auto increment.
When i leave an empty column i get:
"Directcopyxform conversion error:destination does not allow null"

when i just omit the column entirely, i get a wrong datatype error because it basically tries to copy all the columns 1 shift to the left

How am i supposed to represent and auto PK in .csv file. ThanksUse a staging table. Where you can load the information to, perform any processing, then insert into your table. Finally cleaning out the staging table at the end.

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing data from one source in two destination tables linked by a foreign key

Hi,

I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :

table MOTHER (MOTHER_ID, MOTHER_NAME)

table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)

After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.

My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".

The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...

Is there a solution to chain two insertions with a foreign key constraint in a data flow?

Thanks

Regards,

Arnaud Gervais.

Just create two data flows in your control flow. The first to load the mother table. The second to then load the child table.|||

I can do that but I have a unique source file and with this solution I have to do same transformations of my data 2 times...

I've found a better solution that seems to be often used: http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx

It consists on creating two data flow and chaining them on the control flow as following:

- first data flow : I transform data, insert them in the mother table and keep the data flow in a raw file using multicast component.

- second data flow : I read the raw file to get transformed data, and find the foreign key with a lookup component bound to mother table previously imported.

It seems to be the best and fastest solution to do that...

Thanks,

Arnaud.

|||

it sounds like you are talking about the same thing at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=918028&SiteID=1

Jamie Thomson has posted feedback asking for "Intrinsic Flow Priority" to be included in SSIS. This would mean you could achieve what you are trying to do in a single data flow task.

Please go to (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058) and vote for this as well as posting your use case.