I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.
I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:
Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).
I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.
Any help is appreciated.Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.|||This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.
I appreciate the help. Thank you.|||You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.
-PatP|||I double checked and I did specify an Excel file as the connection, not a text file.
It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?|||Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.
-PatP|||You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
DTS Package failure
I have a DTS package which keeps failing giving me the error message “A connection with the transaction manager was lost". The DTS package is using two different servers as connection and the DTS package has Use Transaction, Rollback transaction on failure and Join transaction if present ticked. I have checked the MSDTC service is running on both servers, the following options on both servers have been checked; Network DTC Access, Allow Inbound and Outbound. Does anybody know what is causing this error message?
Any suggestions much appreciated.
Regards
AmberThis is no longer a problem as we removed Use Transactions on the DTS package and added additional steps on failure to allow rollback.
Any suggestions much appreciated.
Regards
AmberThis is no longer a problem as we removed Use Transactions on the DTS package and added additional steps on failure to allow rollback.
DTS Package Excel Connection - Please Advise
Hi,
I'm using MS SQL 2000 and I want to export data from MS SQL database to MS Excel file. So, I choose the DTS package tool to do this job.
I try to create the connection to the Excel file, which located in the shared location by using the UNC path, e.g. \\servername\foldername\filename.xls However, I cannot provide the username/password to connect to the file's location. So, the connection does not work.
Do you have any idea how to create the Excel connection along with shared username/password ?
Anyway, if it is not possible :-( any advices about exporting MS SQL data to MS Excel sheet via DTS packages are still welcome....
(The reason that I prefer DTS package bec. I can schedule the job to run it automatically)
Thank you in advance.
You need not mention the user and password to the excel file, until unless you want to secure the access to excel file with a password.
KBA http://support.microsoft.com/kb/319951 for your information to perform this action without any issues.
|||Thanks, I have tested by sharing a folder on my machine and give access to "Everyone" account. It works fine 
However, with the real situation. The shared folder, which I have to place files in it, needs authentication. They don't allow shared folder to have a read/write access for "Everyone" account.
What should I do?
|||With a READ permission those users should be able to read the file on that shared folder, this has to be done using Windows explorer and SQL will not have any influence in this case. But if you are running this as a scheduled job then SQLAGent will do the rest if it has required privileges.DTS Package Excel Connection - Please Advise
Hi,
I'm using MS SQL 2000 and I want to export data from MS SQL
database to MS Excel file. So, I choose the DTS package tool to do
this job.
I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location. So, the connection
does not work.
Do you have any idea how to create the Excel connection along with
shared username/password ?
Anyway, if it is not possible :o any advices about exporting MS SQL
data to MS Excel sheet via DTS packages are still welcome...
(The reason that I prefer DTS package bec. I can schedule the job
to run it automatically)
Thank you in advance.I am betting the user context that the dts package is executing under dopes not have access to the UNC path.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."
To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.
Thanks|||Is your windows login having appropriate rights on that folder & files..?
To check try this...
1. Click Start Menu -> Run
2. Write \\servername\foldername\filename.xls -> Press OK
If the file opens, ensure that you are having appropriate rights & need to check something else.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."
To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.
Thanks
Thanks for all replies.
To clarify... A scheduled job that I created runs steps with TSQL command. I have a stored procedure to load and execute DTS package by database account. Since more and more securities are concerned, it is not possible for me to run the scheduled job with DTSRUN.exe via local windows account.
For more information: http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3
That's why I can't identify what the SQL-Agent login is because I didn't use it to run the jobs.|||OK
You mean you are using the following sp inside the job:
either
1. using integrated security: EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.IntSecurity=1
or
2. using the current connection via SUSER_NAME() EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.ServerPWD='xxxx', @.PkgPWD='xxxx'
But when a job is executed, specially for file access which is your case, SQL uses SQL-Agent NT login to access the file not the user login or sql login. Therfore you must know what the SQL-Agent login is and check if the SQL-Agent login has the access permission. We had lots of issues before accessing
Excel files in the network drive. Later on we found the issue came from the permission of SQL-Agent login.
So I suggest
1. You contact your SQL server administrator to inform you SQL-Agent NT login and request permission to access the file. - Best solution.
Or
2. You may create a macro in the Excel to import the data. - Hard to schedule. But I can give you hand for this.
Or
3. You may create vbscript to import the data into Excel. This, you may be able to schedule to run. Similar to #2.
I'm using MS SQL 2000 and I want to export data from MS SQL
database to MS Excel file. So, I choose the DTS package tool to do
this job.
I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location. So, the connection
does not work.
Do you have any idea how to create the Excel connection along with
shared username/password ?
Anyway, if it is not possible :o any advices about exporting MS SQL
data to MS Excel sheet via DTS packages are still welcome...
(The reason that I prefer DTS package bec. I can schedule the job
to run it automatically)
Thank you in advance.I am betting the user context that the dts package is executing under dopes not have access to the UNC path.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."
To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.
Thanks|||Is your windows login having appropriate rights on that folder & files..?
To check try this...
1. Click Start Menu -> Run
2. Write \\servername\foldername\filename.xls -> Press OK
If the file opens, ensure that you are having appropriate rights & need to check something else.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."
To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.
Thanks
Thanks for all replies.
To clarify... A scheduled job that I created runs steps with TSQL command. I have a stored procedure to load and execute DTS package by database account. Since more and more securities are concerned, it is not possible for me to run the scheduled job with DTSRUN.exe via local windows account.
For more information: http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3
That's why I can't identify what the SQL-Agent login is because I didn't use it to run the jobs.|||OK
You mean you are using the following sp inside the job:
either
1. using integrated security: EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.IntSecurity=1
or
2. using the current connection via SUSER_NAME() EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.ServerPWD='xxxx', @.PkgPWD='xxxx'
But when a job is executed, specially for file access which is your case, SQL uses SQL-Agent NT login to access the file not the user login or sql login. Therfore you must know what the SQL-Agent login is and check if the SQL-Agent login has the access permission. We had lots of issues before accessing
Excel files in the network drive. Later on we found the issue came from the permission of SQL-Agent login.
So I suggest
1. You contact your SQL server administrator to inform you SQL-Agent NT login and request permission to access the file. - Best solution.
Or
2. You may create a macro in the Excel to import the data. - Hard to schedule. But I can give you hand for this.
Or
3. You may create vbscript to import the data into Excel. This, you may be able to schedule to run. Similar to #2.
DTS Package error: TNS-12541: TNS:no listener
I have created a connection to an Oracle database. I am able to connect to
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> to
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> to
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
DTS Package error: TNS-12541: TNS:no listener
I have created a connection to an Oracle database. I am able to connect to
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell
|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> to
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell
|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> to
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
DTS Package error: TNS-12541: TNS:no listener
I have created a connection to an Oracle database. I am able to connect to
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> > I have created a connection to an Oracle database. I am able to connect
> to
> > one server and not to the other. The error message I get is : TNS-12541:
> > TNS:no listener
> >
> > Here are the steps I took before creating this connection:
> >
> > Oracle Client instaled on SQL Server
> > TNS Names file configured with oracle server and port
> >
> > Can someone tell me what to do? thanks
> >
> > James
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
one server and not to the other. The error message I get is : TNS-12541:
TNS:no listener
Here are the steps I took before creating this connection:
Oracle Client instaled on SQL Server
TNS Names file configured with oracle server and port
Can someone tell me what to do? thanks
James"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> I have created a connection to an Oracle database. I am able to connect
to
> one server and not to the other. The error message I get is : TNS-12541:
> TNS:no listener
> Here are the steps I took before creating this connection:
> Oracle Client instaled on SQL Server
> TNS Names file configured with oracle server and port
> Can someone tell me what to do? thanks
> James
Your TNSNames file needs to have an entry for each Oracle server that you
wish to connect to along with the appropriate credentials.
HTH
Rick Sawtell|||Rick,
Thank you so so much. I did have the right port in the TNSname file but not
the right host name. As soon as I fixed that credential, it worked fine.
Thanks again.
James
"Rick Sawtell" wrote:
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:3F163D43-04EE-4D5B-B9D8-22DEF0819C80@.microsoft.com...
> > I have created a connection to an Oracle database. I am able to connect
> to
> > one server and not to the other. The error message I get is : TNS-12541:
> > TNS:no listener
> >
> > Here are the steps I took before creating this connection:
> >
> > Oracle Client instaled on SQL Server
> > TNS Names file configured with oracle server and port
> >
> > Can someone tell me what to do? thanks
> >
> > James
> Your TNSNames file needs to have an entry for each Oracle server that you
> wish to connect to along with the appropriate credentials.
> HTH
> Rick Sawtell
>
>
DTS Package Error: Login failed for user sa
I am trying to edit a DTS package which is a transfer of data between two tables (connections)
i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa'
i am running enterprise manager on the server itself so i shouldn't have any connection issues.
if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:
' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:
S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?
S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?
S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?
i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa'
i am running enterprise manager on the server itself so i shouldn't have any connection issues.
if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:
' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:
S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?
S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?
S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?
Friday, March 9, 2012
DTS Package Copy
I want to copy the contents of one package into another package. I can brin
g
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a wa
y
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can br
ing
> up the 2 packages side by side and I can copy things like the Connection a
nd
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a
way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
g
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a wa
y
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can br
ing
> up the 2 packages side by side and I can copy things like the Connection a
nd
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a
way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
DTS Package Copy
I want to copy the contents of one package into another package. I can bring
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...
Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...
Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
DTS Package Copy
I want to copy the contents of one package into another package. I can bring
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
up the 2 packages side by side and I can copy things like the Connection and
the Text file, but I can't copy the transform between the 2 opjects...Is
there a way to copy the contents of one package into another? Is there a way
to copy the transform object between to packages in the DTS designer?
Thanks in advance for your assistance...Hi Jim
I am not a DTS expert, you may want to try the DTS newsgroup
microsoft.public.sqlserver.dts. I assume that you can not just call the
second package from the first.
John
"Jim Heavey" wrote:
> I want to copy the contents of one package into another package. I can bring
> up the 2 packages side by side and I can copy things like the Connection and
> the Text file, but I can't copy the transform between the 2 opjects...Is
> there a way to copy the contents of one package into another? Is there a way
> to copy the transform object between to packages in the DTS designer?
> Thanks in advance for your assistance...
DTS Package - Destination server is now off domain.
Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> JeremyWe have a DTS package that refreshes our external website data on a
nightly basis. I recently moved our external website off of our
domain. The SQL Services were previously running as
DOMAIN\Administrator account. When I moved the server off of the
domain, I reinstalled SQL Server and set the services to run as a
local, non-administrative account, MACHINE\sqlservices. The reason I
did this is, if someone were to compromise the machine via the SQL
Services, they would not have administrative access to the machine.
Now, my DTS package fails. I have been reading many posts on the
Internet to try to understand why the package fails, but I have not
been able to figure it out. When it fails, I am logged into the
Internal machine as DOMAIN\Administrator. The owner of the package is
also DOMAIN\Administrator.
Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
the specified database (DATABASENAME). It appears to be successful.
Step 2: (fails) - drop database DATABASENAME. This fails with the
following message: "Cannot drop the database 'DATABASENAME' because it
is currently in use." Checking enterprise manager, it does not appear
to be in use. I've cycled the SQL Services and rerun with no luck.
I have a feeling that this is an issue with security, but I can't
figure it out. Can someone please assist me?
Thank you in advance.
Jeremy|||Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> Jeremy
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> JeremyWe have a DTS package that refreshes our external website data on a
nightly basis. I recently moved our external website off of our
domain. The SQL Services were previously running as
DOMAIN\Administrator account. When I moved the server off of the
domain, I reinstalled SQL Server and set the services to run as a
local, non-administrative account, MACHINE\sqlservices. The reason I
did this is, if someone were to compromise the machine via the SQL
Services, they would not have administrative access to the machine.
Now, my DTS package fails. I have been reading many posts on the
Internet to try to understand why the package fails, but I have not
been able to figure it out. When it fails, I am logged into the
Internal machine as DOMAIN\Administrator. The owner of the package is
also DOMAIN\Administrator.
Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
the specified database (DATABASENAME). It appears to be successful.
Step 2: (fails) - drop database DATABASENAME. This fails with the
following message: "Cannot drop the database 'DATABASENAME' because it
is currently in use." Checking enterprise manager, it does not appear
to be in use. I've cycled the SQL Services and rerun with no luck.
I have a feeling that this is an issue with security, but I can't
figure it out. Can someone please assist me?
Thank you in advance.
Jeremy|||Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> Jeremy
Wednesday, March 7, 2012
DTS Oracle Select
Hi there,m
I have a DTS with an Oracle Connection. I transfers some data from oracle Db to SQL Server DB. I need convert my Oracle query into a parmerized query, i mean that the field [T1."DELV_SHIP_DATE_KEY"] can receive the dates like parameters.
I'd try to put:
T1."DELV_SHIP_DATE_KEY" between ? and ?
but it show me the next error when i try to set the parameter info:
Provider cannot deriver parameter information and
Setparameterinfo has not been called
Any idea??,
Maritzita
I use this select statement (for Oracle):
select T1."DELV_ID" "DELV_Delv Id",
T1."CUST_ID" "DELV_Cust Id",
T1."SHIP_CODE" "DELV_Ship Code",
T1."WHSE_ID" "DELV_Whse Id",
(T1."DELV_SHIP_TIME"*1.0e0)/100 "DELV_Delv Ship Time",
T1."DELV_SHIP_DATE_KEY" "DELV_Delv Ship Date Key",
T2."DELVI_ITEM_NO" "DELVI_Delvi Item No",
T2."ORD_ID" "DELVI_Ord Id",
T2."ORDI_ITEM_NO" "DELVI_Ordi Item No",
T2."DELVI_QTY" "DELVI_Delvi Qty",
T2."PROD_ID" "DELVI_Prod Id",
T2."DELVI_TOT_WGT" "DELVI_Delvi Tot Wgt",
T2."DELVI_TYPE_CODE" "DELVI_Delvi Type Code",
T3."ADR_CMPY_NAME1" "ADR_Adr Cmpy Name1",
T4."PROD_DESC_TEXT1" "PROD_Prod Desc Text1",
T4."PROD_ALT_CONV_FACTOR" "PROD_Prod Alt Conv Factor",
T5."ORDI_QTY" "ORDI_Ordi Qty",
T5."ORDI_ASM_GEN_ITEM_NO" "ORDI_Ordi Asm Gen Item No",
T5."ORDI_IS_UNIT_WGT" "ORDI_Ordi Is Unit Wgt",
T4."PROD_MKT_GROUP_CODE" "PROD_Prod Mkt Group Code"
from "VPEMGR"."DELV" T1,
"VPEMGR"."DELVI" T2,
"VPEMGR"."ADR" T3,
"VPEMGR"."ORDI" T5,
"VPEMGR"."PROD" T4
where T1."DELV_ID"=T2."DELV_ID" and
T1."ADR_ID"=T3."ADR_ID" and
T2."ORD_ID"=T5."ORD_ID" and
T2."ORDI_ITEM_NO"=T5."ORDI_ITEM_NO" and
T2."PROD_ID"=T4."PROD_ID" and
T2."DELVI_TYPE_CODE"='IQ' and
T1."DELV_SHIP_DATE_KEY" between '20030501' and '20031231' and
T1."DELV_SHIP_TIME"/100 between 0.00 and 24.00If I understand the question correctly, I think that you will have better luck with OPENQUERY.
hmscott|||I saw this solution on help from SQL, but i culdn't understand it so much. Can u tell me where i can find more information about it? and if i use this funcionality, how i have to do for pass this results of my query to my SQl database without using a data transformation task?
I have many quetions about it.
tks,
Maritzita
Originally posted by hmscott
If I understand the question correctly, I think that you will have better luck with OPENQUERY.
hmscott|||Look under OPENQUERY in SQL Books On Line. They give an example that specifically shows an Oracle Server. I think that the only thing you will have to do is build your Query String (with the substituted parameters) as a variable. I'm not positive, however, that this will work. Example:
DECLARE @.SQL Varchar(4000)
SELECT @.SQL =
'SELECT Columns
FROM MyTable
WHERE MyDate BETWEEN TO_DATE(' + '12/4/2003' + ') AND
TO_DATE(' + '12/5/2003' + ')'
GO
SELECT *
FROM OPENQUERY(OracleSvr, @.SQL)
GO
I have a DTS with an Oracle Connection. I transfers some data from oracle Db to SQL Server DB. I need convert my Oracle query into a parmerized query, i mean that the field [T1."DELV_SHIP_DATE_KEY"] can receive the dates like parameters.
I'd try to put:
T1."DELV_SHIP_DATE_KEY" between ? and ?
but it show me the next error when i try to set the parameter info:
Provider cannot deriver parameter information and
Setparameterinfo has not been called
Any idea??,
Maritzita
I use this select statement (for Oracle):
select T1."DELV_ID" "DELV_Delv Id",
T1."CUST_ID" "DELV_Cust Id",
T1."SHIP_CODE" "DELV_Ship Code",
T1."WHSE_ID" "DELV_Whse Id",
(T1."DELV_SHIP_TIME"*1.0e0)/100 "DELV_Delv Ship Time",
T1."DELV_SHIP_DATE_KEY" "DELV_Delv Ship Date Key",
T2."DELVI_ITEM_NO" "DELVI_Delvi Item No",
T2."ORD_ID" "DELVI_Ord Id",
T2."ORDI_ITEM_NO" "DELVI_Ordi Item No",
T2."DELVI_QTY" "DELVI_Delvi Qty",
T2."PROD_ID" "DELVI_Prod Id",
T2."DELVI_TOT_WGT" "DELVI_Delvi Tot Wgt",
T2."DELVI_TYPE_CODE" "DELVI_Delvi Type Code",
T3."ADR_CMPY_NAME1" "ADR_Adr Cmpy Name1",
T4."PROD_DESC_TEXT1" "PROD_Prod Desc Text1",
T4."PROD_ALT_CONV_FACTOR" "PROD_Prod Alt Conv Factor",
T5."ORDI_QTY" "ORDI_Ordi Qty",
T5."ORDI_ASM_GEN_ITEM_NO" "ORDI_Ordi Asm Gen Item No",
T5."ORDI_IS_UNIT_WGT" "ORDI_Ordi Is Unit Wgt",
T4."PROD_MKT_GROUP_CODE" "PROD_Prod Mkt Group Code"
from "VPEMGR"."DELV" T1,
"VPEMGR"."DELVI" T2,
"VPEMGR"."ADR" T3,
"VPEMGR"."ORDI" T5,
"VPEMGR"."PROD" T4
where T1."DELV_ID"=T2."DELV_ID" and
T1."ADR_ID"=T3."ADR_ID" and
T2."ORD_ID"=T5."ORD_ID" and
T2."ORDI_ITEM_NO"=T5."ORDI_ITEM_NO" and
T2."PROD_ID"=T4."PROD_ID" and
T2."DELVI_TYPE_CODE"='IQ' and
T1."DELV_SHIP_DATE_KEY" between '20030501' and '20031231' and
T1."DELV_SHIP_TIME"/100 between 0.00 and 24.00If I understand the question correctly, I think that you will have better luck with OPENQUERY.
hmscott|||I saw this solution on help from SQL, but i culdn't understand it so much. Can u tell me where i can find more information about it? and if i use this funcionality, how i have to do for pass this results of my query to my SQl database without using a data transformation task?
I have many quetions about it.
tks,
Maritzita
Originally posted by hmscott
If I understand the question correctly, I think that you will have better luck with OPENQUERY.
hmscott|||Look under OPENQUERY in SQL Books On Line. They give an example that specifically shows an Oracle Server. I think that the only thing you will have to do is build your Query String (with the substituted parameters) as a variable. I'm not positive, however, that this will work. Example:
DECLARE @.SQL Varchar(4000)
SELECT @.SQL =
'SELECT Columns
FROM MyTable
WHERE MyDate BETWEEN TO_DATE(' + '12/4/2003' + ') AND
TO_DATE(' + '12/5/2003' + ')'
GO
SELECT *
FROM OPENQUERY(OracleSvr, @.SQL)
GO
DTS Oracle Connection - Importing Data
I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.
If I have the following information:
TNS Name:
ThisApp.world
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpxxx.world)
(PROTOCOL = TCP)
(Host = SomeHost)
(Port = 1234)
)
)
(CONNECT_DATA =
(SID = App)
(GLOBAL_NAME = ThisApp.world)
)
)
And I also have the name of the Table.
Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:
Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:
I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?
Your assistance will be greatly appreciated.Given the above information, how can I use Server Explorer in VS.NET 2002? It still prompts me for a Username/Password, which I do not have.
Please help anyone.
Friday, February 17, 2012
DTS import problem with text datatype from SQL Server to MySQL table
Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17?
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".Can you not export the data into a text file and import it into mysql ?|||You also can export the data into a file with .csv and import it into mysql.|||Thanks rnealejr and gyuan
Even with export to text file and then import that text file to MySQL gives the similar error -
Error Source: Microsoft OLE DB provider for ODBC drivers
Error Description: The number of failing rows exceeds the maximum specified.Query-based Insertion or updating of BLOB values is not supported.
I have also tried MySQL connection with mysql-oledb-3.0.0 but it seems it has some problems as it swaps the database name and server name parameters each time I open for edit or test connections.|||Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/|||Originally posted by Brett Kaiser
Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/
Thanks
I have checked http://www.dbforums.com/f5 and found a similar posting but no useful replies.
How to import a SQL Server table with text column to a similar table in MySQL database?|||What type of cursor are you using ?|||All DTS defaults. I am using DTS package tasks and connection objects with their defaults.|||Has anybody transfered the data from SQL Server table having text column to MySQL database?|||I understand this could be a ODBC problem of handling the text data but
When I use "MySQL.OLEDB Provider" instead of "MySQL ODBC 3.1 Driver" to make a connection with MySQL in DTS package and setup a Transform Data Task between SQL Server(source connection) and MySQL(destination connection) I see all the MySQL tables appended with "INFORMATION_SCHEMA." in the dropdown list of destination table names in Transform Data Task Properties whereas there is no user or database named INFORMATION_SCHEMA in MySQL database.
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".Can you not export the data into a text file and import it into mysql ?|||You also can export the data into a file with .csv and import it into mysql.|||Thanks rnealejr and gyuan
Even with export to text file and then import that text file to MySQL gives the similar error -
Error Source: Microsoft OLE DB provider for ODBC drivers
Error Description: The number of failing rows exceeds the maximum specified.Query-based Insertion or updating of BLOB values is not supported.
I have also tried MySQL connection with mysql-oledb-3.0.0 but it seems it has some problems as it swaps the database name and server name parameters each time I open for edit or test connections.|||Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/|||Originally posted by Brett Kaiser
Doesn't sound like a sql server problem...
try here
http://www.dbforums.com/f5/
Thanks
I have checked http://www.dbforums.com/f5 and found a similar posting but no useful replies.
How to import a SQL Server table with text column to a similar table in MySQL database?|||What type of cursor are you using ?|||All DTS defaults. I am using DTS package tasks and connection objects with their defaults.|||Has anybody transfered the data from SQL Server table having text column to MySQL database?|||I understand this could be a ODBC problem of handling the text data but
When I use "MySQL.OLEDB Provider" instead of "MySQL ODBC 3.1 Driver" to make a connection with MySQL in DTS package and setup a Transform Data Task between SQL Server(source connection) and MySQL(destination connection) I see all the MySQL tables appended with "INFORMATION_SCHEMA." in the dropdown list of destination table names in Transform Data Task Properties whereas there is no user or database named INFORMATION_SCHEMA in MySQL database.
Tuesday, February 14, 2012
DTS Help
You can add SQL Task with respective connection and
validate your data. When successfuly completed copy your
objects.
Joanna
>--Original Message--
>I have a package that contains an Copy Objects Task. I
want to have a step
>to check a value in another table before executing the
copy task. Not sure
>how to accomplish this. Any help would be greatly
appreciated. Thanks.
>.
>Ok. How would I make not execute the next step if the validation failed?
"Joanna" wrote:
> You can add SQL Task with respective connection and
> validate your data. When successfuly completed copy your
> objects.
> Joanna
>
> want to have a step
> copy task. Not sure
> appreciated. Thanks.
>
validate your data. When successfuly completed copy your
objects.
Joanna
>--Original Message--
>I have a package that contains an Copy Objects Task. I
want to have a step
>to check a value in another table before executing the
copy task. Not sure
>how to accomplish this. Any help would be greatly
appreciated. Thanks.
>.
>Ok. How would I make not execute the next step if the validation failed?
"Joanna" wrote:
> You can add SQL Task with respective connection and
> validate your data. When successfuly completed copy your
> objects.
> Joanna
>
> want to have a step
> copy task. Not sure
> appreciated. Thanks.
>
Subscribe to:
Posts (Atom)