Hi,
I want to transfer data from an Informix database using DTS. What driver do I need to use for that? What are the settings for the Data Source Name? Has anyone of you accomplished DTS from Informix to SQL Server?
Let me know if you have any ideas that you can share.
Thank you.
VivekYou can get the informix oledb provider from the informix client sdk cd - then use that provider in your dts package.|||There is a known bug in that package. Check out IBM website for more information on that. It will save you a lot headaches.|||Which version and bugs ...|||Also, as with any informix product - make sure you read the release notes.|||How much will I have to pay for this driver that you are talking about?|||Also what version of the Informix Client SDK should I get hold of? I have no experience in Informix.|||Here is the link to ibm/informix client sdk (includes release notes):
link (http://www-306.ibm.com/software/data/informix/tools/csdk/)
Showing posts with label source. Show all posts
Showing posts with label source. Show all posts
Thursday, March 29, 2012
DTS from Excel to SQL?
I tried to use DTS for copying a sheet from Excel to SQL. For some
reason, the last column shows up error saying source's column 5 buffer
is too big.. and failed it.. is there anything that I need to watch
out?ebug@.hotmail.com (Kelvin) wrote in message news:<191e0546.0404261422.4bad806@.posting.google.com>...
> I tried to use DTS for copying a sheet from Excel to SQL. For some
> reason, the last column shows up error saying source's column 5 buffer
> is too big.. and failed it.. is there anything that I need to watch
> out?
reason, the last column shows up error saying source's column 5 buffer
is too big.. and failed it.. is there anything that I need to watch
out?ebug@.hotmail.com (Kelvin) wrote in message news:<191e0546.0404261422.4bad806@.posting.google.com>...
> I tried to use DTS for copying a sheet from Excel to SQL. For some
> reason, the last column shows up error saying source's column 5 buffer
> is too big.. and failed it.. is there anything that I need to watch
> out?
Does this apply to your case?
http://support.microsoft.com/defaul...kb;EN-US;281517
Simon
DTS from excel file (excel filename is different everyday)
Good Day to all,
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:
http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this
1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D
Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function
There's a syntax error. I will debug this later.
3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.
Please help :o|||Hi upalsen,
I got it already!
I followed your instructions. Many thanks to you. :)
Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:
100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX
wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.
Here's how my ActiveX Script looks like:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************
Option Explicit
Function Main()
Dim vDay, vMonth, vYear, vDate
vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear
DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"
Main = DTSTaskExecResult_Success
End Function
Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code
Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if
Main = DTSTaskExecResult_Success
End Function
in your starting ActiveX script consider vCounter and write code to get branch code for each value
if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....
.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,
Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.
And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF
I saw it in the Dynamic Property Task under Steps. Am I correct?
Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.
if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"
yes, u r right. step names r listed in dynamic prop under "steps" heading.
Hope you could help me w/ my project.
Im creating a DTS Package. The source data will be coming from an excel file going to my SQL table. The DTS package is scheduled to execute daily, but the source data will be coming from different excel filename.
Example, today the DTS will get data from Data092506.xls. Then tomorrow, the data will be coming from Data092606.xls.
How can I do this? The DTS I've already done has a fixed source data file.
Please help.
Thank you so much.
God Bless.You will need to create a variable in your DTS package for the file name, and then construct the filename dynamically.|||Hi blindman,
I can't seem to figure out how will I do that.
Could you be more specific, pls.
Thanks for taking the time to answer my queries.
God Bless.|||Look here:
http://www.sqldts.com/default.aspx?234|||use the following DTS steps for this
1) create a Global variable of name say "aa" of string type
2) add a ActiveX Task where u assign the value of global variable from system date. something like
DTSGlobalVariables("aa").Value = "d:\Data" & "0" & month(date()) & day(date()) & year(date()) & ".xls"
3) add a Dynamic Property task. select the Excel connection and assign the "data Source" to that global variable.
4) place a work flow so that the execution sequence is ActiveX>>Dynamic Prop>>Other Steps that u already have.|||Hi,
I can't seem to get it yet. I am presented w/ so many information from all the websites and help files that I am reading, and I end up more confused. :eek:
I'm a newbie in SQL and I need instructions for dummies. :D
Here's what I did:
1.) I created a global variable named gVarPath through the DTS Package Properties.
2.) I'm adding now a "ActiveX Script" Task in the DTS Designer. Here's my script:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Main = DTSTaskExecResult_Success
DTSGlobalVariables("gVarPath").Value="D:\PROJECTS\Attendance-Excel\" & RIGHT('0'+ RTRIM(CAST(MONTH(GETDATE()-2) AS CHAR)),2) & RIGHT('0'+ RTRIM(CAST(DAY(GETDATE()-2) AS CHAR)),2) & RIGHT(YEAR(GETDATE()-2),2) & "_ALB.xls"
End Function
There's a syntax error. I will debug this later.
3.) I'm adding a "Dynamic Properties" task.
Question: Where can I select the excel connection? And how can I assign the data source to my global variable?
4.) And how can I place a workflow.
Please help :o|||Hi upalsen,
I got it already!
I followed your instructions. Many thanks to you. :)
Now, I have another question.:D
I need to import data from 24 excel files everyday. Excel filenames are like these:
100206_AAA
100206_BBB
100206_CCC
up to
100206_XXX
wherein 100206 is a date which I already knew how to alter for everyday DTS package execution. The last 3 characters are the branch code, in which we have 24 branches (ex. 100206_AAA, 100206_BBB,...100206_XXX).
How can I make a loop, so I can run the DTS package 24 times. Each run will get data from each excel files.
Here's how my ActiveX Script looks like:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* *********************
Option Explicit
Function Main()
Dim vDay, vMonth, vYear, vDate
vDay=RIGHT(RTRIM("0" & DAY(DATE()-2)),2)
vMonth=RIGHT(RTRIM("0" & MONTH(DATE()-2)),2)
vYear=RIGHT(YEAR(DATE()-2),2)
vDate=vMonth & vDay & vYear
DTSGlobalVariables("gVarPath").Value=vDate & "_AAA.xls"
Main = DTSTaskExecResult_Success
End Function
Thank you so much... :)
God Bless.|||i am not sure if those branch codes r really fixed and hardcoded as AAA, BBB etc? or they will come from another table? assuming they are hard coded, u can ...
create another global variable, say vCounter. start with vCounter=1. add another ActiveX step. put it at the end of the existing workflow. add the following code
Function Main()
if vCounter <= 24 then
vCounter = vCounter+1
DTSGlobalVariables.Parent.Steps ("<NAME_OF_STEP1>").ExecutionStatus = DTSStepExecStat_Waiting
end if
Main = DTSTaskExecResult_Success
End Function
in your starting ActiveX script consider vCounter and write code to get branch code for each value
if vCounter = 1 then
BrCode = "AAA"
elseif vCounter .....
.......
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"|||Hi upalsen,
Yup. The branch codes are fixed and will be hardcoded.
Following your instructions, I created another global variable named "gVarCounter". How can I referenced "gVarCounter" in my Dynamic Properties Task? In my first global variable "gVarPath", I referenced it by assigning the data source of the excel connection to it.
And another question, how will I know the ("<NAME OF STEP1>")?
Here's my ActiveX script:
IF gVarCounter<=24 then
gVarCounter=gVarCounter+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
END IF
I saw it in the Dynamic Property Task under Steps. Am I correct?
Thank you so much. :)|||u need not reference gVarCounter in your dynamic property task. all that u need to do is use gVarCounter in preparing the value of your previous gVarPath variable. like below. and dynamic prop will still use only gVarPath.
if gVarCounter = 1 then
BrCode = "AAA"
elseif gVarCounter=2 then
BrCode = "BBB"
.....
DTSGlobalVariables("gVarPath").Value=vDate & "_" & BrCode & ".xls"
yes, u r right. step names r listed in dynamic prop under "steps" heading.
DTS from AS 400 to Sql server
Hi DBA's:
I need to transfer data from my source database which is Client Access iSeries AS 400
running DB 400 to my database in SQL Server 2000. Here is what I need to do -
There are files in the Production Enviornment on the DB 400. I need to connect to these files from my SQL Server 2000 database using DTS Wizard and tranfer these files into the tables of my SQL Server 2000 database. Can someone walk me through with the options on the source side of the DTS that I need to choose in order to connect to my AS 400 and get the data.
Thanks in anticipation.I'm assuming you mean DB2, not DB 400...
Look up sp_addlinkedserver in BOL
I need to transfer data from my source database which is Client Access iSeries AS 400
running DB 400 to my database in SQL Server 2000. Here is what I need to do -
There are files in the Production Enviornment on the DB 400. I need to connect to these files from my SQL Server 2000 database using DTS Wizard and tranfer these files into the tables of my SQL Server 2000 database. Can someone walk me through with the options on the source side of the DTS that I need to choose in order to connect to my AS 400 and get the data.
Thanks in anticipation.I'm assuming you mean DB2, not DB 400...
Look up sp_addlinkedserver in BOL
DTS from 2 sources ??
hi
i want to load Data from 2 sources (access and mysql) the 2 source have
same schema ;how to load to cube the data and retrive the data of each
source and how configure DTS to continue from the last ID of the last
load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) ?
john1425
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1052215.html
simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) ?
>
> --
> john1425
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1052215.html
>
sql
i want to load Data from 2 sources (access and mysql) the 2 source have
same schema ;how to load to cube the data and retrive the data of each
source and how configure DTS to continue from the last ID of the last
load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) ?
john1425
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1052215.html
simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) ?
>
> --
> john1425
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1052215.html
>
sql
DTS from 2 sources ??
hi
i want to load Data from 2 sources (access and mysql) the 2 source have same
schema ;how to load to cube the data and retrive the data of each source an
d how configure DTS to continue from the last ID of the last load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) 'simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) '
>
> --
> john1425
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1052215.html
>
i want to load Data from 2 sources (access and mysql) the 2 source have same
schema ;how to load to cube the data and retrive the data of each source an
d how configure DTS to continue from the last ID of the last load exp :
source1 : product(id,,name)
(1,nokia 3310)
source 2 : product(id,,name)
(1,nokia 3310) 'simply create a package where you do a lookup to get the last ID from a copy
of your table, then load the mysql database with a filter on the last ID,
then do the lookup again to get the last ID again and load the access table.
(store the lastid into a dts variable, and use it has a parameter for a
filter "select * from table where id > ?")
you'll have 1 copy of the table without duplicated IDs.
But regarding your configuration, you can directly merge information using
linked servers.
In SQL Server create 2 linked servers (1 to access 1 to mysql)
create a view like: select ... from mysql.db..table union select ... from
access...table
the union eliminate duplicated rows (but its a slow solution)
there is other solutions.
"john1425" <john1425.1orlsz@.mail.webservertalk.com> wrote in message
news:john1425.1orlsz@.mail.webservertalk.com...
> hi
> i want to load Data from 2 sources (access and mysql) the 2 source have
> same schema ;how to load to cube the data and retrive the data of each
> source and how configure DTS to continue from the last ID of the last
> load exp :
> source1 : product(id,,name)
> (1,nokia 3310)
> source 2 : product(id,,name)
> (1,nokia 3310) '
>
> --
> john1425
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1052215.html
>
DTS for Import Export TO And From EXCEL
Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>
Tuesday, March 27, 2012
DTS Fail when IDENTITY(1,1)
Hi,
i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.
e.g
Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)
Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)
i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.
i m using DTSDataPump in package to transform data.
waiting for ur reply|||Does the table have existing data?
NO
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable
Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.
-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP
yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO
INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO
SELECT * FROM Table1
GO
INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO
SELECT * FROM Table2
GO
SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]
u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.
Why are you bothering with a DTS package at all?|||That's what I don't get.
Why are you bothering with a DTS package at all?
i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.
whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.
:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this
CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:
Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text
Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"
'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep
'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With
'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True
'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask
objPackage.Execute
End Sub
i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.
e.g
Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)
Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)
i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.
i m using DTSDataPump in package to transform data.
waiting for ur reply|||Does the table have existing data?
NO
Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable
Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.
-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.
-PatP
yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO
INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO
SELECT * FROM Table1
GO
INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO
SELECT * FROM Table2
GO
SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]
u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.
Why are you bothering with a DTS package at all?|||That's what I don't get.
Why are you bothering with a DTS package at all?
i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.
whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.
:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this
CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:
Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text
Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"
'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep
'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With
'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True
'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf
.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask
objPackage.Execute
End Sub
Sunday, March 25, 2012
DTS Error: Importing from Excel file to SQL Server 2000
Data for Source Column 15 'Notes' is too large for the specified buffer size.
How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype totext
I have never seen this error when importing before. What do I do?
An error I've gotten way too much. Some things you can try:
- The Excel provider (JET) often makes assumptions based on the first 8 lines of the file, and sometimes that is the problem).
- Or you can save to comma separated or tab separated file format, and import that way.
Oftentimes, I cannot figure out the problem as well. I've tried posting before, but no answer. The only thing I come up with is to save to tab-delimited, and import that way.
Sorry I can't be more help; hopefully someone has the answer.
DTS error on GUID column
I am doing a DTS from table "source" to table "destination". However
"destination" has a primary key of data type uniqueidentifier and the source
doesn't have a corresponding map field. So I thought setting the
"destination" primary key to have a default value of newId() will
automatically insert a guid for every record inserted (just like a regular
identity key) but unfortunately this doesn't work. I get a message that says
"cannot insert null into the field fieldName" which makes sense because it i
s
the PK but why doesn't the newId() generate an automatic GUID? Or is it even
possible? or could I change the transformation script to make it work and if
so, how? Thanks for any help.On guid primary key column undid primary key constraint, set allow null, set
default value to newId() and DTS worked fine and new guids got inserted.
After DTS set all constraints back as original. So everything's good.
"Naveen" wrote:
> I am doing a DTS from table "source" to table "destination". However
> "destination" has a primary key of data type uniqueidentifier and the sour
ce
> doesn't have a corresponding map field. So I thought setting the
> "destination" primary key to have a default value of newId() will
> automatically insert a guid for every record inserted (just like a regular
> identity key) but unfortunately this doesn't work. I get a message that sa
ys
> "cannot insert null into the field fieldName" which makes sense because it
is
> the PK but why doesn't the newId() generate an automatic GUID? Or is it ev
en
> possible? or could I change the transformation script to make it work and
if
> so, how? Thanks for any help.
"destination" has a primary key of data type uniqueidentifier and the source
doesn't have a corresponding map field. So I thought setting the
"destination" primary key to have a default value of newId() will
automatically insert a guid for every record inserted (just like a regular
identity key) but unfortunately this doesn't work. I get a message that says
"cannot insert null into the field fieldName" which makes sense because it i
s
the PK but why doesn't the newId() generate an automatic GUID? Or is it even
possible? or could I change the transformation script to make it work and if
so, how? Thanks for any help.On guid primary key column undid primary key constraint, set allow null, set
default value to newId() and DTS worked fine and new guids got inserted.
After DTS set all constraints back as original. So everything's good.
"Naveen" wrote:
> I am doing a DTS from table "source" to table "destination". However
> "destination" has a primary key of data type uniqueidentifier and the sour
ce
> doesn't have a corresponding map field. So I thought setting the
> "destination" primary key to have a default value of newId() will
> automatically insert a guid for every record inserted (just like a regular
> identity key) but unfortunately this doesn't work. I get a message that sa
ys
> "cannot insert null into the field fieldName" which makes sense because it
is
> the PK but why doesn't the newId() generate an automatic GUID? Or is it ev
en
> possible? or could I change the transformation script to make it work and
if
> so, how? Thanks for any help.
DTS error [DNETLIB]
I am copying files from one database to another. Identical tables that I
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
Tom
Now I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
Tom
Now I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
DTS error [DNETLIB]
I am copying files from one database to another. Identical tables that I
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
DTS error [DNETLIB]
I am copying files from one database to another. Identical tables that I
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
created by just Generating SQL Scripts from the source database and running
it on my new database.
This works from most of my files.
One error I am getting for a few of tables is: [DNETLIB].
What is this?
Thanks,
TomNow I cannot even connect if I right click the database icon and choose
export data.
When I go to the first screen: Choose Data Source.
I get the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB]
Context: Error during initialization of the provider.
How can that be?
I just ran this a couple of minutes ago and had no problem with it. I can
look at the tables fine in Sql Enterprise Manager.
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7QAaFW7GHA.2384@.TK2MSFTNGP04.phx.gbl...
>I am copying files from one database to another. Identical tables that I
>created by just Generating SQL Scripts from the source database and running
>it on my new database.
> This works from most of my files.
> One error I am getting for a few of tables is: [DNETLIB].
> What is this?
> Thanks,
> Tom
>
DTS Error - Data too large for specified buffer size
I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
Scott
It's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
Scott
It's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>
DTS Error - Data too large for specified buffer size
I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
ScottIt's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
ScottIt's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>
Thursday, March 22, 2012
DTS Error
We are getting an error message that states error source:
Microsoft source oledb provider for sql server,
unspecified error. Invalid columname usertyp. This is
happening when we try to create a dts package between sql
server database and access database. Any idea on how to
slove this problem or what is going on.Use Profiler to see whats up.
>--Original Message--
>We are getting an error message that states error source:
>Microsoft source oledb provider for sql server,
>unspecified error. Invalid columname usertyp. This is
>happening when we try to create a dts package between sql
>server database and access database. Any idea on how to
>slove this problem or what is going on.
>.
>
Microsoft source oledb provider for sql server,
unspecified error. Invalid columname usertyp. This is
happening when we try to create a dts package between sql
server database and access database. Any idea on how to
slove this problem or what is going on.Use Profiler to see whats up.
>--Original Message--
>We are getting an error message that states error source:
>Microsoft source oledb provider for sql server,
>unspecified error. Invalid columname usertyp. This is
>happening when we try to create a dts package between sql
>server database and access database. Any idea on how to
>slove this problem or what is going on.
>.
>
DTS Error
Hi,
Can any body help me to sort out this problem
I am running a DTS package source as dbf file and destination as sqlserver database the following error occurs
for row number 2035 errors encountered so far on this
task 1. insert error, column 3 ('arch_dt', DB_TYPE DBTIMESTAMP)
when i was opening dbf file in foxbase its showing (arch_dt field) date as datatype the same when i was importing to access the datatype is changing to text type.
so daily manually i am opening in excel file and delete the records where the line error occurs.
in excel file that particular row will have some text format instead of dateformat.
how can i solve this error without manual interaction..
with regards
rajkumarIf you insert it as a chracter datatype then you can check the value and correct it.
You could also probably use an activex script on it but that would be slow.
Can any body help me to sort out this problem
I am running a DTS package source as dbf file and destination as sqlserver database the following error occurs
for row number 2035 errors encountered so far on this
task 1. insert error, column 3 ('arch_dt', DB_TYPE DBTIMESTAMP)
when i was opening dbf file in foxbase its showing (arch_dt field) date as datatype the same when i was importing to access the datatype is changing to text type.
so daily manually i am opening in excel file and delete the records where the line error occurs.
in excel file that particular row will have some text format instead of dateformat.
how can i solve this error without manual interaction..
with regards
rajkumarIf you insert it as a chracter datatype then you can check the value and correct it.
You could also probably use an activex script on it but that would be slow.
DTS DataPump from a Stored Procedure
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.
Paulo
Originally posted by andrewsc
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.
Paulo
Originally posted by andrewsc
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?
Wednesday, March 21, 2012
DTS- Data Driven Query Task
I use a data driven query task to perform row updates along with appending row data to a text file. In the source I use 'select top 1000 field1, ..., fieldn from table'. The package hangs when executing the data driven task. If I reduce the return count to 'select top 100...' the package execute with no problems. I can do that 10 times, with no problems. But anything more then 100 will hang the package at that task. Please help!
Thanks,
MoniqueTake help from PROFILER and see where it hangs.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.
Thanks,
MoniqueTake help from PROFILER and see where it hangs.
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.
DTS copy table from source DB->destination DB
I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination
I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.
I just want to transfer the data and schedule it but when I ran the package it said:
'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'
I re-opened the 'Data Driven Query Task'
I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:
INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()
But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.
Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?
Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.
So, when I run it now I get the error
'The data driven query task requires at least one query (and associated columns) to be specified'
I don't want to set any queries. I just want the transformation to run when i execute the package.
Any ideas?|||Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>
Thanks,
I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.
i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.
I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.
I just want to transfer the data and schedule it but when I ran the package it said:
'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'
I re-opened the 'Data Driven Query Task'
I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:
INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()
But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.
Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?
Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.
So, when I run it now I get the error
'The data driven query task requires at least one query (and associated columns) to be specified'
I don't want to set any queries. I just want the transformation to run when i execute the package.
Any ideas?|||Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?
For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation
Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>
Thanks,
I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.
i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.
Subscribe to:
Posts (Atom)