Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 dts

Author  Topic 

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-15 : 06:12:16
hello sir
i have data in excel.
i want to transfer that data in sql server.
in sql i have sectormaster table.
in that table i want that excel data.
pls help me.
gayatri

nr
SQLTeam MVY

12543 Posts
Posted - 2008-01-15 : 06:17:20
Have you tried?
What are you having problems with?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-15 : 07:14:39
sir
first click on enterprise manager.
after that write click on data transformation sevices.and select new package.
then i took connection for Excel and open a file name.
after i took connection for Sql Server and i am used sql sever authentication after that dabase name choosed.
after that i took transform data task.
and set the properties ==
1. Source- Tabel/View -- that is sheet name.
2. Destination - Sql tabel name which is already created.
3. Transformation - arrows between excel and sql data feilds.
4. options - max error control=1000
after that i execute setup
and its successefully execute.
when i click execute symbol on task bar that time also message is successefully execute.
but when i saw the sql table in that table data is not add.
pls help me.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2008-01-15 : 07:22:09
1. Make sure you are connecting to the right database
2. Make sure owner of your table is dbo and not the username you are using in your sql authentication.

Connect to the database using SQL Authentication in query analyzer and see the output of the following two queries:

Select * from dbo.<table-name>
Select * from <table_name>

Note: Replace <table-name> with the name of your destination table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-15 : 07:25:42
Have you had a look at preview of data from excel? Also, check for existence of same table under different schema (dbo & your local ones) as suggested by Harsh.Did you check message for no of rows transferreed?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-15 : 07:31:00
iam using sql authentication i have username password
so dbo is not used
gayatri
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-15 : 07:32:15
yes i had data in excel.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-15 : 08:32:31
what did the message show? Did it showed count of records processed?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 00:18:33
no, message is not display.
successefully executed messeage display.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-16 : 00:27:46
No message in execution pane? where are you executing dts from?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 00:34:23
write click on transfer data task then select execute setup
and message given its successefully execute.
when i click execute symbol on task bar that time also message is successefully execute.
but when i saw the sql table in that table data is not add.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-16 : 00:37:13
dont execute task alone.execute the package as a whole by clicking triangular buton on top. Then it will show execution pane with status of execution each tasks and you can watch for status of data task which will say 'xxxx rows processed'
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 00:50:57
error is 1 task failed during execution.
when i click on ok
then display status=== in that status is error occured(28)
pls help me,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-16 : 01:06:50
Which is task erroring?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 01:17:37
i don't know because there is one dialog box come and on that message is=== 1 task(s)failed during execution.
ok
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-16 : 01:35:12
cant make out what are telling here. Its not at all showing intermediate step statuses? Are you sure you are executing dts pcakage by opening in enterprise manager?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 01:43:51
yes it's sure
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-16 : 01:47:25
Can you just tell us the your sequences of tasks inside dts package?
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-16 : 01:50:37
1.first click on enterprise manager.
2.after that write click on data transformation sevices.and select new package.
3.then i took connection for Excel and open a file name.
4.after i took connection for Sql Server and i am used sql sever authentication after that dabase name choosed.
5.after that i took transform data task.
and set the properties ==
1. Source- Tabel/View -- that is sheet name.
2. Destination - Sql tabel name which is already created.
3. Transformation - arrows between excel and sql data feilds.
4. options - max error control=1000

6.after that i execute setup
and its successefully execute.
7.when i click execute symbol on task bar that time also message is successefully execute.
8.but when i saw the sql table in that table data is not add.
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts
Posted - 2008-01-16 : 07:03:01
Dear Gayatri,
first of all, you are not proceeding corretly...follow these steps and let me know the status...
go to the desired database....there right click and select all tasks.....import data.now you will get import/export wizard.
click on next
there select microsoftExcel97-2000 option.
now it asks for the file name...
now show your file path.click on next
now provide the server, username..password.now select the database.click next
again next(copy tables and views from the source database)
now you will get source and destination options.at the destination option, after (dbo. option)type your desired table name.after that next....run immediatly...
next
finish



that's it.....
please let me know your purpose served or not


Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

Gayatri D.Patil
Yak Posting Veteran

58 Posts
Posted - 2008-01-17 : 08:48:59
hello sir
thanks
but now i have error ihat is--

error during transformation 'DirectCopyXform' for row number 28.
error encountered so far in this task: 1 transformCopy
'DirectCopyXform' conversion error:destination doesnot
allow NULL on column pair 3 (source column 'StatusCode'
(DBTYPE_WSTR),destination column 'StatusCode'(DBTYPE_STR)).
transformCopy'DirectCopyXform' conversion error:destination
doesnot allow NULL on column pair 2 (source column 'DescriptionL'
(DBTYPE_WSTR),destination column 'DescriptionL'(DBTYPE_STR)).
transformCopy'DirectCopyXform' conversion error:destination
doesnot allow NULL on column pair 1 (source column 'SectorCode'
(DBTYPE_R8),destination column 'SectorCode'(DBTYPE_Numeric)).


pls help me.
thanku
Go to Top of Page
  Previous Page&nsp;  Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site