Hi,
Is it possible to create DTS packages with MSDE? If so, how?
Would you execute them via the "dtsrun" command-prompt utility?
Thanks very much,
Mark Holahan
MSDE does not come with the tools that are necessary to create DTS packages.
For that you will need to user Enterprise Manager, which come with the full
versions of SQL Server, including the Developers version. MSDE instances can
store DTS packages in their repository and MSDE 2000 comes with the
dtsrun.exe utility, allowing you to execute packages.
Jim
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message
news:e93IFVWBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to create DTS packages with MSDE? If so, how?
> Would you execute them via the "dtsrun" command-prompt utility?
> Thanks very much,
> Mark Holahan
>
Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Creating SQL View - syntax is wrong
We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:
CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
I am attaching the whole query stmt here.
Thanks very much for all assistance.Is LineNbr numeric?
--EDIT:
Can you tell us the error?
Might help|||The error received in Query analyzer is:
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 107
Line 107: Incorrect syntax near '<'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 133
Line 133: Incorrect syntax near 'APTran'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 186
Line 186: Incorrect syntax near 'APDoc'.
The field aptran.linenbr is datatype smallint.
Thanks, Dave Spangler|||CASE
WHEN cast(APTran.LineNbr as int) < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN cast(APTran.LineNbr as int) > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,|||Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!
CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.|||Originally posted by rdjabarov
Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!
CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.
Hey, get your own ideas...:D
And why would they have to check for Nulls Null would give them an "E"|||Yup, forgot about ELSE
CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
I am attaching the whole query stmt here.
Thanks very much for all assistance.Is LineNbr numeric?
--EDIT:
Can you tell us the error?
Might help|||The error received in Query analyzer is:
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 107
Line 107: Incorrect syntax near '<'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 133
Line 133: Incorrect syntax near 'APTran'.
Server: Msg 170, Level 15, State 1, Procedure xvr_03901EXPORTER, Line 186
Line 186: Incorrect syntax near 'APDoc'.
The field aptran.linenbr is datatype smallint.
Thanks, Dave Spangler|||CASE
WHEN cast(APTran.LineNbr as int) < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN cast(APTran.LineNbr as int) > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,|||Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!
CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.|||Originally posted by rdjabarov
Hey, based on the code you submitted before I clicked on Post, I thought (here's that magic word again ;)) that it was a varchar field!
CASE
WHEN APTran.LineNbr < 0 THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN APTran.LineNbr > 0 THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,
And you probably need to test for NULL unless this is taken care of by the 'IsNullable' property of the field.
Hey, get your own ideas...:D
And why would they have to check for Nulls Null would give them an "E"|||Yup, forgot about ELSE
Creating sql server table from dts
I am trying to design a SSIS DTS package that imports an Excel named range (acts as a table) into a SQL Server 2005 database table. The direct import (using the import/export wizard) works fine, but I need to use a DTS package with a data transformation step. The DTS fails to create/replace/or update the SQL Server table. Suggestions?
Which is it? SSIS or DTS?
Can you clarify what you're trying to do?
How are you attempting to create/replace/update the table?
Kirk Haselden
Author "SQL Server Integration Services"
Kirk, Thank you for the response. Your book will arrive tomorrow and I expect it to answer my many questions. I am trying to use the SQL Server 2005 SSIS designer to produce a package (SSIS or DTS?) to copy a named range (proxy for an Excel 'table') from a network drive; convert the data types to what I want on the SQL Server 2005 server; delete the database table if it already exists and create a new table; and write the new data to the table. The connection to the Excel workbook named range and the data conversion step work fine. I get the following error in the destination step: [SQL Server Destination [98]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security." I have no idea what this means!
I am a 'user' rather than an 'IT pro' who is trying to build a small 'data-mart' for better management of data used for analysis, forecasting and strategic planning. A SSIS package appears to be the best means for accomplishing this task every month and many other data upload packages will be produced as soon as I understand the process.
Thank you for your help.
|||Kirk Haselden's book on Integration Services is very helpful. I highly recommend it!Creating sql server table from dts
I am trying to design a SSIS DTS package that imports an Excel named range (acts as a table) into a SQL Server 2005 database table. The direct import (using the import/export wizard) works fine, but I need to use a DTS package with a data transformation step. The DTS fails to create/replace/or update the SQL Server table. Suggestions?
Which is it? SSIS or DTS?
Can you clarify what you're trying to do?
How are you attempting to create/replace/update the table?
Kirk Haselden
Author "SQL Server Integration Services"
Kirk, Thank you for the response. Your book will arrive tomorrow and I expect it to answer my many questions. I am trying to use the SQL Server 2005 SSIS designer to produce a package (SSIS or DTS?) to copy a named range (proxy for an Excel 'table') from a network drive; convert the data types to what I want on the SQL Server 2005 server; delete the database table if it already exists and create a new table; and write the new data to the table. The connection to the Excel workbook named range and the data conversion step work fine. I get the following error in the destination step: [SQL Server Destination [98]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security." I have no idea what this means!
I am a 'user' rather than an 'IT pro' who is trying to build a small 'data-mart' for better management of data used for analysis, forecasting and strategic planning. A SSIS package appears to be the best means for accomplishing this task every month and many other data upload packages will be produced as soon as I understand the process.
Thank you for your help.
|||Kirk Haselden's book on Integration Services is very helpful. I highly recommend it!Sunday, February 19, 2012
Creating Identity Field
Below is a simple CREATE TABLE statement in my DTS job that drops myTable,
recreates it and then transfers data from myTable in database 1 to the
myTable in destination database. Given the fact that myTable contains data
and that the causeID field may not begin with a 1 value, what parameters
should I use so SQL will not change or start with causeID = 1?
I just want my data to import and retain whatever identity values it has. Do
I have to specify the (1,1) after IDENTITY?
CODE:
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL,
[causeCode] varchar (3) NULL,
[causeName] varchar (50) NULL
)Scott wrote:
> I just want my data to import and retain whatever identity values it
> has. Do I have to specify the (1,1) after IDENTITY?
> CODE:
>
> CREATE TABLE [myDatabase].[dbo].[myTable] (
> [causeID] int IDENTITY(1,1) NOT NULL,
> [causeCode] varchar (3) NULL,
> [causeName] varchar (50) NULL
> )
You can use
Set IDENTITY_INSERT [myTable] ON
then insert the data and afterwards
Set IDENTITY_INSERT [myTable] OFF
New data which will be inserted later will get the correct identities.
HTH,
Stijn Verre^t.|||Why are you using IDENTITY at all? Why is "cause_code" not the proper,
relational key'|||causeID is the primary key, causeCode is a text field code that a user
enters.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>|||Let me help out with Celko's angle here.
If I type causeCode = "foobar" and so does Celko, why do we have different
causeID values?
Now, let me say that I am not against using Identity (or a similar surrogate
key approach), but I do believe that causeCode should at least be unique /
non-repeating.
A
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
> causeID is the primary key, causeCode is a text field code that a user
> enters.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
>|||Probably something like this...
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eudsZJ98FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Let me help out with Celko's angle here.
> If I type causeCode = "foobar" and so does Celko, why do we have different
> causeID values?
> Now, let me say that I am not against using Identity (or a similar
> surrogate key approach), but I do believe that causeCode should at least
> be unique / non-repeating.
> A
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||I think Scott simply has terminology mixed up which isn't a crime, some
people confuse a 'relational key' with a the standard Natural Key.
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>
recreates it and then transfers data from myTable in database 1 to the
myTable in destination database. Given the fact that myTable contains data
and that the causeID field may not begin with a 1 value, what parameters
should I use so SQL will not change or start with causeID = 1?
I just want my data to import and retain whatever identity values it has. Do
I have to specify the (1,1) after IDENTITY?
CODE:
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL,
[causeCode] varchar (3) NULL,
[causeName] varchar (50) NULL
)Scott wrote:
> I just want my data to import and retain whatever identity values it
> has. Do I have to specify the (1,1) after IDENTITY?
> CODE:
>
> CREATE TABLE [myDatabase].[dbo].[myTable] (
> [causeID] int IDENTITY(1,1) NOT NULL,
> [causeCode] varchar (3) NULL,
> [causeName] varchar (50) NULL
> )
You can use
Set IDENTITY_INSERT [myTable] ON
then insert the data and afterwards
Set IDENTITY_INSERT [myTable] OFF
New data which will be inserted later will get the correct identities.
HTH,
Stijn Verre^t.|||Why are you using IDENTITY at all? Why is "cause_code" not the proper,
relational key'|||causeID is the primary key, causeCode is a text field code that a user
enters.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>|||Let me help out with Celko's angle here.
If I type causeCode = "foobar" and so does Celko, why do we have different
causeID values?
Now, let me say that I am not against using Identity (or a similar surrogate
key approach), but I do believe that causeCode should at least be unique /
non-repeating.
A
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
> causeID is the primary key, causeCode is a text field code that a user
> enters.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
>|||Probably something like this...
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eudsZJ98FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Let me help out with Celko's angle here.
> If I type causeCode = "foobar" and so does Celko, why do we have different
> causeID values?
> Now, let me say that I am not against using Identity (or a similar
> surrogate key approach), but I do believe that causeCode should at least
> be unique / non-repeating.
> A
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||I think Scott simply has terminology mixed up which isn't a crime, some
people confuse a 'relational key' with a the standard Natural Key.
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>
Creating Global Temp table in sql 2005 DTS
Hi All,
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev RajputHi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev RajputHi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
Creating Global Temp table in sql 2005 DTS
Hi All,
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev Rajput
Hi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev Rajput
Hi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
Creating Global Temp table in sql 2005 DTS
Hi All,
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev RajputHi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
I want to use temp table created in one step to be used in other
step.
How can i do that ?
I tried this..
One step : Create table ##abc (Fileid Int)
Other Step : Select * from ##abc -- When i Parse the query it is
giving error Invalid object name ##abc
Can anyone help me with this ?
Regards,
Rajeev RajputHi
"Rajeev" wrote:
> Hi All,
> I want to use temp table created in one step to be used in other
> step.
> How can i do that ?
>
> I tried this..
>
> One step : Create table ##abc (Fileid Int)
>
> Other Step : Select * from ##abc -- When i Parse the query it is
> giving error Invalid object name ##abc
>
> Can anyone help me with this ?
>
> Regards,
> Rajeev Rajput
>
Assuming that you have set up the correct presedences and the two tasks are
sharing the same connection, then make the RetainSameConnection property to
true on the connection and it should be ok.
John
Friday, February 17, 2012
Creating excel file from DTS package
Hi,
I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.
I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?
Thanks!Am I missing something or can't you just create a data transformation task going from your database to a excel spreadsheet?
Create a conenction to a spreadsheet and a connection to a database and build your transformation. DTS will take care of everything else. If you need to play with filesnames etc you can do that after the package has been created (using the filesystem object)|||is there any sample to show that how to ceate an excel file after reading data from sql server?
Thanks|||start>programs>Microsoft SQL Server>Import and Export Data
follow the wizard. select your db>Select your output file (excel)>hit next>run immediately>done|||Sorry, I didn't explain my question properly. I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.
Thanks!|||I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.
Thanks!|||I 've create a template with the functions for addign totals, etc already built in. using VB SCRIPT, copy the template and populate it every time you run the DTS package.|||Could you please explain how can I create this template and copy it every time I need this?
Thanks|||'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objFSO, objFileTemplate, strFile
Set objFSO = CreateObject ( "Scripting.FileSystemObject" )
strFile = DTSGlobalVariables("strRootPath").Value & "Template.xls"
Set objFileTemplate = objFSO.GetFile ( strFile )
strMonth = Right ( "0" & DatePart ( "m", DTSGlobalVariables("dtStart").Value ), 2 )
strFile = DTSGlobalVariables("strPath").Value & _
DatePart ( "yyyy", DTSGlobalVariables("dtStart").Value ) & _
strMonth & _
" - " & _
DTSGlobalVariables("ID").Value & " " & DTSGlobalVariables("Id").Value & _
".xls"
DTSGlobalVariables("FileName").Value = strFile
If ( objFSO.FileExists ( strFile ) ) Then
Main = DTSTaskExecResult_Failure
Exit Function
End If
objFileTemplate.Copy strFile, 0
Main = DTSTaskExecResult_Success
End Function
Note: This is basically the code. You have a template in a directory. This vbscrip takes the template, copies it to a different directory. The name is obtained by using the 'Id' field and concatenating month and year.
If you want, we can take this offline, just let me know your email address.
I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.
I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?
Thanks!Am I missing something or can't you just create a data transformation task going from your database to a excel spreadsheet?
Create a conenction to a spreadsheet and a connection to a database and build your transformation. DTS will take care of everything else. If you need to play with filesnames etc you can do that after the package has been created (using the filesystem object)|||is there any sample to show that how to ceate an excel file after reading data from sql server?
Thanks|||start>programs>Microsoft SQL Server>Import and Export Data
follow the wizard. select your db>Select your output file (excel)>hit next>run immediately>done|||Sorry, I didn't explain my question properly. I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.
Thanks!|||I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.
Thanks!|||I 've create a template with the functions for addign totals, etc already built in. using VB SCRIPT, copy the template and populate it every time you run the DTS package.|||Could you please explain how can I create this template and copy it every time I need this?
Thanks|||'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objFSO, objFileTemplate, strFile
Set objFSO = CreateObject ( "Scripting.FileSystemObject" )
strFile = DTSGlobalVariables("strRootPath").Value & "Template.xls"
Set objFileTemplate = objFSO.GetFile ( strFile )
strMonth = Right ( "0" & DatePart ( "m", DTSGlobalVariables("dtStart").Value ), 2 )
strFile = DTSGlobalVariables("strPath").Value & _
DatePart ( "yyyy", DTSGlobalVariables("dtStart").Value ) & _
strMonth & _
" - " & _
DTSGlobalVariables("ID").Value & " " & DTSGlobalVariables("Id").Value & _
".xls"
DTSGlobalVariables("FileName").Value = strFile
If ( objFSO.FileExists ( strFile ) ) Then
Main = DTSTaskExecResult_Failure
Exit Function
End If
objFileTemplate.Copy strFile, 0
Main = DTSTaskExecResult_Success
End Function
Note: This is basically the code. You have a template in a directory. This vbscrip takes the template, copies it to a different directory. The name is obtained by using the 'Id' field and concatenating month and year.
If you want, we can take this offline, just let me know your email address.
Creating DTS Package to archive database from one sql server to another
Hi...
I am new to database programming.
I need to create a DTS Package which should archive a database fron one sql server to another.
I have no idea that which connection objects and which task objects should be used in order to accomplish this task.
Please help me with this.
Any one of your help is greatly appreciated.
Thank youHi There,
Use "Transfer Databases" task. You can find that under Task menu.
Select appropriate options under each tab. Its pretty straight forward.
Test this DTS first using a test database before implementation.
Good Luck!|||I have moved your question from the Articles section of our site to the Forum.
Creating DTS
Are you referring to Data Transformation Services package in SQL Server 2000 or SQL Server Integration Services package in SQL Server 2005? If it's the latter you can create an SSIS project using the Business Intelligence Development Studio which is part of SQL Server 2005 in editions from Developer Edition and higher. Andrew Watt MVP - InfoPath wrote in message news:6a53eb64-e504-4710-99bf-527c9aa02e46@.discussions.microsoft.com...
> Is there a way to create a DTS from Windows application ? >
> And how can I send parameters and configure DTS from my CSharp Code ?
>Is there a way to create a DTS from Windows application ?
> Is there a way to create a DTS from Windows application ? >
> And how can I send parameters and configure DTS from my CSharp Code ?
>Is there a way to create a DTS from Windows application ?
And how can I send parameters and configure DTS from my CSharp Code ?|||I'm talking about Data Transformation Services|||You should direct your question to the "SQL Server Integration Services " forum. That is where the DTS experts live.
Thanks|||Valew|||The public newsgroup for Data Transformation Services in SQL Server 2000 is microsoft.public.sqlserver.dts. Andrew Watt MVP - InfoPath
> I'm talking about Data Transformation Services
>
Creating DTS
Is there a way to create a DTS(Data transformation services) from Windows application ?
And how can I send parameters to DTS from my CSharp Code ?
ThanksDTS is based around a COM object model, so any COM compatible programming language can be used to leverage this object model and therefore build, and execute a package.
You can create SSIS packages in code as well.
Rather than writing your own application why not use Enterprise Manager for DTS or Business Intelligence Development Studio for SSIS?|||Cause at my work we do not have Business Intelligence Development Studio, And I don't know how SSIS works,....
I Have a dll to work with DTS but I don't know how to send the parameters....
I downloaded from http://www.sqldts.com/
Any sugestion ?|||Are you really asking about DTS for SQL Server 2000? My confusion arises because this is a forum for SSIS and not DTS, the title indicates that.
How is this DLL expected to work with DTS? What is it?
If you downloaded the DLL from SQLDTS, what DLL is that from the site?|||UHUn It's DTS for SQL 2000
It's a class that I can call DTS from SQL Server or a file .dts|||So where did this class come from?
dtspkg.dll is a DLL from MS that allows you to load and then execute a DTS package.|||Is this one that I'm using
Subscribe to:
Posts (Atom)