I recently installed Integration Services on one of our SQL 2005 servers running SP2. For starters I keep getting a warning message during the system test that indicates that there is a difference between the version that is installed and the version I am trying to install. I am using the same media I used for the initial server installation. I am assuming that this message is showing up due to the fact that the media is pre SP 2 and the server is now running SP2 allong with build/hotfix #3200. None the less the installation seems to have completed without errors. The problem I am having is when I connect to Integration services and try to expand the different directories I get the following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
BUTTONS:
OK
------------------------------
I have tried a number of different fixes that i have found on the web but none have resolved the issue. If there is anyone out there that has experienced this issue and has been able to resolve it your feed back would be greatly appreciated.
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
Hello friends. I managed to design an Integration service package,but the desired level of performance has not been achieved(i.e it is performing slow). So I want to know what are the best practices for optimized solution . In my package I'm exreacting data from XML file and Storing it in SQL server database with some processing dring data flow.
I'm using 1) Two Script Task Control -In these control,I m opening the connection to XML file through VB.net code and iterating each record at a time. 2)Two OLE DB Command -Each fetched record from script task component is processed in OLEDB command through stored procedure and then inseted into database. 3)One for Loop -This loop contains two script Task control and two OLEDB Command control, (mentioned above),for fetching single record and inserting it in database. 4)One derived Column 5)One Multicast 6)One Character Map 7)One OlEDB Source
As with my current performance I'm able to insert one record in every .5 second (Which is much below to acceptable limits) Is control lying disabled on SSIS designer pane also affect the performance of execution.
I am running a vb.net application that is using the Web Service to issue Update Snapshot. There is a Windows account we are using as a Service account that has the proper permissions to Generate Events. The application is running on AutoSys on a 2003 server. I have confirmed that it will run under this users credentials manually by logging into the server as the user and firing the app. It works without a problem. This was using Default Credentials.
The AutoSys job is configured to run under this user's credentials. There are three ways I have executed this application
>>Through AutoSys running as the user with the proper permissions (this is in place of a Local Service account which is default for AutoSys. This windows user has local admin permissions on the AutoSys server as well as Generate Event permission as confirmed before) >>By logging into the server as Administrator and executing the app with "Run As..." set to the Windows ID >>By switching to new.NetworkCredentials and passing the hardcoded username/pass/domain
For all of the above scenarios - it fails
The ONLY time it successfully issues UpdateSnapshot is when I am logged in to the server as the user in question. In ALL other instances detailed above it fails and the following comes out of the event log
"Line 1: Incorrect syntax near 'MyDomainName'.
I have no idea why it is choking on the Domain Name in all three of those scenarios.
Do I need to impersonate that user through code when passing the Web Service credentials??
PLEASE PLEASE PLEASE help - I need to wrap this up. ANY HELP WOULD BE GREATLY APPRECIATED
Hi, I have just install SQL 2005 SP2 and trying to get Window SharePoint Services V3 integrated with SQL 2005 SP2 reporting services. In SharePoint Central Administration, I select the Reporting Services Integration page and have setup the Report Server Web Service URL and Authentication Mode. I then goto Grant database access, specify the SQL server name, get promted for a username and password that has access SQL Reportserver and get the following error "The group name could not be found" Does anyone have any ideas? Thanks
Hello, I have a problem when trying to fully process an SSAS database using Integration Services "Analysis Services Processing Task" task. I have 2 of these tasks which are responsible for processing the Dimensions then the Cubes. When I run the package either via the BIDS environment or on the local server from the Integration Services engine, I will get an error after about 20 minutes stating:
"Error: Memory Error: Allocation failure. Not enough storage is available to process this command""Error: Errors in the metadata manager. An error occurred when loading the <cube name> cube from the file \?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataMyWarehouse<cube file>.xml"
The cube name is not specific, it will fail and any of my cubes could be in the error log
If I fully process the AS database using the AS engine (logon to local AS server, right-click AS database and click Process), I get no errors at all, it processes and completes fine. The processing options are identical when I run in AS or via the SSIS "Analysis Services Processing Task" task.
I've searched quite a lot online but no joy, the information I have gleaned from various sites does not directly link SSIS with SSAS processing problems.
When either the AS processing starts via SSAS or SSIS the memory usage of MSMDSRV.exe increases to around 1.4 / 1.5 GB but never goes to 2GB ever, even when the error appears.
I've done the following with no effect.
" Have run via AS and works fine " No specific cube it fails on " Have created a Dimension only package, same problem " Changed the maxmemorylimit " Changed the connections to localhost " Memory DOES NOT max out on server
Server Specs: Windows Server 2003 Standard + Service Pack 2 4GM ram, 2GB paging file
i am having a problem while executing XML Query. Im using Sql Report Server 2005 and accessing WCF(.SVC) file.
my connection string is : http://localhost:4278/ITV.CSF.FaultHandlingDemo.Host/FetchEntityService.svc?wsdl while trying to execute connection string is not showing any error.
When i am writing query in Query String and when i try to execute it showing "Failed to execute web request for the specified URL.".
my Query string is <Query> <Method Namespace="http://ITV.CSF.FaultHandlingDemo.ServiceContracts/2007/09/" Name="ReadDataFromDataSet"/> <SoapAction>http://ITV.CSF.FaultHandlingDemo.ServiceContracts/2007/09/ReadDataFromDataSet</SoapAction> </Query>
I have a table report which has 10 fields as cols.The last 2 fields come from 2 diffrent subreport, having a single field each. There are also 2 groups in this table. I have to limit the rows by checking each cell in a group is 0. If all the cells have got a 0 then hide the row. I can check for all the cells except the last 2(Subreport).
Since the last 2 cells has only got 2 subreports instead of a textbox( as it is the case for the remaining 8 cells), i don't know how to check for data returning from a subreport. For all the other textboxes i have checked it as ReportItems!textbox1.value=0
I have installed SQL Express 2K5 with Reporting Services on a server (on our private LAN) running Server 2003 with SP1.
I have requested our ISP to forward port 80 from one of our public IP addresses to the private IP address of the server - I am reasonably confident this is setup properly since they have successfully configured other port forwards for me in the past.
With that said, when I try to access the reporting web site from the internet, I receive an IE error message indicating "Internet Explorer cannot display the webpage." I am able to view the reporting website from within the LAN. Windows Firewall is currently disabled.
Am I missing something? Any suggestions/recommendations would be greatly appreciated.
The code is this: Public Sub Main() Dim directory As DirectoryServices.DirectorySearcher Dim result As DirectoryServices.SearchResult Dim email As String email = Dts.Variables("email").Value.ToString Try directory = New _ DirectoryServices.DirectorySearcher("(mail=" & email & ")") result = directory.FindOne Dts.Variables("name").Value = _ result.Properties("name").ToString() Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.Events.FireError(0, _ "Script Task Example", _ ex.Message & ControlChars.CrLf & ex.StackTrace, _ String.Empty, 0) Dts.TaskResult = Dts.Results.Failure End Try Dts.TaskResult = Dts.Results.Success End Sub End Class
My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'
It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.
I have to create tablix report using data from 2 datasets. The data source for one of the dataset, DataSet1 is Microsoft SQL Server and the data source for the other dataset, DataSet2 is Excel.
I have to pic 3 columns from DataSet1 and one column from DataSet2.
I'm working on a web project that will display the reports in an iframe to the user. When I was using windows security It worked perfectly.
Now my boss wants me to use form based authentication instead that can authenticate against our current Active Directory. This is working perfectly and does authenticate correctly. The only problem is that when I load a report in the iframe http://myserver/reportserver/myreport-blah reporting services prompts the user to enter in windows credentials. Is there anyway to pass my form credentials to the report server so that the user doesn't have to log in again.
Both the report server and the web app are on the same server BUT in different virtual directories.
PS: I tried using the reportviewer control but it does not display things correctly. For example, I have use a multi select dropdown on the reports and it messes up the drop down when the user only has 1 selection (hiding the selection). If the user has more than one selection the drop down uses scroll bars. I would use the reportviewer control if it displayed exactly like it does when using alternate URL method.
This is the exception i getThe current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. ---> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.Services.Protocols.SoapException: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. ---> The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database.
Source Error:
Line 3382: [return: System.Xml.Serialization.XmlElementAttribute("SubscriptionID")] Line 3383: public string CreateSubscription(string Report, ExtensionSettings ExtensionSettings, string Description, string EventType, string MatchData, ParameterValue[] Parameters) { Line 3384: object[] results = this.Invoke("CreateSubscription", new object[] { Line 3385: Report, Line 3386: ExtensionSettings,
Is there a way to give customers access to SSIS? They need to be able to create their own SSIS packages. Of course we have more then one customer so it would be nice to have modular security in place where they don't get to see customers abc and customer xyz packages. Only their own.
I have created an integration services project (attached is a screenshot) that workes against the flat file (.DAT extension) and it does some manipluation in the data and then load it into the table. Everything works fine. Now I want to get the name of my flat file source fille (which is a .DAT file) and then insert it in the table. I am running th integration services against different .DAT files (only one file at one time) which are located on different locations.....so what I want is that, whenever I run the package it do the usuall processing and then while loading the data in to the destination table, it also load the name of the file into the destination table (lets called a field "FileName" of nvarchar type in the table "Comphistory")
Problem When you have a SSIS package that contains a connection from a data source, this connection is not updated when the data source changes based on a configuration change.
Situation : A SSIS solution contains 3 configurations : Development, Test, Production. You can create those configurations in configuration manager of the solution.
The SSIS project contains one Data source. It doesn't really matter what type but I take SQL Server. The database server in development is SQL_DEV, in test is SQL_TEST and in production is SQL_PROD. Initially they are for all configurations the same. You can specify those values by changing the active configuration and then editing the Data source.
In the SSIS package (DTSX), you can create a connection manager based on a Data source. If you change the Data source, the connection manager is also changed. If you change the Data source by changing the active configuration, the connection manager is not being updated.
If you think this isn't a big issue think big. We have 4 configuration, 10 shared Data sources and 25 DTSX packages. That would give a maximum of 1000 settings (4 x 10 x 25). Using this method it can be reduced to 40 (4 x 10). Of course this is a theoretical but it is very common to have the destination data source re-used on all packages, which still would be 100 settings (4 x 25)
Steps to reproduce - create a new SSIS project - In the solution explorer, create a new Data source named TestSource. - In the connection managers window of Package.dtsx, create a new connection from a Data source. - Make some changes in to TestSource.ds under the Data Sources. For example change the server or the database. - Verify that those changes are also in the package.
- in the solution explorer, right click the solution and select configuration manager - under active solution configuration, create a new configuration named test. - Set the copy settings from : development - Verify that Create new project configuration is checked. - click OK and close. - Notice that the active configuration is now Test - Make some changes the Testsource.ds like a different server. - Verify that those changes are also in the package. - Make the development configuration as active. - Notice that the Testsource.ds contains now the original settings. - You will notice that the connection manager still contains the "test" settings and not the development settings. - If you create a deployment utility it will still contains the wrong values.
Can someone explain to me why I am getting this kind of error though I am able to integrate all the data succeesfully to the next destination.
Ronald
SSIS package "Prescription.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [521]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 58, column 1. The destination column (PatientId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 12. The destination column (ServiceId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 12. The destination column (ServiceId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Can someone explain to me why I am getting this kind of error though I am able to integrate all the data succeesfully to the next destination.
I am trying to get the Prescription table from Access to SQL SERVER 2005 database
Ronald
SSIS package "Prescription.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [521]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 58, column 1. The destination column (PatientId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 12. The destination column (ServiceId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 12. The destination column (ServiceId) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 7. The destination column (AllergyCode) is defined as NOT NULL.".
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
I'm trying to use the new integration services and have found all kinds of help for it, but where is it? I can't find it. Is it in the SQL Server Management Studio?
I am trying to know how to read many XML files within one data flowtask. I have many many files that I would like to apply to with thesame data flow so I need to know how to read them in and what taskshould I use under SQL 2005 DTS/Integration services.Thanks for your help!JON
I've to do a mining project and I intend to use the SSIS.
I've done a clustering plugin last year on analysis services and I also want to use it.
Let me try to explain the architecture of the process:
1) Receive data (read data from the database - these data are texts, actually)
2) Pre-process the data (transform the texts in a sparse matrix) using a new plugin
3) Call my clustering plugin and assign it to read the table created on the previous step
4) Call my KNN plugin to classify other pre-processed texts using the clusters found on the previous step as classes.
5) Show results.
Alright... It all running as a workflow on integration services
Here are my doubts:
A) How to view and use my plugin made for Analysis Services on Integration Services ? (is it possible or will I have to create another plugins from zero just to run on Integration Services ?)
B) Assuming the previous step is possible, how to modify my plugins to define inputs and outputs to do the correct communications between each plugin ? I think this is the most important question. Is it simple to do ? Is there any documented examples ?
When i try to connect to SQL 2005 integration services from object explorer i get connected (in the sense it shows in object explorer running packages stored packages ..)but when i try to exand any of these objects i get the following error
Failed to retrieve data for this request(Microsoft.sqlserver.smoEnum)
Additional information
The sql server specified in SSIS service configuration is not present or not available.This might occur when there is no default instance of Sql server on the computer. For more information see "config integeration services" in server 2005 books online.
Login time out expired
An error has occured while establishing a connection to the server.When connecting to the Sql server 2005, the failure may be caused by the fact that under default settings Sqlserver does not allow remote connections.
Names Pipes Provider: could not open a connection to Sql Server[2].(MsDtsSrvr)
We have developed a datawarehouse using DTS in 2000. Now we have started using 2005 and as it supports DTS so there no issued for our datawarehouse to run.
But every six months there are new requirements and we achange and add to the existing DTS, but now DTS development is not supported in 2005, can we use itegration services for the same and embedd it with the existing DTS or do I need to redevelop the datawarehouse using SSIS.
We use Windows Authentication to connect to SQL Server, is there any special permissions required to connect to Integration Services in SSMS?
Whenever I try to browse the servers available with Integration Services (from Object Browser), none of the servers gets listed. If I directly give server name and try to connect to Integeration Services I get the following error. But I'm able to connect to the database engine.
TITLE: Connect to Server ------------------------------
I don't need to have SSIS service installed on my sql server to run SSIS packages as jobs. So I've now deployed my packages to our live clustered SQL server, and I even have a package that runs to a point. So basically the package imports some data then reprocesses a dimension on the cube, it imports the data ok, then fails to process the cube with the following message
Description: The task "Process Cube Dimension DimFiscalPeriod" cannot run on this edition of Integration Services. It requires a higher level edition.
Now the SQL server is enterprise edition, so do I really need to install SSIS on this server or not ?
I'm having issues trying to connect to a remote SSIS server. Connection between servers within the DC works. Port TCP 135 and 139 are open. Also tried to connect using IP address but the same error occurs. Do i need to open more ports?
