Source And Target - Both Behind Firewall Using NAT
Nov 14, 2005In this situation do I need a proxy or forwarder at both ends to prevent connection issues? Are there plans to handle this in future SSSB upgrades. Thanks.
View 8 Replies
ADVERTISEMENT
Target And Source Columns DataType
Nov 27, 2011I need to create an Bulk upload utility using ASP.Net and SQL Server. Below is the process for the uploads -
Excel Template wherein user will enter the details. A Tab-delimited output file will be generated using the VBA.
There are 2 tables - one is Temp Table which is replica of the the final table and second is the final table
Using File.OpenText(filePath).ReadLine() - All the Rows from the tab delimited data file will be inserted into DataTable.
using SQLBulkCopy the tab-delimited data file data will be inserted into the Temp Table.
Data will be validated based on the data inserted in the temp table. If the data as errors then the temp table will be cleared else the data will be inserted from the temp table to the final table.
My Issue is that in both the tables there is a column (Name : PeopleKey (Int PrimaryKey)). If the user enters Alphabetic value then the Bulk Utility is failing. Below are the two options in my mind -
1. I can change the DataType in Temp table from int to VARCHAR. So, the data can be inserted at first and then I can validate and get the data corrected. But i am not sure whether it is the right way to fix issue as the source and target tables columns are different.
2. When the data in inserted into the Datatable by following Step 3. So, once the data in inserted into DataTable then i can validate there. Thus the source and target tables Datatype will be same.
Flat File Source To Oracle OLE DB Target (ETL)
Jul 20, 2007What could be simpler: map a flat file record structure, extract the data, and populate essentially the same flat file record struc in an Oracle table. Let the fun begin.
Specifically: the flat file record struc is fixed length 196 bytes. A particular field consists of 4 bytes of Integer data; IS deals very nicely with the definition, does not appear to be any issue with that. The issue is trying to get the 4 bytes of integer to map and load into the Oracle table. The data type in the flat file def is DT_UI4. The data type in the Oracle target is DT_NUMERIC. One would think that perhaps a simple transform and Viola?! I've defined the transform but does not seem to matter - whatever I try yeilds the same results.
I 've tried many different src/trg data type defs., but all yeild the same results.
Execution Results from debug:
Everything validates and then...
[kcd [8671]] Error: Data conversion failed. The data conversion for column "load_time_min" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[kcd [8671]] Error: The "output column "load_time_min" (11050)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "load_time_min" (11050)" specifies failure on error. An error occurred on the specified object of the specified component.
Any ideas appreciated!
Thanks.
Getting Details When We Migrate The Data From Source To Target In SSIS
Nov 14, 2006Hi
Can anyone help me out in getting the information or execution progress of a package like "number of records migrated", "which component is getting executed at present",etc...when we migrate the datas using the package which we have created programmatically and trying to execute the package programatically.We can see these informations in the the "progress tab" when we execute the package using BIDS in SSIS.
Thanks in advance
Regards
Getting Id Values From Both Source And Target Rows When Duplicating Records
Aug 27, 2007Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @number
DTS - Split Single Source Record (text) To Multiple Target (sql)
Aug 31, 2000I am using DTS and VBScript in DataPump tasks in order to transfer large amounts of data from text files to an SQL database.
As the database uses a normalized schema, there is often the case of inserting multiple records in a destination table from various fields of the same record of the source text file.
For example, if the source record contains information about goods sold like date, customer, item code, item name and total amount, and does so for a maximum of 3 goods per sale (row), therefore has the structure:
[date], [custid], [code1], [name1], [amount1], [code2], [name2], [amount2], [code3], [name3], [amount3]
trying to transfer that record to a [SALES] target table (in a normalized database), we would have to split each source record as follows:
[date], [custid], [code1], [name1], [amount1]
[date], [custid], [code2], [name2], [amount2]
[date], [custid], [code3], [name3], [amount3]
What is the best way to do this using DTS?
I have tried using a datapump task and VBScript, and I guess it has to do with the DTSTransformStat_**** constants, but none of those I used seems to work
Vasilis Siatravanis,
siatravanisv@interamerican.gr , vasilliss@hotmail.com
SQL 2012 :: SSIS - Updating Source And Target Data Structure
Feb 17, 2015I have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".
View 2 Replies View RelatedSSIS As A ETL Tool For Conversion Where Source And Target Data Is Both On Non SQL Server.
Feb 12, 2008
Hi, I am less of a technical but more of a analyst professional and right now investgating on various tools / options for the new conversion project I will be leading in insurance client. One of the tools that client want to use is SSIS but the source and target database is not on SQL server but plans are to build a staging SQL server database for transformation. Does SSID supports this kind of ETL process where both source and target system are non SQL servers.
Thanks,
H Gill
Integration Services :: Compare Source And Target Data Using Conditional Split
Aug 12, 2015I'm encountering a very peculiar situation when I'm trying to compare source and target data using conditional split. Following is the Data Flow and how I'm trying to achieve this.
Source Data : Col_A (PK)Â Â Â Â Â Col_2
                      1                   100
                      8                   500
Target Data : Col_A (PK)Â Â Â Â Â Col_2
                      1                   100
                      3                   700
                      8                   500
Look-up Target on Col_A to check for existing records. Now we have four columns in Look-up match output: Col_A, Col_B, Lkp_Col_A (Target Col), Lkp_Col_B (Target Col).
Conditional Split: Compare Col_B with Lkp_Col_B
Update target if there is any change in the existing value of Col_B.When I'm running the package for every record in source, the conditional split fails and even when there is no change in Col_B, some of the records (Not all and quite randomly) get updated with the same value. If I run the package for few records, it works absolutely fine.
Row Count Validation Between Source Flat File To Target Database Table
Aug 8, 2006Hi all,
I am new in SSIS. Anyone know how to valify number of record that I load from csv file to SQL database table?
For example, the source file call product.csv and target table in database named DSS table name PRODUCT. I load data from flat file to table then I need verification if count between source and target not match send e-mail to me.
Thanks.
Grace
Create Target Table Dynamically Based On Source Table Schema?
Sep 13, 2005I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
I Want To Join One Table From The Source To The Other Table In The Target Diff Database
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
Update Target Table Using Metadata And Source Table
Oct 4, 2013I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data.
declare @Stop table (StopId int, UserField varchar(20))
declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))
declare @UpdateSource table (HasPathway varchar(10))
insert into @Stop (StopId, UserField)
values (1, 'Yes')
[code]...
I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.
Firewall Again
May 30, 2001Hi guys.
We have an external webserver/sqlbox not on domain network and internal on domain.
I need to register both servers or need to do a linked server between two.
There is something i found in Multi protocol. Will somebody explain the whole process.
If i need to change the port settings in SQL, is that just the Client neetwork utility I should change or should i run the setup.
Any input appreciated.....
:-)
-MAK
Connecting To SQL DB Through A Firewall
Dec 1, 2004Hi there!
I'm a bit new to all this, so please bear with me! :)
I've got a webserver in our DMZ and I'm trying to create an ODBC connection from that server to a db server within our firewall. When I try and connect, the following message appears:
Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()),
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied
I'm at a bit of a loss as to what's going on, as we have an application on the webserver that connects to another SQL DB server within our firewall with no problem!
webserver:
OS - win2000 standard server sp4
db server:
OS - WinNT 4.0
SQL - 7.0
If anyone can help, it would be much appreciated!!
Cheers,
Ewan :)
Can't Use SQL Server With Firewall
Jul 20, 2005My ISP recently had me reset my TCP/IP stack. After that, Norton PersonalFirewall prompted me twice that SQL Server was trying to access theInternet. Both times I responded to allow it to and to always use thataction. Now I am not able to use SQL Server with NPF enabled. If I disableNPF, SQL Server works fine.I am using the desktop edition of SQL Server 7, on a standalone PC, notconnected to a server. I have been using SQL Server and NPF together forover a year. Now, since my TCP/IP stack was reset, NPF interferes with SQLServer.Anyone have any experience with this?Thanks,Neil
View 3 Replies View RelatedDTS Package And Firewall
Jul 20, 2005Hi all, I have some DTS packages that are used to import /export data to aSQL box located outside in a DMZ behind a firewall.We need to open up a port in the firewall so that the Internal Server cancommunicate (Execute DTS packages against) with the SQL box located outsidethe DMZ.How do I find out what Port we need to open so that the Internal SQL box cancommunicate with the external SQL box?Thanks in advanceMark
View 1 Replies View RelatedFTP Task And FIREWALL
May 6, 2008Hello,
I have created a new dtsx package with a FTP Task inside.
On this task I am able to configure the user, password server address etc...
Everything is working correctly until I try to connect to a FTP in DMZ.
Of course I have a firewall to access to it...So my question is how to bypass proxy settings in the FTP task component.
I do it in FileZilla so how can I do the same thing in ftp task?
Regards
Stephane
Data Connection Behind Firewall
Apr 17, 2007Hello,
I am trying to connect a web app to a SQL 2005 that is behind a firewall.
The challenge is this, the SQL is behind a firewall and the webserver is at a different location. From my desktop development environment, I connect through a VPN first and then can point to the internal IP of the DB server. This works fine as long as the VPN is connected.
But how do I do this from a hosted web application that is not on my local machine or using my VPN?
Is there some sort of tunneling Connection that I can use within code?
thanks
ZoneAlarm Pro(firewall) Causes SQL Problems
Nov 6, 2004When I changed to software Firewall, I got lots of problems running asp.net and Dnn.
Unfortunatly zonelabs does not support me, cause ZA pro don't "officially" support servers. But speaking to their support, and via email, I am told several of their customers run ZA pro under winXP, with IIS and SQL running.(ZA pro 5.1.033.000)
Unfortunatly I have not been able to get them to read my thead on Zonelabs support forum.
I bought ZA pro after talking on with a salesrep, and was told IIS and SQL is a breeze to config, and if I run into problems their forum or support would help me. Yeah right. So at the time beeing I am stucked.
I first started out installing, and making the most obvious changes, and got everthing working I thought. But when trying to log into a dnn project in my root, nothing happend.
So, I knew the firewall didn't play along. Then I gave SQL server rights, 4 green checkmarks in config. Now I could log in. But installing new modules was still a problem. I tried to add "formcreator for dnn" by Snow to a tab. But I got a msg saying a critical error accourd. "SQL server doed not excist or access denied". This is what I have done so far:
1. In Privacy Settings Cookie Control, Ad Blocking and Mobile Code Control are all set to Off
2. Added localhost: Clicked Firewall >> Zones >> Add >> Host/Site name OR IP Address: 127.0.0.1
3. Made sure IIS, and SQL Server have full access: Click Program Control >> Programs.
So as you see, the firewall is still not allowing the application (dnn application) to connect to the SQL server. Surely their must be a way to fix this?
Anyone that has managed to get ZAP to work correct along with IIS and SQL?
clue
Asp.net Application Connect To SQL Through A Firewall
May 7, 2002Hi guys.
We have an external webserver(server1 on domain1) is a DMZ, and sql server7(server2 on domain2) inside the firewall.
Domain1 trusts domain2 but domain2 does not trust domain1.
Our firewall hosting by outside vendor and they told me they already opened up the port 1433 for server2.
I put a small asp.net application on server1(this application works fine in intranet. 2000 server,iis5,virtue directory, and .net frameword were well installed and configured), and I got S'QL server does not exist or login fail error'.
Any idea about that? And how to test the port was opened?
Any input appreciated.....
Multiprotocol Replication Through A Firewall
Mar 4, 2000OK, here's the situation. I have set up my two server to use MultiProtocol as their net-lib and locked it down to use only port 1433 instead of "port hopping". I have set up my firewall to allow the traffic on port 1433 between the 2 machines and I added a hosts entry at each machine for the partner machine. With this set up I can do everything that I need to and have not found any limitations until now.
THE PROBLEM: I publish a db (on either machine) and enable immediate updating subscriptions. I then push this db to the other machine. Everything works fine and updates on the publishing db are replicated to the subscribing databases without any problems. However, when I try to do an update on the subscribing database I get an error that "transaction error, ODBC driver [SQLOLEDB] does not support distributed transactions..." No matter which machine I configure as the publisher or the subscriber this error always occurs when trying to update the subscriber. Also, as soon as I disable the firewall the error goes away and updates go back and forth like normal. I've double checked and all packets go through the firewall on port 1433 and none are refused. Not sure where to go from here and any help would be appreciated....
THANKS,
- Matt K.
kanema@saic.com
SQL7 Replication Thru Firewall
Dec 23, 2000Hi,
Need to set up replication between a server in our corporate network and another outside our network. Both are NT 4 servers running SQL7 and I am instructed to configure merge replication on both ends. My questions are: Is it possible to achieve this by running tcpip(read somewhere that I would need to run MPR to load the net libraries) and configuring the firewall to allow tcp/ip on the ports we are running SQL on between the two servers?
Please help as this is very urgent.
Regards
Kunta
Connecting To SQL Server Outside Firewall
Jan 6, 2000I am trying to connect to SQL Server 7.0 from a web server. The web server is outside our firewall, SQL Server is inside the firewall. I want to use a multiprotocol connection so I can encrypt it. We have port 1433 open on our firewall for TCP/IP from SQL Server. When I connect with just TCP/IP it works. When I switch to multiprotocol it fails with error code 1701. I can connect to the SQL Server box from inside the firewall using multiprotocol.
My theory is that when you use multiprotocol you are using another port than 1433. Is this the answer?
Connecting To SQL Server Through Firewall
Aug 3, 2004I have a simple asp page that queries a database inside our firewall. It works fine from the inside, and only uses port 1433. But if i put it out on our web server it won't connect and tries to use ports 139 and 445. Has anybody ran across this before?
Thanks,
John
SQL Server Sercurity Outside The Firewall ...
Jul 20, 2005Hello,We use Informix and MySQL on linux/unix to drive our web application.SQL*Server is used only for backend enterprise applications within thefirewall. I am trying to get the management to use SQL*Server outside thefirewall. They tell me there are security issues with Microsoft products,including SQL*Server, that make it vulnerable to attacks outside thefirewall. Can someone please point me to white papers/documentation thatsuggests how SQL*Server can be used securely outside the firewall? I thinkif I put SQL*server on it's own box and open it up only to the applicationson our web servers, we should be secure. However, I need hard evidence.Thanks.- Rajesh
View 2 Replies View RelatedBest Way To Update Database Behind Firewall?
Jul 20, 2005HelloI have a project where I need to update a few tables in a SQL Server 2000database which resides behind a firewall, ie port 1433 is blocked. I alsoneed to verify the updates were successful.I am a C# developer. What are my options in doing this.. web services?John Dalberg
View 1 Replies View RelatedRemote Database Connection Through Firewall
Jan 29, 2001I'm trying to adminster a remote MS SQL server from inside my company's firewall. I have the correct permissions to pass traffic through the firewall, but I need to authenticate to it first. When I try to register this remote SQL server through Enterpise Manager, it of course does not let me because of the firewall. Is there any way I can pass the firewall ID and password during the server registration process, and then pass the SQL server ID and password? I'm no SQL server expert by any means, but it doesn't look like it's possible. Are there any third party utilities that may accomplish this?
Thanks for the help!
Aaron
Connecting To A Names Instance Through A Firewall.
Mar 12, 2007Hi
I am really struggling with this problem.
I am connecting to several SQL 2000 servers on another LAN from my workstation and I have to go through a firewall.
I got the firewall people to open port 1433 and was then able to connect any server with a default instance using Query Anlyzer and the IP address of the Server.
When I try to connect from my workstation to a server with a named instance I am being blocked by the firewall before it gets to the server.
I went into Client Network Utility on my workstation and removed named pipes which only left Tcpip using port 1433.
Fire wall is now blocking “nbname UDP 137”.
Is there a setting somewhere on my workstation that I can force the traffic to the named instance to use Tcpip through port 1433.
Any Help would be great.
SQL 2012 :: SSIS Catalog FTP And Firewall
Oct 3, 2014I am running a package in the catalog. The package contains a c# script that uses FTP webrequest. It runs fine in SSDT. Trying to execute from the catalog the ftp list directory is timing out.
This seems to be a firewall issue. We have tried adding an exception for dtexec, but that did not . I am thinking another program runs the package, maybe ISServer.exe?
Any program that would need the firewall exception to run the package in the catalog?
SQL 2012 :: SSIS Firewall Port
Aug 5, 2015We have SSIS packages deployed and scheduled on SQL_Server_A. The packages have to access databases on SQL_Server_B, which is behind a firewall.
Question: would opening only SQL port between these two servers be enough or some other ports have to be open as well? (The packages only select and bring data back to Server_A or transfer data from Server_A to the databases and do inserts. Nothing else.)
Firewall Port Forwarding To SQL Server
Apr 25, 2008Hi,
Is there any concerns or problems with forward a port to SQL server
from our internet firewall so that you can access the databases over the internet? Is it a standard practice to do this in order to remotely access the SQL server. Or is there a better way to do this? Also, if it is ok to do this are any things you need to do to enhance your security from vulnerabilities being open to the internet.
Any suggestions or comments would be appreciated on this subject.
Thanks
Remote SqlExpress Connection And Firewall
Apr 12, 2008I am running SqlExpress on a Vista machine as a server and accessing it from machines running XP. I could not connect with the Vista server until I turned off the firewall, despite creating exceptions for both the sqlexpress and sqlbrowser services in the firewall setup. Obviously, I don't want to run on a wireless network without a firewall for any length of time. What else to I need to enable in order to run with my firewall up? In SMSS, I could 'see' the Vista server on the network with the firewall up, but I could not 'see' the SERVERSQLEXPRESS service or connect to it .
thanks,
Steve