I am completely new to SSIS. Is there a way to get SSIS to work with a SQL server 2000 database. Is there a SQL 2000 Add in that can be utilized. Basically I want to save the SSIS package to the SQL 2000 database and schedule it to run.
I have the following situation: A webserver in a DMZ which connects to a DbServer in a Domain.
but when I try to make a ODBC connection on the webserver I get the well-known "Server does not exist or access denied" error.
I tried using IP address as well as FQDN. I turned off the firewall in the router (I'm able to access shares and what not from the webserver so the firewall is really turned off).
I think however i have narrowed down the problem to the TCP/IP connection of the SQL Server. Normally when you try from a command prompt "telnet dbserver 1433" you get a connection. But when I try this to the specific DbServer I get an "unable to connect" error. Even when I try it on the dbserver itself (so telnet localhost 1433). This should work always?
In the network configuration of the sql server the Named Piped as well as the TCP/IP protocols are enabled. TCP/IP is offcourse configured on port 1433.
I have a query that joins with different table using link server in the database. SSSI doesn€™t take the query. How to make the SSIS package query to use the link server in the data base ?
I am getting an error saying incorrect syntax near fIt works in SQL Server 2005, but I cannot get it to work in SQL Server 2000 The error appears to be in the section that I marked in Bold. CREATE PROCEDURE [dbo].[pe_getReport] -- Add the parameters for the stored procedure here @BranchID INT, @InvestorID INT, @Status INT, @QCAssigned INT, @LoanOfficer nvarChar(40), @FromCloseDate DateTime, @ToCloseDate DateTime, @OrderBy nvarChar(50)ASDECLARE @l_Sql NVarChar(4000), @l_OrderBy NVarChar(500), @l_OrderCol NVarChar(150), @l_CountSql NVarChar(4000), @l_Where NVarChar(4000), @l_SortDir nvarChar(4)BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @l_Where = N' Where 1=1' IF (@BranchID IS NOT NULL) SET @l_Where = @l_Where + N' AND f.BranchID=' + CAST(@BranchID As NVarChar) IF (@Status IS NOT NULL) SET @l_Where = @l_Where + N' AND f.Status=' + CAST(@Status As NVarChar) IF (@InvestorID IS NOT NULL) SET @l_Where = @l_Where + N' AND f.InvestorID=' + CAST(@InvestorID As NVarChar) IF (@QCAssigned IS NOT NULL) SET @l_Where = @l_Where + N' AND f.QCAssigned=' + CAST(@QCAssigned As NVarChar) IF (@LoanOfficer IS NOT NULL) SET @l_Where = @l_Where + N' AND f.LoanOfficer LIKE ''' + @LoanOfficer + '%''' IF (@FromCloseDate IS NOT NULL) SET @l_Where = @l_Where + N' AND f.ClosingDate>=''' + CAST(@FromCloseDate AS NVarChar) + '''' IF (@ToCloseDate IS NOT NULL) SET @l_Where = @l_Where + N' AND f.ClosingDate<=''' + CAST(@ToCloseDate AS NVarChar) + '''' IF @OrderBy IS NULL SET @OrderBy = 'DateEntered DESC' SET @l_SortDir = SUBSTRING(@OrderBy, CHARINDEX(' ', @OrderBy) + 1, LEN(@OrderBy)) SET @l_OrderCol = SUBSTRING(@OrderBy, 1, NULLIF(CHARINDEX(' ', @OrderBy) - 1, -1)) IF @l_OrderCol = 'InvestorName' SET @l_OrderBy = 'i.InvestorName ' + @l_SortDir ELSE IF @l_OrderCol = 'BName' SET @l_OrderBy = 'b.BName ' + @l_SortDir ELSE IF @l_OrderCol = 'StatusDesc' SET @l_OrderBy = 's.StatusDesc ' + @l_SortDir ELSE IF @l_OrderCol = 'QCAssigned' SET @l_OrderBy = 'q.LoginName ' + @l_SortDir ELSE SET @l_OrderBy = 'f.' + @l_OrderCol + ' ' + @l_SortDir SET @l_CountSql = 'SELECT f.FundedID As FundedID FROM FundedInfo AS f LEFT OUTER JOIN Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN QCLogins AS q f.QCAssigned = q.LoginID ' + @l_Where + ' ORDER BY ' + @l_OrderBy CREATE TABLE #RsltTable (ID int IDENTITY PRIMARY KEY, FundedID int) INSERT INTO #RsltTable(FundedID) EXECUTE (@l_CountSql)SELECT f.DateEntered As DateEntered, f.LastName As LastName, f.LoanNumber As LoanNumber, f.LoanOfficer As LoanOfficer, f.ClosingDate As ClosingDate, i.InvestorName As InvestorName, b.BName As BName, s.StatusDesc As StatusDesc, q.LoginName As LoginNameFROM FundedInfo AS f LEFT OUTER JOIN Investors AS i ON f.InvestorID = i.InvestorID LEFT OUTER JOIN Branches AS b ON f.BranchID = b.BranchID LEFT OUTER JOIN Status AS s ON f.Status = s.StatusID LEFT OUTER JOIN QCLogins As q ON f.QCAssigned = q.LoginID WHERE FundedID IN(SELECT FundedID FROM #rsltTable) ORDER BY CASE @OrderBy WHEN 'DateEntered ASC' THEN f.DateEntered END ASC, CASE @OrderBy WHEN 'DateEntered DESC' THEN f.DateEntered END DESC, CASE @OrderBy WHEN 'LastName ASC' THEN f.LastName END ASC, CASE @OrderBy WHEN 'LastName DESC' THEN f.LastName END DESC, CASE @OrderBy WHEN 'LoanNumber ASC' THEN f.LoanNumber END ASC, CASE @OrderBy WHEN 'LoanNumber DESC' THEN f.LoanNumber END DESC, CASE @OrderBy WHEN 'LoanOfficer ASC' THEN f.LoanOfficer END ASC, CASE @OrderBy WHEN 'LoanOfficer DESC' THEN f.LoanOfficer END DESC, CASE @OrderBy WHEN 'ClosingDate ASC' THEN f.ClosingDate END ASC, CASE @OrderBy WHEN 'ClosingDate DESC' THEN f.ClosingDate END DESC, CASE @OrderBy WHEN 'InvestorName ASC' THEN i.InvestorName END ASC, CASE @OrderBy WHEN 'InvestorName DESC' THEN i.InvestorName END DESC, CASE @OrderBy WHEN 'BName ASC' THEN b.BName END ASC, CASE @OrderBy WHEN 'BName DESC' THEN b.BName END DESC, CASE @OrderBy WHEN 'StatusDesc ASC' THEN s.StatusDesc END ASC, CASE @OrderBy WHEN 'StatusDesc DESC' THEN s.StatusDesc END DESC, CASE @OrderBy WHEN 'LoginName ASC' THEN q.LoginName END ASC, CASE @OrderBy WHEN 'LoginName DESC' THEN q.LoginName END DESCENDGO
I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!
I'm having issues getting this to work under Windows Server 2003 X64.
I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....
My question is: Were do I get the X64 version of these tools?
sqlce30setupen.msi sql2Ken@P4.msi
The SQL environment is X86 as follows: SQL2000 SP4
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
In one of our forth coming projects, with ASP.Net/C#/MSSQL Server, We have to deal with a Business table having about 15 millions of records. We want to know, that which methodologies should we adopt, both regarding front end and back end perspective, so the site could give optimised performance. Also in place of a Dedicated Server, the Hosting Company provides MSDE (that come with .net). Will this create any problem with this project, that have such a huge table? Should we go for some advanced database technique, such as, Clustering, Spliting Tables, etc.
Followings are the fields that the business table contains:
ID, Category ID (which comes from a Category table, each business is under a category), BusinessName, SignupDate, Address1, Address2, Phone Number, Hours Of Operation, Years in Business, LicenseNumber, DiscountCoupon, Website
I am thinking about updating to SQL 2005. Currently running SQL 2000, and have a couple hundred SQL CAL 2000 device licenses. Can I upgrade to SQL 2005 without purchasing new device licenses? Thanks, Kevin
I have installed SSIS on server running SQL 2000 but I don't see the Business Intelligence or SSIS. I could see the SSIS service is running but do not see VS or BI version of VS.
Hi all., I am new to SSIS,if it is a wrong place to post this, please forgive me. How to work with SSIS., I need to use SSIS in my project., Please can any one tell a link to learn how to work with SSIS.,
We have DTS packages in SQL Server 2000, which populates the data from SQL 2000 to Ms-Access tables. We are moving our database from SQL Server 2000 to SQL Server 2005.
Why & How & What is the best approach to migrate DTS packages?
Migrate SQL Server 2000 DTS to SQL Server 2005 DTS Or SSIS
Is SQL 2000 clustering on windows 2000 any good ??
We are looking at spending *quite* a bit of money to implement it, but I need opinions of what its like from the people that actually use it & look after it.
e.g.
DOES IT WORK LIKE IT SHOULD???????? Is it reliable? Is it resource hungry? Are there lots of bugs?
All repsonses very welcome. No response too small.
I have a server (win server 2003) that is running Symantec Backup Exec for backups. I found that this soft uses an SQL server 2000 instance to keep his data... The problem is that I developed an application in asp.net (with VS2003) that use an SQL server 2005 express database. What I want to know is if I install SQL server 2005 express, is the backup will still work? I mean that the 2005 express wont upgrade sql 2000? What do I have to do to make it works?
I´m currently developing an import/export package in SSIS. I recently realized that this package is supposed to be run on a SQL Server 2000 instance, and I have no clue if this will work... I´m pretty new to SQL Server, but I now that DTS from 2000 ws replaced by SSIS in 2005, and the command-line dtsrun replaced by dtexec.
So my question: Will I be able to run a SSIS package on SQL Server 2000, and how would I do that?
We have just installed a new SQL 2005 Clustered server and loaded integration services. However it appears that integrations services may not work properly in a clustered environment.
So can integration services be setup on a clustered SQL 2005 server?
Ive got some problems that are causing me a major headache. I am running windows XP Pro and i have the SQL Server 2000 Enterprise Edition Evaluation cd but when i auto-run to install it the installation wizard just wont come up. If i try to run it a second time it says another instance of setup is already running, but nothing ever happens. Can anybody tell me why this is?? My work has been stalled for days trying to figure this out. Thanks.
I'm stuck with this and I haven't idea how to solve it. I'm trying to migrate a dts 2000 package from BIDS and I obtain this message:
This wizard will close because it encountered the following error:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib)
I'm going to Migrate DTS 2000 Package select my current sql2k production server (it has almost 600 dts although I think that is not any problem at all)
Wizard recognize without problems my server and then I put a folder for save them but on the next step appears the aforementioned message.
I have an active x script written in vb that just counts the records in a table and fails the step if there are not enough records. It is simple and effective. But when I transfered this task to my SSIS package I get a function not found error.
I am not familiar with active x or vb script so I have no idea what is wrong. I can do without while I build the package but I need it in the end to prevent possible problems....
We are converted our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:
the book "microsoft sql server 2005 integration services" by kirk haselden claims you can download examples and source code at www.samspublishing.com
er no
what happens is this
go to the site look for the 'downloads' link see there isn't one search for authors h etc.... lose a few minutes of your life find the book
yay
it forces you to sign up for an account in order to get the samples - outrageous
prepare for endless spam from sams
sign up
click on download samples find the configedit folder load into vs run it it doesn't work!!
thanks
Warning 1 The referenced component 'SourceLibrary' could not be found. Warning 2 The referenced component 'SourceGrid2' could not be found. Error 3 Unable to find source file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigurationsEditorinDebugConfigurationsEditor.exe' for assembly 'ConfigurationsEditor.exe', located in '[TARGETDIR]' K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditSetupConfigEditSetupConfigEdit.vdproj SetupConfigEdit Error 4 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 9 7 ConfigEdit Error 5 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 10 7 ConfigEdit Error 6 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 87 40 ConfigEdit Error 7 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 11 15 ConfigEdit Error 8 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 24 17 ConfigEdit Error 9 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 25 11 ConfigEdit Error 10 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.Designer.cs 318 17 ConfigEdit Error 11 Metadata file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlinDebugConfigEdit.dll' could not be found ConfigurationsEditor Warning 12 Could not resolve this reference. Could not locate the assembly "SourceGrid2". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit Warning 13 Could not resolve this reference. Could not locate the assembly "SourceLibrary". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit
one could argue that ssis should have a config editor supplied with it, instead of having to go into notepad
I have configured Kerberos delegation for several web services. One of the web service calls SSIS packages, but the packages don't run with the expected impersonate user : the package starts with the imporsonate user, but continue with ASPNET user (which is not allowed to execute SSIS and connect to DB).
If the web service is called directly (no delegation), SSIS packages run with the correct user. It looks like than there is an autenthicate issue, but kerberos is configured and web services can run from one to another with the impersonate user. The issue occured only when I call SSIS packages.
Here is a extract of the SSIS log file :
Code Snippet <dtslog> <record> <event>PackageStart</event> <message>Beginning of package execution. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAPascal.Brun</operator> <source>ImportMonthlyCSV</source> <sourceid>{D053CB99-FDE4-492D-83BC-821E1B34704B}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>0</datacode> <databytes>0x</databytes> </record> <record> <event>OnError</event> <message>SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Data Warehouse" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. </message> <computer>WKS-GE-BRAZILIA</computer> <operator>WKS-GE-BRAZILIAASPNET</operator> <source>Import CSV</source> <sourceid>{284D3166-F372-4B03-86C1-75A4D8DC9A5C}</sourceid> <executionid>{EA9C1929-4131-4FDD-A6FC-560E01A65536}</executionid> <starttime>09.08.2007 17:31:02</starttime> <endtime>09.08.2007 17:31:02</endtime> <datacode>-1071611876</datacode> <databytes>0x</databytes> </record> ...
I would like to setup SSIS project so that multiple developers can work on same project. I am having issues with protectionlevel properties while another developer opens the package created by other developer.
Can anyone guide me on setting up project so that multiple developers could open the package and run (not simaltaneously though). Also tips on setting up source safe or team foundation will be appreciated!
I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:
select 'AAA-'||OWNER AS SOURCE, table_name, column_name, SUBSTR(data_type,1,50) DATA_TYPE , SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH from all_tab_cols where owner='XXX' ORDER BY TABLE_NAME, COLUMN_ID
Here ARE the following errorS I get when running from SSIS:
[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".
[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.