Most of the installation went well. I got to the point of attaching the
source database, but had problems attching the warehouse database:
Database 'REAL_Warehouse_Sample_V6' cannot be started in this edition
of SQL Server because it contains a partition function 'pf_Range_Fact'.
Only Enterprise edition of SQL Server supports partitioning.
I am running standard edition at mey development workstation, the only
one we have running. I do not necessarily need to see partitioning
working. But sounds like I will need to install Enterprise edition at
my development computer to go down this path of discovery? Anyone been
able to do otherwise?
Is there any way to get in and look at the stored procedures at least, or schemas?
Even after going back, it was not clear to me from the prerequisit list that Enterprise edition was required for this.
Hello, I am reading Microsoft REAL project ETL part. I find that it uses neither transaction nor checkpoints throughtout the packages. I am wondering if something goes wrong with the process and the process aborts in the middle,how are we going to handle this. Any particular reson not using transaction? Is it best practice without using transaction for performance consideration and log volumn? I am new to SSIS. I would like to follow the best practice. Thanks for advice..
I am trying to load Project Real. I get warnings when opening the RecurringETL package. However I created the two environment variables it's looking for and have confirmed these variables are functional. I am unsure how to troubleshoot this and am afraid to do anything given these warnings. Where is the configurations collection??????:
Warning 1 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Configuration". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 2 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Root_Dir". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 3 Warning loading TestHarness.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. C:Microsoft Project REALETLTestHarness.dtsx 1 1 Thanks
Can someone explain how the PR testharness is supposed to function? When I run it f with and without debugging and by using dtexec other processes can start and stop before the awaiting response from the console date and number of days to process. If you just look at the testharness and the loadgroupfulldaily the increment date and SQL Audits will finish prematurely before the console data is even entered. Is there a property that I am missing?
Hi List Im trying to set up an implementation of Project Real --it works like this- Create two system environment variables called REAL_Root_Dir and REAL_Configuration with the values given below. Click on Start -> Control Panel -> System. Go to the Advanced Panel, click Environment Variables button, then New in the System variables box.
If the Project REAL files were installed at C:Microsoft Project REAL, then the variable values will be:
The package OLEDB connections work like this First read enviroment variable to get location of config file Next read Config File to get connection string for Config Database <?xml version="1.0"?> <DTSConfiguration> <Configuration ConfiguredType="Property" Path="Package.Connections[SQL - Configuration].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=(local);Initial Catalog=DataWarehouseABC;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue> </Configuration> </DTSConfiguration> Next read Config database to get connection strings for Source and Destination databases
Destination database is called "DataWarehouseABC" Source database is called "SnapshotABC"
the Source database OLEDB connection works 100% however the destination OLDB connection we get this error below PS--Both source and destination databases are on the same development machine , however both databases are restored bak files from another production machine
Error 1 Error loading LoadGroup_Daily.dtsx: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'xxxxxx'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "DataWarehouseABC" requested by the login. The login failed.".
Any ideas on how one OLEDB Connection in this package can get this corruption
Executed as user: xxxxxx-sql. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:14:05 p.m. Error: 2008-05-12 16:14:07.84 Code: 0xC00220DE Source: EPT Dimensions - x Description: Error 0x80070003 while loading package file "E:ETLLoadGroup_Dimensions_Daily.dtsx". The system cannot find the path specified. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:14:05 p.m. Finished: 4:14:07 p.m. Elapsed: 2.25 seconds. The package execution failed. The step failed.
Hi Im using project real framework and have BIDS installed on same server as SQL Server 2005, when I run from BIDs all good when I run from SQL Server agent via File system --the first package that master package calls fails I have double checked the environment variables in Project Real and all good because otherwise BIDs would have failed and of course in each package the expression data uses @[User::RootDir] to point to the package file path @[User::RootDir] + \LoadGroup_Dimensions_Daily.dtsx
the issue is the package under SQL Agent is ignoring @[User::RootDir] + \ and finding an old package path somewhere in Bids SLN metadata
Any clues how can refresh the metadata of the package for deployment
dear list can anyone figure out a workaround as to why OLEDB Provider MSDORA cannot store passwords I have all the info stored in a table (Project Real Best Practise) The user id I have stored in string ConfiguredValue gets transfered to OLEDB Provider MSDORA named (SQL_REAL_Source_myoradb) but not the password To workaround this bug only with MSDORA can aynonne sugest a setting I should use in package security ie the default is EncryptSensitiveWithUserKey?
thanks Dave
CREATE TABLE [admin].[Configuration]( [ConfigurationFilter] [nvarchar](255) NOT NULL, [ConfiguredValue] [nvarchar](255) NULL, [PackagePath] [nvarchar](255) NOT NULL, [ConfiguredValueType] [nvarchar](20) NOT NULL ) ON [PRIMARY]
For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.
I have reporting service 2000 . it has started to make queries to database (my datasource), those queries are NOT my report server queries ..i do not know why its querying database for no reason :-(
I'm written a query to pull adjustments made on customer accounts, and I am attempting to pull in the payment associated with the adjustments. I'm using a single account to test my query and somewhere in my final output select statement I'm doing something that is bringing me back more records than I want. The code I have written is:
IF OBJECT_ID('TEMPDB..#TMP1O') IS NOT NULL DROP TABLE #TMP1O
I've got a Derived Columns component as part of a data flow. On this I've set the error output for my columns to Redirect Row in all cases. I've set a data watcher on the error output and then ran the package.
There are several rows which I'm expecting to fail - about 3 of them. These fail but there are also another 697 which seem to have no problem. So I fixed one of the problem columns in the source data and then re-ran the package. I only updated one row in the source so this row no longer appeared in the error output, but neither did several hundred of the other rows.
Is it possible that the error output has been tripped for that one row but for some reason it sends several hundred more rows? The ids on these additional rows follow on from the erroneous row, and when I fix that row the rows following it no longer appear in the error output.
I am trying to bring in the result fo a query to a flat file. The source and destination connections are oK, the mapping is also correct. However I get the following error during execution:
[Flat File Destination [507]] Error: No column was specified to allow the component to advance through the file.
followed by this:
[DTS.Pipeline] Error: component "Flat File Destination" (507) failed the pre-execute phase and returned error code 0xC02020F0.
I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package. Can i fix this?
And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message
'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'
Blindman, you probably know exactly what to do, huh?
Supposing there are three Columns: Countries, Provinces and States.Then when the user enters “USA” for country, it will bring up a list of states, if they enter “Canada” for country, it will bring up a list of provinces, if they enter another country, it will not bring up any list?
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA SET Supervisor = 'A123' WHERE PersonnelNumber = 'B456'"
I have been working on some SSIS packages for a while now and today while i was working i was trying to create a new connection and in the process there was an error and it said the BIDS has to be closed and i closed it but later when i open BIDS and try to open my project(.sln) from the file menu to work on the half done package it pops up an error which shows the path to my project location on the first line and next statement on the pop up error box says:
"Make Sure the application for the project type (.dtproj) is installed."
I tried to check some forums in which some suggested to try installing SP1 which i tried but ..i dont know why but the SP1 fails to install (i dont know if its causing problem becoz i already installed SP2 as i had some other problem before for which the cure was to install SP2).
Did anyone here face such a problem before ?
I'd really appreciate if the experts here can tell a cure for this problem.
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
in order to maintain a deployed project into an Integration Services Catalog I'd like to know if it is possible to import it into a new project inside SSDT.
I want to update value of a custom field for a perticular project in Project Server 2007 using PSI.
I have created 5 enterprise custom fields(A,B,C,D,E) through PWA/Server Settings.
I want to search all Projects on Server. If any project is having value for custom field A then I want to update rest of the custom fields(B,C,D,E) for that perticular project.
I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.
I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.
Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:
Dim oApp As New Application Dim oPkg As New Package oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing) Dim oResults As DTSExecResult oResults = oPkg.Execute
Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.
During the creation of the setup project I get a message that states: The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project. C:windowssystem32msxml6.dll
OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.
How do I find this out?
Has anyone else created a project like this and experenced the same?
I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.
I've got an SSIS solution file with project deployment model in VS 2013 and would like to deploy that to SSISDB on different environments.All these days I followed the regular way to create a project in SSISDB and deploy it to that. Now want to find out if i can automate this process and so got some questions
1. Can we automate the process of creating a project on SSISDB based on our SSIS project name? This will be like when we do a deployment it should check if the project exists or not on SSISDB based on our SSIS project name, if the project exists we just deploy the packages in the project and if the project does not exists in SSISDB it will create that project and deploy the packages.
2. Can we also automate the process of creating environments? In traditional way we manually create the environment variables under environment tab of SSISDB, but can we make that also as part of deployment? Like when we are releasing to Dev server we look if that particular Dev variable exists on that server, if it exists we just update the existing stuff and if it does not exists we just create it.
I have visual studio 2005 and sql server 2005 with integration service installed on my machine. Couple of days ago, I installed visual studio 2008 professional. When I go to create SSIS project I dont see it in visual studio 2008. What do I have to do to make it appear in visual studio 2008 so that I can create SSIS projects.
I fail to use project professional 2003 to access to the project server 2003 using MSDE 2000 in local area network, following message was shown,
Connection failed:
SQLState: '01000' SQL Server Error 1326 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()) Connection failed: SQLState '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
I have seen these pages with similiar cases but can't help.
Do I need to re-create a new package from scratch after doing what I did? It seems I've made what is probably a typical newbie mistake: I used COPY/PASTE to consolidate containers from two packages into a single new project/package.
Now my new package generates multiple duplicate errors (23 of each...) whenever I open it:
Warning loading LoadBillingData.dtsx: Process configuration failed to set the destination at the package path of "PackageData Flow Task to Load Profiles, Versions, and Transactions.EventHandlers[OnError].Variables[System::Propagate].Properties[Namespace]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
Error loading LoadBillingData.dtsx: Changing the name of a variable failed because it is a system variable. System variables are read-only.
What happened:
new guy (goes without saying...) creates:
01. PROJECT A 02. add PACKAGE A to PROJECT A 03. add some containers, figger out what the heck is going on, get containers working 04. create PROJECT B 05. create PACKAGE B 06. add containers, they work fine 07. whoops! Want to consolidate the packages. Unix guy naively expects Bill has made MS Visual Studio like all windows products (of which newbie is aware): 08. create PROJECT C 09. create PACKAGE C 10. <ctrl a> PACKAGE A 11. <crtl c> PACKAGE A 12. <ctrl v> PACKAGE C 13. <ctrl a> PACKAGE B 14. <crtl c> PACKAGE B 15. <ctrl v> PACKAGE C 16. copy Flat File Connection Managers 17. copy Database Connection Manager 18. drag the arrows around, fix the flat file sources, renew the column maps, etc 19. whoops! the connections all look like a registry number! Fix the OLE DB Destinations, Execute SQL Task containers by selecting the DB Connection Manager copied from PROJECT A
20. successfully execute PROJECT C/PACKAGE C 21. save all 22. exit 23. start MS Visual Studio 24. Open Solution 25. ERRORS!!!
Hello, I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view.
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string?
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
I'm wondering how to print a SSRS report without bringing up a report viewer. Is there way to print(PDF format) the SSRS report in the web application( .net ) directly from the "print" button without bringing up a report viewer?
Hi All,I've been struggling with this for hours...Could someone please advise me on how to convert my current File based SQL Server Express website to a Server based SQL Express one.Particularly interested in what I need to do in the SQL Express management tool, changes I need to make the projecvt itself and changes needed to get IIS to understand things have been changed.Thanks,Martin.
Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
IF @Studio IS NOT NULL SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio) IF @Br1 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1) IF @Br2 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2) IF @Br3 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3) IF @Br4 IS NOT NULL SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4) IF @OverBr4 IS NOT NULL SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4) IF @Condo IS NOT NULL SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo) IF @ListingType IS NOT NULL SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType) IF @WindowAir IS NOT NULL SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir) IF @CentralAC IS NOT NULL SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC) IF @BalconyDeckPatio IS NOT NULL SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio) IF @UseOfYard IS NOT NULL SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard) IF @Dishwasher IS NOT NULL SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher) IF @WasherDryer IS NOT NULL SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer) IF @Fireplace IS NOT NULL SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace) IF @EIK IS NOT NULL SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK) IF @HardwoodFloors IS NOT NULL SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors) IF @BroadBandNet IS NOT NULL SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet) IF @TV IS NOT NULL SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV) IF @Thermostat IS NOT NULL SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat) IF @LandlordNotPresent IS NOT NULL SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent) IF @Smoking IS NOT NULL SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking) IF @NoPetsAllowed IS NOT NULL SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed) IF @Cat IS NOT NULL SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat) IF @MoreCats IS NOT NULL SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats) IF @SmallDog IS NOT NULL SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog) IF @LargeDogs IS NOT NULL SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs) IF @Doorperson IS NOT NULL SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson) IF @IngroundPool IS NOT NULL SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool) IF @AboveGroundPool IS NOT NULL SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool) IF @Elevator IS NOT NULL SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator) IF @UseOfGarage IS NOT NULL SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage) IF @LaundryFacilities IS NOT NULL SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities) IF @HealthCenter IS NOT NULL SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter) IF @StorageAreas IS NOT NULL SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas) IF @WheelchairAccess IS NOT NULL SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess) IF @BusinessCenters IS NOT NULL SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax) IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin) IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
Hi. I am trying to setup php4 to talk to MSSQL 7, but am having no luck. Both are on a Win2k server. I get the error below. Please can someone tell me what to do, or even a step by step guide to setting up php to connect to a MSSQL7 database. I originally created the database in access 2000 and now want to use MSSQL to use it. This is the error i get:
Warning: MS SQL message: Could not locate entry in sysdatabases for database 'customers'. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:Websiteewsiteestdb.php on line 13
Warning: MS SQL: Unable to select database: customers in C:Websiteewsiteestdb.php on line 13 Database unavailable
I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.
