Connection Problem To Connect To TeraData From SSIS
Feb 10, 2008
Hi
I have a problem to connect to TeraData from SSIS.
I have the TeraData client (Query Man) that I connect to the TeraData using ODBC connection. When I connect with Query Man I can see the objects (views) of the correct scheme. However, when I try to connect with SSIS using the same ODBC connection €“ I don€™t see the objects of the specific scheme. The way that I connect to TeraData using SSIS is creating DS connection (with odbc) and then I create DSV €“ where this is the point that I can see lots of other schemes objects, but not the scheme€™s objects that I can see in the Query Man.
Can anyone assist?
I'm using SQL Server 2005 Enterprise on a Win2003 x64. I want to connect to a Teradata 2.6, but there are no x64 ODBC- or OLE-DB-drivers for Teradata yet. So, only OLE-DB can't be used as the 32-Bit OLE-DB-driver for Teradata is not visible e.g. if you want to create a linked-server-connection. One workaround would be to use "OLE-DB for ODBC", but this OLE-DB is also not available on my system.
Does anybody know a way to connect SQL 2005 x64 to Teradata or to get "OLE-DB for ODBC" up and running???
There is an entry to a similar issue (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=699116&SiteID=1) where the workaround "use 64Bit-ODBC with .net ODBC" (?) puzzles me!
I'm new to SSIS, using Terdata Attunity connector for integrating data flow between Terdata (source) to SQL Server (Target).
SSIS package is getting failed because of length mismatch between source and Target for Unicode character datatype columns. Reason is Teradata TPT always occupies 3 times more length of actual defined in DB.Â
Even I tried by increasing length of attribute in Source but it didn't work.
I know by converting datatype from unicode -> Latin would work, but i don't want to do conversion since loosing some characters.
################################################## Error is [Teradata Source [263]] Error: TPT Export error encountered during Initiate phase. TPTAPI_INFRA: API306: Error: Conflicting data length for column(5) -  Source column's data length is (200) Target column's data length is (300). ##################################################
I€™ve developed a BI system using the SQL Server 2005 Developer€™s Edition.
SSAS, SSIS and SSRS have been added to a SQL Server 2005 Standard Edition Server in preparation for delivering the developed database to this live environment.
So I am able to connect directly to the Database Engine, SSAS, SSIS and SSRS successfully from the actual SQL Server 2005 Standard Edition box which is sat on a Windows 2003 Server operating system.
However when I launch SQL Server Management Studio from a desk top PC which is connected to the SQL Server Standard Edition box I can€™t connect to either SSIS or SSRS, however I can connect to the Database Engine and it also seems that I can connect to SSAS OK.
I am using Windows Authentication where the I used a SQL Server account which exists with €śsysadmin€? permissions and this SQL Server account has the same login name as the Windows Server 2003 desktop login.
The TCP/IP connection to the host has failed. java.net.ConnectException: Connection timed out: connect" whenever i try to connect to the remote DB which is present at a different system.
Hi. I'm (very) new to SQL Server. I have run into what seems to be a very common problem, judging by the number of Google hits, but it's not actually in the FAQs here, as far as I can see.
Situation I installed SQL Server Express with Advanced Services 2005 on my Windows XP Pro laptop, using (with one exception) the default install. The exception was to put the data files on a separate partition (D: ) where I have more free space.
SQL Server Management Studio Express connects happily, and I set up a single data import table.
Issue: bcp Fails to connect to default local instance I initially tried bcp in output mode, prior to working in earnest on importing my data. However the command line-
bcp MyDatabaseName.dbo.UserProfile out UserProfile.txt -T -c
Fails with the error-
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
As I understand it, the local instance of SSE is the default, and should use a local (shared memory?) connection. This seems to work for Management Studion Express, but not bcp.
While investigating this problem, I tried enabling remote connections (all types) through the Surface Area Configuration wizard, and found that remote connections using either of:
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S localhostsqlexpress bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S mycomputernamesqlexpress
both work ok.
Extra Info When starting Management Studio Express, it says something like "Configuring environment for the first time". However it says this every time it is started, not just the first run after installation, does this indicate some configuration data is not being successfully stored?
I have found threads that look similar to my issue-
However these do not relate directly to bcp, and to be honest my knowledge of SSE is slender enough at this stage that i'm unsure if there are really relevant.
Both the OLE DB Connection and ADO.Net Connection in SSIS Package does not remember password.
Im connecting to a SQL Server 2000 box using its sa password as test.
The SSIS package runs fine when you first set up the connection in bids
The bottom line is that SSIS keeps forgetting the password I feed into the two Connections that I'm using. I double-click a connection, type the password in, check "Save my password" and hit "OK" but the password disappears from there whenever I run the package or double-click the connection again.
is there any known workaround for this issue as I would like to schedule my SSIS package using a SSIS Step in a SQL Server 2005 Agent job.
the only thing I found when googling this error was link below but the workaround described here is a little harsh
I'm getting "Access denied" error when I try to connect to SSIS from my desktop (Database Engine is fine). I'm a member of sysadmin within SQL, as well as an administrator on the server. I don't have any problem when I log on to the server directly via Remote Desktop and open SSIS.
I went into the surface area config tool, and I see that Database Engine and Analysis Services both expand and have a sub tabs to enable Services and Remote Connections, but Integration Services does not expand to a Remote Connections option, just Services. We're running MSDN Enterprise edition.
Does it matter if all the SQL services are using "Local System" for the logon ? I use an administrative logon on my SQL2000 boxes, and was fiddling with them on the 2005 box yesterday, but it seemed to cause more problems, so they are all Local System for now.
I am trying to query Teradata tables from SQL Server 2005 using SSIS that will populate a table in SS 2005. I have been able to test the connection successfully. I choose the Query option b.c the tables in Teradata are over 100 million records and I don't want to copy those tables locally.
I am able to enter my query and parse it. I can even preview the query and see the results. A very simple query:
select distinct acc_yr from d4574fdp.clm_dim
But when I try and execute the query, I get the following error message:
quote: TITLE: SQL Server Import and Export Wizard ------------------------------
Could not connect source component.
Error 0xc0202009: Source - Query [1]: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "OLE DB Provider for Teradata" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
this is the error message I found when I run my asp.net application and the connection string as follows: <add key="ConnectionInfo" value="Server=(local);Database=Dive;Trusted_Connection=yes;" /> can someone tell me how to connect and how to specify username and password in Visual Web Developer 2005 Express Edition.......... I'm a little bit new on VWD 2005 I need an assistance on it.
I am trying to connect to a database and run a query using vbscript. The problem I have run into currently is that I can not get the connection to open even though I know the DSN name it still says it can not find it. I am fairly new to vbscript so I am not sure exactly what it is looking for in the connection string so that it will find the database. Any ideas?
I have no issues connecting OLE DB manager with variables (basically i am passing source and destination) but i have issues to connect SMO Connection Manager
I tried to pass the variable into SqlServerName on SMO connectin properties..no luck... tried on control flow item (Transfer SQL server Objects Task) with sourceconnection and Soruce db .. no luck.
I am trying to transfer objects from one server to another server but I'll pass source and destination server names dynamically thru variables..
How do you setup a conntection in ssis to connect to an access mdb? I have used DTS to upload access tables to sql and want to do the same in the new ssis services. I migrated the DTS package but the connection in created for the access mdb does not work in ssis and is shown to be be illegal. The migration did not post any errors when it migrated the package.
Wether I'm trying to access a datapump task in an imported package in SSIS, or in 2000 enterprise manager
they crash once created. I can not alter or redo mappings or anything else. After enterprise manager churns for 5-10 min it crashes.
SSIS partially crashes: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. After this I can no longer click on anything and have to end task on Visual studio.
I can do a disconnected edit on the package in enterprise manager and alter the statement, but as far as I know you can't really change much as far as mappings back in there. This also means that my 2000 packages have been limited to 1 teradata pump per package.
This happened on my laptop, 2 coworker's desktops, and now that i've migrated to a new high end developer box it's happening on it also.
I'm trying to convert old DTS packages over to SSIS and also create new SSIS packages that use teradata as a warehouse source for many different departments.
So far i've tried what I can find here on the forums:
using special syntax and wrapping the sql command in an alias/variable name
Methods i've tried: using an ODBC connection string that works just fine in VB for pulling data. - test connection succeeds but when I select data reader source I get:
Error at Data Flow Task [DataReader Source[185]]: Cannot acquire a managed connection from the run-time connection manager
Using an OLE DB connection where I get the standard error many have gotten and I tried the listed use special syntax and wrapping the sql command in an alias/variable name from : MSDN Error at Data Flow Task [Source - Testing]: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "OLE DB Provider for Teradata" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Currently i'm testing/trying this on a very simple view that produces 2 columns and 173 rows of string data. I have not tried the .net provider and am reluctant to accept that as the only solution if it works as it would involve permissions issues and red tape here at the bank and is a less user-friendly solution for my dev team.
Any ideas?
Driver version on my machine and likely the other 5 that would need this to work: 3.03.00.01 12/17/2003
I have a 32 bit development/playbox SQL 2005 SSIS that can see and run 32 bit OLE DB drivers from Teradata. Granted I have the same issues as others where a table or a view would work and not a query or a variable query. The alternative is to use Linked Server and OPENQUERY.
But our real development box is a 64 bit IA 64 using 64 bit SQL 2005. Non of the Teradata.com drivers would work/show up in the dropdown box of the Connection Manager since they are 32 bit. One option is to use the 64 bit Teradata ODBC drivers and then use the .Net ODBC connection instead. Linked server doesn't work either because the SQL 2005 is 64 bit and it is looking for 64 bit drivers.
Has anyone have any experience with 64 bit SQL 2005 and Teradata?
Looked at other 3rd party vendors such as ETI but their documentation excludes IA 64 (Itanium).
Planning to try again using the 64 bit ODBC and .Net Adapter but I'm interested to hear if someone (MSFT & NCR) would have a real solution for 64 bit.
I want to add a step to an SSIS package to passthrough native Teradata sql statements so that they can be executed on Teradata. Does anyone know if I can use an "Execute SQL Task" to accomplish this? It should be very similar to passing native Oracle SQL to an Oracle server.
I am not able to connect to SQL Database server using windows authentication when I am connected through VPN connection.However I am able to connect to same server using SQL server authentication and also able to do RDP to DB server machine.
I found a resolution in one of the forum suggesting to change UserRasCredentials to 0 value in *.pdk file.Since VPN connection which I am using generates instant proxy this resolution doesn't work.
Also application which I am using does windows authentication connection it is required for me to impersonate windows authetication when connected using VPN.
I was wondering if anyone could tell me why I could not connect to my server with the DAC when the server CPU utilization for all CPU's was greater than 90%.
BACKGROUND: I have successfully established DAC connections to this server in other periods when the CPU utilization was not pinned so high. I have done so by RDP'ing into the server, opening SSMS and cancelling the request to connect Object Explorer. Finally, I start a new query adding the "Admin" prefix to my server.
As I mentioned, when the server has CPU resource available, this works fine. This indicates that all the groundwork to setting up and using the DAC is laid correctly (i.e. DAC is enabled, I am connected locally, I am logged in with a user role with proper permissions, etc.).
This leads to my question. Why can I not connect to SQL Server 2005 Standard Edition using the DAC when all the (8-way) CPU utilization is at 90% or higher?
I need to make this connection to analyze why the server is so busy.
I am running SQL 2005 SP1 on Win 2003 Std x64 SP2. I get an error trying to connect using an ODBC System DSN connection. The ODBC connection gets the following error:
Connection failed: SQLState:'28000' SQL Server Error: 18456 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'
The SQL Server logs the following error:
18456, Severity: 14, State: 8.
I have tried this with several SQL accounts. I am able to login to the SQL Manager with the accounts without issue. It is only when trying to connect via ODBC that I get the error. I am currently downloading SQL 2005 SP2 to see if this will help with the issue. Any other ideas would be greatly appreciated.
i have SQLServer2000 and 2005 installed,established one connection each from Query Analyze(SQLServer2000 ) and Microsoft SQL Server Management Studio - Query (SqlServer2005). Written a script to disconnect all connection.It work fine in case of SQLServer 2005 but do not kill connection from Query Analyze(SQLServer2000 ). On quering sys.dm_exec_sessions do not show any connection.But when i execute the query from Query Analyze(SQLServer2000 ) it return the result in place of disconnect error.
DECLARE @spid INT
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR
SET @getspid = CURSOR FOR
select session_id from sys.dm_exec_sessions where session_id>28 and
host_name NOT IN ('xxx') and program_name in ('SQL Query Analyzer','SQL Query Analyzer - Object Browser',
'SQLCMD','OSQL-32','Microsoft SQL Server Management Studio')
OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
EXEC(@tString)
Print @tString
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid
Please let me know why Query Analyze(SQLServer2000) connection disconnect on killing the respective spid. Regards Sufian
There are two machines(A and B) in intranet . The network between the machines is well. I can't use client in machine A to connect the sql server in Machine B until i set the "connection timeout" a bigger number such as 30 seconds. If i use the default "connection timeout" to connect , it will return a error message " Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" But The correspondence of executing sql command is very quick after the connection is established.
Why need i bigger "connection timeout"？ Is that a configuration problem?
I just can't get it to connect to the database. Says something that remote connections are not allowed. I've done the Area thing and allowed all connection including remote connections.
i am using sql2005 on win2k3(sbs) platform(domain concept).my client is winxp.when i am tring to connect from client pc i am getting following error message.
connection failed sql state 01000 sql server error 233 microsoft odbc sql server driver(dbnetlib) connection open (connect) connection failed sql state 08001 sql server error 17 microsoft odbc sql server driver(dbnetlib)sql server does not exist or access denied
Hi I am new to .net and sql server....i have just installed VS 2005 and sql server7.0... I am doing my coding in vb.net......so i wanted to know the connection of the datagrid to the db in my sql server 7.0. Can some one pls help me with this urgently.
I've been informed that our data feed systems are going to be shut down and that our data will be residing in Teradata V2R5 databases. I was wondering if anyone knows if you can get connectivity to a Teradata server via Linked Server in the Security folder of SQL Server Enterprise Manager? If no then; Is it possible through a DTS package? Thanks for the info...
I am extracting data from an SQLServer database to load into Teradata using DTS. The performance is abysmal. The same data in a text file loads quickly via multiload. I can move the data to other DBMSs via DTS quickly as well. Is there some way for me to improve the elapsed time/performance while using DTS? If not, what is the best way to move data from SQLServer into Teradata?
