Thursday, March 22, 2012
DTS packages migration from MSSQL 2K to MSSQL server 2005
I need to migrate all my dts packages from sql server 2000 to sql
server 2005 so I can access these packages under Legacy->DTS in
Microsft sql server 2005 Management studio.
How can I do this?
Regards,
Lena
Lena, privet
Under Legacy folder, there is Data Transformation Services folder, right
click on it then Import Package File ( I assumed you saved the dts package
as a file)
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
> Hello
> I need to migrate all my dts packages from sql server 2000 to sql
> server 2005 so I can access these packages under Legacy->DTS in
> Microsft sql server 2005 Management studio.
> How can I do this?
> Regards,
> Lena
|||I already did that.
Now when I try to open the package, I'm getting error:
"The package cannot be edited.
SQL Server 2000 DTS Designer Components are required to edit DTS
packages. Install the special Web download, "SQL server 2000 DTS
designer Com[ponents" to use this feature".
I can not find this download on Microsoft's site.
On Mar 6, 1:21Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Lena, privet
> Under Legacy folder, there is XData Transformation Services folder, right
> click on it then Import Package File ( I assumed you saved the dts package
> as a file)
> "Lena" <lenagloz...@.yahoo.com> wrote in message
> news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
>
>
>
> - Show quoted text -
DTS packages migration from MSSQL 2K to MSSQL server 2005
I need to migrate all my dts packages from sql server 2000 to sql
server 2005 so I can access these packages under Legacy->DTS in
Microsft sql server 2005 Management studio.
How can I do this?
Regards,
LenaLena, privet
Under Legacy folder, there is Data Transformation Services folder, right
click on it then Import Package File ( I assumed you saved the dts package
as a file)
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
> Hello
> I need to migrate all my dts packages from sql server 2000 to sql
> server 2005 so I can access these packages under Legacy->DTS in
> Microsft sql server 2005 Management studio.
> How can I do this?
> Regards,
> Lena|||I already did that.
Now when I try to open the package, I'm getting error:
"The package cannot be edited.
SQL Server 2000 DTS Designer Components are required to edit DTS
packages. Install the special Web download, "SQL server 2000 DTS
designer Com[ponents" to use this feature".
I can not find this download on Microsoft's site.
On Mar 6, 1:21=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Lena, privet
> Under Legacy folder, there is =A0Data Transformation Services folder, righ=t
> click on it then Import Package File ( I assumed you saved the dts package=
> as a file)
> "Lena" <lenagloz...@.yahoo.com> wrote in message
> news:e01c20ab-e0a9-4104-ad48-90f70fd91a5d@.47g2000hsb.googlegroups.com...
>
> > Hello
> > I need to migrate all my dts packages from sql server 2000 to sql
> > server 2005 so I can access these packages under Legacy->DTS in
> > Microsft sql server 2005 =A0Management studio.
> > How can I do this?
> > Regards,
> > Lena- Hide quoted text -
> - Show quoted text -
Wednesday, March 21, 2012
DTS package with inputs
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
Paul G
Software engineer.
Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT@.dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT@.dateto =
> CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom =
> CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
Paul G
Software engineer.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
DTS package with inputs
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.
--
Paul G
Software engineer.Hi Paul
You can set global variables on the command line such as (this has wrapped):
DECLARE @.datefrom CHAR(10)
DECLARE @.dateto CHAR(10)
DECLARE @.cmd varchar(2200)
DECLARE @.cmdroot varchar(2000)
DECLARE @.stat int
SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '
SELECT @.dateto =CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@.datefrom =CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
"DateTo":"7"="' + @.dateto + '"'
EXEC @.stat = master..xp_cmdshell @.cmd
SET @.stat = COALESCE(@.stat,@.@.ERROR)
John
"Paul" wrote:
> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
> input) and then connecting to an Oracle database and appending a table in the
> Oracle database with the data from the SQL query. The SQL query writes the
> data to a table and I then use access to connect to this table and the
> destination table in the Oracle database and run a simple query inside of
> access to append the data into the Oracle table. I would like to create a
> dts package that does this but the initial query requires 2 input parameters,
> start date and end date, anyhow just wondering if you can still build a dts
> package that when run from Enterprise manager would prompt for the date
> inputs and then perform the sql query as well as the data append to the
> Oracle table.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> You can set global variables on the command line such as (this has wrapped):
> DECLARE @.datefrom CHAR(10)
> DECLARE @.dateto CHAR(10)
> DECLARE @.cmd varchar(2200)
> DECLARE @.cmdroot varchar(2000)
> DECLARE @.stat int
> SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> + ' Monthly Export" '
> SELECT @.dateto => CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> @.datefrom => CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> "DateTo":"7"="' + @.dateto + '"'
> EXEC @.stat = master..xp_cmdshell @.cmd
> SET @.stat = COALESCE(@.stat,@.@.ERROR)
> John
> "Paul" wrote:
> > Hi I am using mssql 2000 and have been performing a data port manually that
> > consists of running a query on an sql database (using a date range as an
> > input) and then connecting to an Oracle database and appending a table in the
> > Oracle database with the data from the SQL query. The SQL query writes the
> > data to a table and I then use access to connect to this table and the
> > destination table in the Oracle database and run a simple query inside of
> > access to append the data into the Oracle table. I would like to create a
> > dts package that does this but the initial query requires 2 input parameters,
> > start date and end date, anyhow just wondering if you can still build a dts
> > package that when run from Enterprise manager would prompt for the date
> > inputs and then perform the sql query as well as the data append to the
> > Oracle table.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Hi Paul
The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.
I would put the code into a stored procedure and then run that from the SQL
Agent job step.
If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx
John
"Paul" wrote:
> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
> well as the provided code in the actual SQL job as the command in a step, or
> would this be placed in one of the queries of the dts package?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > Hi Paul
> >
> > You can set global variables on the command line such as (this has wrapped):
> >
> > DECLARE @.datefrom CHAR(10)
> > DECLARE @.dateto CHAR(10)
> > DECLARE @.cmd varchar(2200)
> > DECLARE @.cmdroot varchar(2000)
> > DECLARE @.stat int
> >
> > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > + ' Monthly Export" '
> >
> > SELECT @.dateto => > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > @.datefrom => > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> >
> > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > "DateTo":"7"="' + @.dateto + '"'
> >
> > EXEC @.stat = master..xp_cmdshell @.cmd
> > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> >
> > John
> >
> > "Paul" wrote:
> >
> > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > consists of running a query on an sql database (using a date range as an
> > > input) and then connecting to an Oracle database and appending a table in the
> > > Oracle database with the data from the SQL query. The SQL query writes the
> > > data to a table and I then use access to connect to this table and the
> > > destination table in the Oracle database and run a simple query inside of
> > > access to append the data into the Oracle table. I would like to create a
> > > dts package that does this but the initial query requires 2 input parameters,
> > > start date and end date, anyhow just wondering if you can still build a dts
> > > package that when run from Enterprise manager would prompt for the date
> > > inputs and then perform the sql query as well as the data append to the
> > > Oracle table.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.|||ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> The values would need to be global variables and to assign them withing the
> job would require an activeX task. It is easier and probably a more flexible
> design if you have a job that you pass the dates required to it. Then if at
> some point you need a different date range or run it manually then it is easy
> to do.
> I would put the code into a stored procedure and then run that from the SQL
> Agent job step.
> If you are using stored procedures withing the DTS package then you can have
> parameters that maped to the global variables see
> http://www.sqldts.com/234.aspx
>
> John
> "Paul" wrote:
> > Hi thanks for the response, for some reason I did not receive the
> > notification of replies. So would you place the variable declarations as
> > well as the provided code in the actual SQL job as the command in a step, or
> > would this be placed in one of the queries of the dts package?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Paul
> > >
> > > You can set global variables on the command line such as (this has wrapped):
> > >
> > > DECLARE @.datefrom CHAR(10)
> > > DECLARE @.dateto CHAR(10)
> > > DECLARE @.cmd varchar(2200)
> > > DECLARE @.cmdroot varchar(2000)
> > > DECLARE @.stat int
> > >
> > > SET @.cmdroot = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
> > > + ' Monthly Export" '
> > >
> > > SELECT @.dateto => > > CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
> > > @.datefrom => > > CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)
> > >
> > > SET @.cmd = @.cmdroot + ' /A "DateFrom":"7"="' + @.datefrom + '" /A
> > > "DateTo":"7"="' + @.dateto + '"'
> > >
> > > EXEC @.stat = master..xp_cmdshell @.cmd
> > > SET @.stat = COALESCE(@.stat,@.@.ERROR)
> > >
> > > John
> > >
> > > "Paul" wrote:
> > >
> > > > Hi I am using mssql 2000 and have been performing a data port manually that
> > > > consists of running a query on an sql database (using a date range as an
> > > > input) and then connecting to an Oracle database and appending a table in the
> > > > Oracle database with the data from the SQL query. The SQL query writes the
> > > > data to a table and I then use access to connect to this table and the
> > > > destination table in the Oracle database and run a simple query inside of
> > > > access to append the data into the Oracle table. I would like to create a
> > > > dts package that does this but the initial query requires 2 input parameters,
> > > > start date and end date, anyhow just wondering if you can still build a dts
> > > > package that when run from Enterprise manager would prompt for the date
> > > > inputs and then perform the sql query as well as the data append to the
> > > > Oracle table.
> > > > Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
Friday, February 24, 2012
DTS issue
I am using DTS (the version that shipped with MSSQL 7.0).
I am trying to import at space-delimited text file into a database table.
I have one column in my text file which tends to exced 255 characters.
When this occurs, I run into the following error during the import:
'data for source column 3(colname3) is too large for the specified buffer size.'
Microsoft recommended changing the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows from 8 to 0.
This did not work.
Any suggestions for defeating this error would be greatly appreciated.
Thanks,
JeffJust a guess, but is the registry key relevant? It looks like something Excel-specific.
Tuesday, February 14, 2012
DTS import does not import all rows / records
I am having trouble importing data from an excel spreadsheet into MS
SQL Server 2000 using DTS Wizard. The DTS import process is
successfull, no errors, but only 50 rows of approx. 1500 rows of data
are imported. I tried to remove 20 rows in the excel spreadsheet in
the interval row 0-50. When i later ran the import, only 30 rows were
imported. I deleted almost every row in the interval 0-50, with the
result of the import having 0 rows imported (but job ran
successfully). I decided to delete rows 0-100 in the spreadsheet in
order to see if the resolved the problem, but it didn't. As I
suspected something in the excel file to be the cause, I exported the
excel spreadsheeet to a tab delimited textfile, with only one row. A
DTS import resulted in importing approx 100 rows, double the amount of
the textfile, but the other 1400 rows were not imported. The data in
the column is containing numeric values only.
Please help me! What could possibly be the cause of DTS skipping rows
like that. DTS doesn't feel reliable at all :/
Regards,
BjrnYou may want to check your spreadsheet to see if it has a named range
and if that is what you are using in DTS. If the named range only
includes rows 1-50 then DTS will only import those rows.
I'm not sure what you were doing with the text file, since you said
that you exported one row and it imported 100.
HTH,
-Tom.|||Maybe it would be a better idea to avoid the DTS Wizard. Make a local
package on your server and you will have much more control over it.
And if an error occures, DTS will tell you what kind of error that is.
Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
> Regards,
> Bjrn|||First save the excel sheet in csv format.
Then use DTS to import form the csv file.
Tawfiq.
Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount
of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
>
> Regards,
> Bjrn