Exporting DTS jobs?

SQL Server 2000.

Hey there,

I'm trying to set up a test environment. My projuction environemtn has a bunch of Scheduled Jobs which run either stored procedures or DTS jobs.

I've exported the SP's and the Scheduled jobs but I'm wondering if there is any way to migrate the DTS jobs?

Cheers...open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.|||Me being the lazy bum that I am, I'd use DTSBackup (http://www.sqldts.com/default.aspx?242). I've got develoeprs that really like DTS though, so I get an infernal number of packages on some of my servers.

-PatP|||Me being the lazy bum that I am, ...
-PatP

I think I'm maybe lazier. I created a script that saves my DTS packages every night to a remote (off-server) location:

' ************************************************** ********************
' * SaveDTS.vbs
' * Hugh Scott
' * 2005/06/28
' *
' * This script logs into a SQL Server, locates all DTS packages and Then
' * saves each pacakge as a file to a specified location
' *
' * Parameters:
' * IN: None
' * OUT:
' * None
' *
' * Usage:
' *
' *
' * Modification History:
' * Date Initials Modification
' * 2005/06/28 HMS Original
' *
' ************************************************** ********************
Dim oDTS
Dim oConn
Dim oRS
Dim sSQL
Dim iPackages
Dim sServer
Dim sUser
Dim sPassword
Dim bTrusted
Dim sTarget
Const DTSSQLStgFlag_UseTrustedConnection = 256

bTrusted = True
sServer = <set server name here>
sTarget = <set backup destination here>

Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")

If bTrusted Then
oConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
Else
oConn.ConnectionString = "Provider=SQLOLEDB.1;UID=" & sUser & ";Password=" & sPassword & ";Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
End if
oConn.Open

sSQL = "SELECT DISTINCT name FROM sysdtspackages"

Set oRS = oConn.Execute(sSQL, iPackages)

While Not oRS.EOF

Set oDTS = CreateObject("DTS.Package")
sName = oRS("name")
oDTS.LoadFromSQLServer sServer,,,DTSSQLStgFlag_UseTrustedConnection,,,,sN ame
sFileName = Replace(sName, ":", "")
oDTS.SaveToStorageFile sTarget & sFileName & ".dts"
Set oDTS = Nothing
oRS.MoveNext
Wend

Set oRS = Nothing
oConn.Close
Set oConn = Nothing

Schedule it as a job (set the task type to VB Script). It ensures that you always have a backup of your DTS packages when it comes time to restore the db server.

Regards,

hmscott|||Nice idea. I may implement this.|||Awesome help guys thankyou all so much! :D|||open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.

I tried HMSCOTT's code, and it yielded the same results as everything else

it is unable to backup my packages correctly, they get restored with too many incorrect settings

the only option I have gotten to work is doing a manual "save as" which is explained above|||... they get restored with too many incorrect settings ...

Can you explain/elaborate? What settings are incorrect?

Be aware that DTS is very environment dependent. You would have to make certain that:

1. Any drivers that a DTS package would use would be the same on the new server as the old server.

2. Any custom DTS Tasks would have to be the same on the new server as the old server.

3. External paths, links and certain files must be identical (for example, an Oracle TNSNames.ora file, or a UNC path)

4. In some cases security settings will have an impact.

Regards,

hmscott|||here is a description of the problem:

if i open this particular dts package and save it to file it is 511B, but if i use any backup program or script i.e. your script, DTS backup, a script i wrote, etc... it is 472B.

when i open both files in enterprise manager, the 511B file yields the correct package (obviously for migration i would have to change settings but no big deal as long as its the same structure)

the 472B package is compeltley different, I don't even know how it thinks the translation even comes close. it messes up some success/failure criterias and creates additional connection ??|||hmm, okay, I can't address most of your specific issues. I can only comment on my own experiences and environment.

I just opened up all of the package "structured" files that I have. I opened them from a different server than the one on which they normally reside.

I found the following:
1. In all but one case, the packages restored just fine with no errors.
2. The comments that I had created using the textbox feature were all missing (ugh). Also, in some cases, the objects were shifted on the designer window (moving from the left side of the screen to the right and vice versa).
3. In one case, only two VBScript tasks showed in the designer window (out of maybe 40 tasks). I checked the disconnected edit properties, though, and found everything to be present.
4. When I executed the package (the one with the clear designer window), everything executed in the order in which I would expect it.

I am running SQL Server 2000 with SP4 (patched with hotfix 2040).

I will do some more research at work tomorrow. I'm glad you pointed this out; I excercised the recovery process once last year, but I haven't looked at this in a while.

Regards,

hmscott|||one thing to check would be to look at the size of your structured files for your backup program versus a file that you saved manually

maybe do this for the most complicated packages to see if there is an issue

it might just be that my packages are too complicated for a backup program to account for|||See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

Here's a pleasant thought...there is no DTS in 2k5|||hahaha... that is hilarious

what does 05 use to automate external data flows?|||http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx

Look for Intergration Services...they don't even mention DTS, nor do they mention how (or if) it's possible to migrate existing packages.|||See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

Here's a pleasant thought...there is no DTS in 2k5

Yep, with 2k5, they shot all of the Access developers and brought in the BizTalk developers. Arrrrgh!!!

On the serious side, I figured what I had done wrong in the earlier example (the one package that would not restore properly): I had a custom task in the package and had not registered the custom task on the server to which I restored the package. Once, I took care of that little task, then the package opened up just fine. My file sizes are also different between the programmatic save versus the manual save. I think that is because the programmatic method is saving version information in addition to the structure. When I go to open the package, I see a list of possible entries to open, starting with the most recently saved version.

Again, I can't comment or help on your specific scenario; I'm glad for the opportunity to have checked out the script and verified the restore process. It seems to work fine for me.

:shrug:

Regards,

hmscott

Exporting diagram

When using the DTS to copy a db from my local MSSQL to the remote server (connecting via IP address) everything is copied ok except the diagram laying out the relations.
The table relations *are* copied because the damn thing works as expected also on the remote server.

I figured out that the diagrams are saved in the sysfile 'dtproperties' but when copying using the DTS query tool from local to remote this table is not visible.

I need to remove some relations on certain tables when bulk loading new data and was just wondering how I got to copy the diagram (as opposed to creating a new diagram on the remote server and adding relations manually).

Thanks a lot for any ideas,
Nielsdtproperties doesnt show up in the query builder. just write it by hand "select * from dtproperties"
also, if you dont create the destination file/object (i.e. flat text file) prior to running this it will complain about an object not being found. for instance if you name your destination file dtproperties.txt in the exporter but it doesnt exist it will complain -- at least it did for me.

Exporting Database using Microsoft OLE DB Prvider

Hello,

I'm trying to export a database using the 'DTS Import/Export Wizard', I need to use the 'Microsoft OLE DB Provider for SQL Server', but it does not appear on the 'Data source' Drop-Down list of that Wizard.

What can I do to get that OLE DB Provider installed?

I'm running SQL Server 2000 Personal on a Windows XP Media Center PC and I have MSDAC 2.8 SP1 installed.

I also checked that I have the file sqloledb.dll on the 'C:\Program Files\Common Files\System\Ole db' directory.

I'll appreciate any help.

Thanks.

apply sql server sp4|||

I already have SQL Server SP4.

I also tried to unregister and re-register sqloledb.dll and oledb32.dll

|||Anybody? I'm having the exact same problem. how do you "re-install" and register the standard data source items like "Microsoft OLE DB Provider for SQL Server"?

I'm running XP Media Center SP2 with

SQL Enterprise Manager 2000 SP4.

I've re-registered the DLLs, etc. Where can the data source files physically be found?

I don't have much use for "Driver da Microsoft para arquivos textto (*txt,*csv)"!

I need my SQL Import/Export back! Help!

Thanks, all...

Exporting Data to File using TSQL

Does anyone know of a way to do this without using DTS? Simply by
SELECT * FROM <table>John,
Something like this:
EXEC master..xp_cmdshell 'osql -E -dPubs -Q"select * from
authors" -o"C:\authors.txt"'
or BCP (OUT)
HTH
Jerry
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:4E489E6F-2CE8-416B-AAF3-A73349B7092C@.microsoft.com...
> Does anyone know of a way to do this without using DTS? Simply by
> SELECT * FROM <table>
>

Exporting data to Excel from a DTS

Hi all,

I've seen this noted in many posts, but nothing I've checked out gives
me any clue on how to do this.

Basically as my topic says, I have a DTS and I simply need to export
some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
need to automate this process so it can run nightly and each new day a
new spreadsheet will be on a network share for us to pick-up.

Can someone point me to the right direction? This needs to be done
totally through the DTS script, so no ImportExport wizard or anything
manual.

Thanks --

SamAlex wrote:
> Hi all,
> I've seen this noted in many posts, but nothing I've checked out gives
> me any clue on how to do this.
> Basically as my topic says, I have a DTS and I simply need to export
> some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
> need to automate this process so it can run nightly and each new day a
> new spreadsheet will be on a network share for us to pick-up.
> Can someone point me to the right direction? This needs to be done
> totally through the DTS script, so no ImportExport wizard or anything
> manual.
> Thanks --
> Sam

I have dozens of DTS packages that create Excel reports. Here is the method I use:

1. FTP a template workbook from source folder to reports folder.
2. Create worksheet (table) in the report workbook
3. Data pump from SQL Server to the workbook.
4. E-mail the report to recipients

Here's the setup details:
1. Create a source folder on the server. This keeps all my report templates.
2. In the source folder, create a template workbook with just one worksheet.
In my company, this sheet has the company name, the name of the report and
standard boiler-plate text about confidentiality, etc. This is the primary
reason I use this approach, since it does not require that I re-create the
standard title worksheet every time.
3. Copy the template workbook from source folder to reports folder. You need
to do this only for the first time. The file must exist in order to create the
connection. SQLAgentCmdExec will need write access to the reports folder.
4. Create DTS package:
5. Create two connections, one to SQL Server, the other to the workbook in the
reports folder.
6. Task 1 - FTP task to copy template from source to reports, with overwrite
7. Task 2 - Execute SQL Task to CREATE TABLE (worksheet) in the report. I
create the table every time the package runs rather than keeping it in the
template because the report may change over time. When this happens, I just
change the data pump and leave the template alone.
8. Task 3 - Data Transformation task to pump data into the worksheet. The
source would be your SQL statement that selects the data from your five tables.
9. Task 4 - ActiveX task to e-mail the report.

The easiest way to create the CREATE TABLE statement is to set up the data pump
task and click on the Create Table button. Copy the Create `New Table`...
statement to the clipboard and paste it into the Execute SQL task, changing the
table name as appropriate. The table name becomes the worksheet name. If you
edit this satement, be careful not to use single-quote character. The delimiter
in the statement is the left-leaning accent mark (the one at upper-left of
keyboard, on same key as tilde).

HTH,

Ed

Exporting data from SQL Server to Excel from a Stored Procedure

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

ThanksI'd use the Data | Get External Data | New Database Query menuitem in Excel. When you get to MS-Query, don't select any tables. Use the SQL button and type in the EXECUTE command for the stored procedure. Save this query and then the users can simply execute it!

-PatP

Exporting Data from excel to SQL Server express

Hi

I have managed to add the DTS wizard as a tool and saved the routine. However, I do not know how to schedule my PC to automatically run this DTS procedure. I have tried to use scheduled tasks, but it does not seem to want to start.

Pls help.

It was absolutely amazing.

Just by typing in "SQL Server" and Excel in the search box on this page in the forums, I found these previous posting on the same topic...

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

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

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

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

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

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

There were more...

|||

Hi Arnie,

Thanks for the suggestion, I discovered a a previous post you made. You suggested that you get scheduled tasks to run dtsrun.exe. I do not have DTSrun.exe and surely you would have to make the executable run only your specified saved DTS 'routines'. How would you do this?

Regards

George

|||

Perhaps these will help guide you.

DTS - How to Run a DTS Package as a Scheduled Job
http://www.support.microsoft.com/?id=269074

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

|||

Hi

Thank you for your help, but unfortunately the above solutions do not seem to work. Let me explain my situation more completely:

I have installed Microsoft SQL Server Express Edition with Advanced Services SP2 and the Toolkit. I have made the DTSWizard a tool in SQL management studio express I have transferred sample data into the database using the DTS tool. I have 'saved' the DTS transfer|||

For command line details about executing a 'package', refer to Books Online, Topic: dtutil

Exporting BIT datatypes?

Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END|||select cast(colBit as int) as colInt|||I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END

That is a darn good idea - but I dont have the privs. to make a view :-/
Edit: this is all going straight into oracle.|||So what format is the output in?|||So what format is the output in?

If I let DTS handle the datatype - it gives it the datatype NUMBER and the value 0 or -1.

If I manually change the columns datatype to char, I get T or F.
edit:this is all going directly into oracle.|||You know you can just type the sql in to the source in DTS

How much data are we talking about...|||You know you can just type the sql in to the source in DTS

How much data are we talking about...

This happens in a few tables...We're talking over 10,000 rows.|||Does Oracle even have a bit datatype?

What version we talking about?|||it most certainly does not. 9i.
if i could use number, and it didn't automatically turn "1" into "-1" this wouldn't be a big deal but alas...|||How about the ABS function? Would that work?|||How about the ABS function? Would that work?

hmmm yes, that'd work on the front end for the conditonal statements but it'd still require me to change that all over the application unless there was some way to run that during DTS...(which there may be? I'm not too familiar with SQL server or DTS)

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the mysql table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If mysql can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:

> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the mysql table's strucutre and it was also set to varbinary(8)
> Please help.
>sql

Exporting Binary Data from MSSQL to MySQL

I have a MSSQL table with Primary Keys, Company_Id, set as a binary
value. I used the dts Transform Data Task and to create a new table in
MySQL.
The default create code comes up with:
CREATE TABLE `New Table` (
`Web_Edited` tinyint unsigned NULL,
`Company_Id` date NOT NULL,
`Rn_Descriptor` varchar (80) NULL, ...
Why is the Company_Id set to date and not varbinary?
Secondly, if I change the create statement
...`Company_Id` date NOT NULL, ...
to
... `Company_Id` varbinary(8) NOT NULL, ...
then on the Transform Data Task's Destination Tab, the Company_Id is
displayed as longtext. And then I specify that the Company Id column
is the only column I wanted in the transformation, and executed the
trasformation.
I get an error stating this:
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation__1' conversion error: General
conversion failure on column pair 1(source column 'Company_Id'
(DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
But I already specified the Company_Id column as a binary, and I even
checked the MySQL table's strucutre and it was also set to varbinary(8)
Please help.Hi
I don't know much about MYSQL, but this may be a driver issue, are you using
ODBC? If MYSQL can import text files you may want to try using BCP and then
importing from MYSQL.
John
"datenisis@.gmail.com" wrote:
> I have a MSSQL table with Primary Keys, Company_Id, set as a binary
> value. I used the dts Transform Data Task and to create a new table in
> MySQL.
> The default create code comes up with:
> CREATE TABLE `New Table` (
> `Web_Edited` tinyint unsigned NULL,
> `Company_Id` date NOT NULL,
> `Rn_Descriptor` varchar (80) NULL, ...
> Why is the Company_Id set to date and not varbinary?
> Secondly, if I change the create statement
> ...`Company_Id` date NOT NULL, ...
> to
> ... `Company_Id` varbinary(8) NOT NULL, ...
> then on the Transform Data Task's Destination Tab, the Company_Id is
> displayed as longtext. And then I specify that the Company Id column
> is the only column I wanted in the transformation, and executed the
> trasformation.
> I get an error stating this:
> The number of failing rows exceeds the maximum specified.
> TransformCopy 'DTSTransformation__1' conversion error: General
> conversion failure on column pair 1(source column 'Company_Id'
> (DBTYPE_BYTES), destination column 'Company_Id' (DBTYPE_STR))
> But I already specified the Company_Id column as a binary, and I even
> checked the MySQL table's strucutre and it was also set to varbinary(8)
> Please help.
>

Exporting a DTS Package

Hi,
I have a Database on one server and the same Database on
another server.
One Database has a DTS Package that I need.
Can I export this DTS package to my Database that does not
have it without doing a backup of the one that has it and
restoring it to the one that does'nt?
CHi
You van save them as files and transfer , another option is to move from
sysdtspackages table in msdb database.
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
> Hi,
> I have a Database on one server and the same Database on
> another server.
> One Database has a DTS Package that I need.
> Can I export this DTS package to my Database that does not
> have it without doing a backup of the one that has it and
> restoring it to the one that does'nt?
> C|||The easiest way of moving DTS packages between servers is to Design the DTS
package on the source server and simply say Save As and specify the other
server name.
If the servers can't see one another on the network save the package to a
structured file copy to other server then open
--
HTH
Ryan Waight, MCDBA, MCSE
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
> Hi,
> I have a Database on one server and the same Database on
> another server.
> One Database has a DTS Package that I need.
> Can I export this DTS package to my Database that does not
> have it without doing a backup of the one that has it and
> restoring it to the one that does'nt?
> C|||We use both of Ryan's methods all the time and either way
is probably your best way. Using the structured file
also allows you to utilize versioning software such as
Source Safe.
One thing to remember when you move DTS packages to a new
server is to verify all of your Data Connections within
the packages, especially if you have paths specified to
text files or MS Access files.
Matthew Bando
BandoM@.CSCTechnologies (remove this).com
>--Original Message--
>The easiest way of moving DTS packages between servers
is to Design the DTS
>package on the source server and simply say Save As and
specify the other
>server name.
>If the servers can't see one another on the network save
the package to a
>structured file copy to other server then open
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"C" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b5d01c3b3fb$5c516ff0$a401280a@.phx.gbl...
>> Hi,
>> I have a Database on one server and the same Database
on
>> another server.
>> One Database has a DTS Package that I need.
>> Can I export this DTS package to my Database that does
not
>> have it without doing a backup of the one that has it
and
>> restoring it to the one that does'nt?
>> C
>
>.
>

export/import

What are the ways to export/import data in/out of SQL Server 2005? I
used to use DTS for 2000 to do such thing and now I can't even find
the wizard in 2005!The import/export wizard is available from SQL Server Management Studio from
the Object Explorer. Right-click on the a database node and select Tasks
and then either Import or Export Data. The wizard will generate a SQL
Server Integration Services (SSIS) package that you can execute and/or save.

Note that SSIS is a replacement for the DTS feature found in previous SQL
Server versions. SSIS packages can be created from scratch using SQL Server
Business Intelligence Development Studio.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<othellomy@.yahoo.comwrote in message
news:1172559059.922478.114550@.a75g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

What are the ways to export/import data in/out of SQL Server 2005? I
used to use DTS for 2000 to do such thing and now I can't even find
the wizard in 2005!
>

|||On Feb 27, 7:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

The import/export wizard is available from SQL Server Management Studio from
the Object Explorer. Right-click on the a database node and select Tasks
and then either Import or Export Data. The wizard will generate a SQL
Server Integration Services (SSIS) package that you can execute and/or save.
>
Note that SSIS is a replacement for the DTS feature found in previous SQL
Server versions. SSIS packages can be created from scratch using SQL Server
Business Intelligence Development Studio.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<othell...@.yahoo.comwrote in message
>
news:1172559059.922478.114550@.a75g2000cwd.googlegr oups.com...
>
>
>

Quote:

Originally Posted by

What are the ways to export/import data in/out of SQL Server 2005? I
used to use DTS for 2000 to do such thing and now I can't even find
the wizard in 2005!- Hide quoted text -

>
- Show quoted text -

Okay thanks!

Export with DTS to a dbf file

Hello,

I'm trying to export data from sql server2000 to a dbf file
When creating the dts package, i've got the following error (when creating the table in the dbf file):
"HResult of 2147467259 returned. Unexpected error occurred..."

The table i try to export is like:
Table1 (field1 int, field2 int...)

Does anyone have any idea?

Thanks in advance

IsabelleDo not forget that length of name field for DBF is only 10 ...
Check name of columns in your table.

export to XML

I'm on a shared server with no DTS capabilities. Is it possible to use a
stored procedure to export data from an SQL recordset into an XML file on a
remote server? i.e. I would need to create http://mydomain/myfile.xml
Anyone have sample code?
thanksYour web site appears to be down. Have a look at for xml explicit. This can
be used in a stored procedure to do exactly what you are looking for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shank" <shank@.tampabay.rr.com> wrote in message
news:uupZSE$DGHA.3000@.TK2MSFTNGP14.phx.gbl...
> I'm on a shared server with no DTS capabilities. Is it possible to use a
> stored procedure to export data from an SQL recordset into an XML file on
> a remote server? i.e. I would need to create http://mydomain/myfile.xml
> Anyone have sample code?
> thanks
>|||I have the below code that generates XML.
How do I get this into an XML file on a remote server?
Example: http://www.mydomain.com/mytestfile.xml (ficticious domain name)
Does this need to be done through ASP?
Or can I go direct from SQL to an XML file? (preferred)
thanks
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[xTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xTest]
GO
CREATE TABLE [dbo].[xTest] (
[SkuNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Descrip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku1', 'Descrip1',
'Type1')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku2', 'Descrip2',
'Type2')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku3', 'Descrip3',
'Type3')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku4', 'Descrip4',
'Type4')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku5', 'Descrip5',
'Type5')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku6', 'Descrip6',
'Type6')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku7', 'Descrip7',
'Type7')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku8', 'Descrip8',
'Type8')
INSERT INTO dbo.xTest (SkuNo, Descrip, Type) VALUES ('Sku9', 'Descrip9',
'Type9')
SELECT 1 as Tag, NULL as Parent,
SkuNo AS [Stock!1!SkuNo],
Descrip AS [Stock!1!Descrip],
Type AS [Stock!1!Type]
FROM xTest
FOR XML EXPLICIT|||Hi
Look at http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp and the
sp_makewebtask solution although this is really only suitable for smaller
files.
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:uupZSE$DGHA.3000@.TK2MSFTNGP14.phx.gbl...
> I'm on a shared server with no DTS capabilities. Is it possible to use a
> stored procedure to export data from an SQL recordset into an XML file on
> a remote server? i.e. I would need to create http://mydomain/myfile.xml
> Anyone have sample code?
> thanks
>

Export to Excel using DTS

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)

what is the best way to do this? DTS, BCP

thanks
FOCELBrett will be along shortly to offer up a much cleaner solution using BCP and two lines of code (one of which will call a stored procedure to mix up a margarita). In the meantime, may I humbly offer up:

You can create a package with a global variable indicating which SP to run. Have three transformations defined in the package and determine which transformation to execute in a VBScript (using the global variable).

I'm not sure what will happen to the other transformations (the two that are not executed). That may require more testing...

You can look for more hints at http://www.sqldts.com.

Regards,

hmscott

export to excel file

I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported.

First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I created the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. I haven't been able to correct this.

Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.

How can I do this?

Thanks,
-D-I figured it out...please disregard.

Export to .txt file using SQL Server DTS

I am exporting a SQL Server 2000 table to an "errors.txt" file. I can get this to work just fine. I need to be able to plug the date and record count into my file name...(ex. MMYYYYCCCCerrors.txt).

The first 6 characters are the month and year of the file. CCCC represents a record count with leading zeroes.

Current Process - An ASP page has an "Export" button on it. After the "Export" button is clicked, a Stored Proc is called and the SP executes the DTS package. The DTS package just copies the data from a table to a .txt file.

Thanks.Hello Len,

I would propose that you add an ActiveXTask to your DTS-package, which manipulates the properties of your export task.

This ActiveXskript defines a variable with your desired file name (e.g. MMYYYYCCCCerrors.txt) and checks if this files exists in OS level. If it does exist, increase CCCC by 1 and check again.

For better help on this topic look at this article (http://www.sqldts.com/default.aspx?235). Generally, this site offers me quite a lot of help.

Hope this helps you! Otherwise post a reply.

Greetings,
Carsten

export through DTS

Hi Everyone I got another problem every time I try to import data to Sql from Access 2000 it keeps give me an error message that says row 2271. Errors encountered so far in this tabel 1 insert error, column 8 (DOB, DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid Character value for cast sepcification.

anyone got any suggestionsWhat is the record in row 2271?|||the same data in 2271 exsist in the rest of the database. columns Category, rehire, firstname..lastname..etc hiredate (date formats date/time)|||I guess, Derrick was asking what is the value of column 8 (DOB) at record 2271. It has to be different vs. let's say record #2270. Is the data type in access also datetime or it's a varchar?|||oh sorry the data in access is date/time data type, not varchar|||Here's where I'm going with this. Give us the CREATE TABLE statement for the SQL Server table you're importing into. Then give use the entire row of data from records 2270 and 2271. We should be able to see what's going on from that easily.

Export Table to Text File Using Query Analyzer

I can use DTS and BCP, but I am trying to export a table into a text
file through Query Analyzer (I need to show documentation in QA). DTS
uses EM and does not leave a "paper trail"
I am looking for the reverse of BULK INSERTHi
I am not sure what you are wanting to audit regarding your paper trail.
There is no BULK EXPORT command. If your DTS package is executed through a
job then you will have the job history. To use a stored procedure to start
the job/package, then you can start a job using sp_start_job or execute a DT
S
package through xp_cmdshell or the sp_OA* procedures see
http://www.databasejournal.com/feat...cle.php/1459181
John
"Justin Chandler" wrote:

> I can use DTS and BCP, but I am trying to export a table into a text
> file through Query Analyzer (I need to show documentation in QA). DTS
> uses EM and does not leave a "paper trail"
> I am looking for the reverse of BULK INSERT
>

Export Table but Keep Indexes

I need to migrate some tables from my development database to the
production database. I have played with the export dts wizard but the
tables lose their keys and indexes. Is there a way to migrate a table
and keep the indexes and keys?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Make sure you use the right DTS option: "Transfer objects and data between
SQL Server databases", and check all relevant option inside that dialog.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Debbie" <anonymous@.email.com> wrote in message
news:uEE3qp1vDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I need to migrate some tables from my development database to the
> production database. I have played with the export dts wizard but the
> tables lose their keys and indexes. Is there a way to migrate a table
> and keep the indexes and keys?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!