I am fairly new to SSIS and I have been tasked with a project that will be a SSIS package that is scheduled to run each sunday nite to update stats on a website. What I want to happen is for our client to drop an access DB on our FTP site in a particluar folder, and my package will grab the file, download it, then rename it and put it into an archive folder on the FTP site. So my question is when i am connected to the FTP site can I perform file operations tasks on the remove server to rename my file and copy it to another folder after I download it?
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 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?
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.
A Southwest Booksellers has stores in three cities: Albuquerque {Winrock, OldTown, NobHill} Santa Fe {VistaView, MesaVerde} Taos {Zia, Zuni} and sells nine categories of books: {Computers,Science,ArtHealth,SelfHelp,Business,Fiction,History,Religion}.
The bookstores will keep a database of transaction records for one year. A sales transaction record contains fields for city id, store id, book category id, date, and quantity of category sold. They would like to have a database of aggregate historical data from which to generate the following reports:
1) Show city sales by quarter for a given year. 2) Show store sales by quarter for a given year. 3) Show Book Category sales by quarter for a given year. 4) Show city sales by year. 5) Show store sales by year. 6) Show Book Category sales by year. 7) Show sales by Book Category and quarter for a given store and year.
Your solution should simulate annual processing and contain the following elements:
1) Script to generate transaction records. 2) Star Schema database of aggregate historical data. 3) ETL algorithm. 4) Reports implemented as Stored Procedures.
Demonstrate your solution using the following data:
Year Transactions ---- ------------ -------------------------------- 2000 1000 2001 1500 TheCaverns store in Carlsbad 2002 2100 2003 2800 Philosophy category 2004 3600 CasaDelRio store in SantaFe 2005 4500 Travel category
A transaction is one book category of quantity between 1 and 5.
please see what i did create table city ( idc tinyint, city varchar(20) );
insert into city values(1,'Albuquerque'); insert into city values(2,'Santa Fe'); insert into city values(3,'Taos');
create table Alb
( idal tinyint, store varchar(20) );
insert into Alb values(1,'Winrock'); insert into Alb values(2,'Oldtown'); insert into Alb values(3,'NobHill');
create table Santa
( idsan tinyint, store varchar(20) );
insert into Santa values(1,'VistaView'); insert into Santa values(2,'MesaVerde');
create table Taos ( idta tinyint, store varchar(20) );
insert into taos values(1,'Zia'); insert into taos values(2,'Zuni');
create table books
( bid tinyint, type varchar(10) );
insert into books values(1,'Computers'); insert into books values(2,'Science'); insert into books values(3,'Arthealth'); insert into books values(4,'Selfhelp'); insert into books values(5,'Business'); insert into books values(6,'Fiction'); insert into books values(7,'History'); insert into books values(8,'Religion');
create table y2000 ( qtr tinyint,
);
insert into y2000 values(1); insert into y2000 values(2); insert into y2000 values(3); insert into y2000 values(4);
while @n <= 1000 BEGIN set @idal = convert(tinyint, rand()*100) % 5 + 1 set @idta = convert(tinyint, rand()*100) % 5 + 1 set @idsan = convert(tinyint, rand()*100) % 5 + 1 set @bid = convert(tinyint, rand()*100) % 5 + 1 set @qty = convert(tinyint, rand()*100) % 5 + 1 set @qtr = convert(tinyint, rand()*100) % 5 + 1
insert ST values( @idal , @idsan , @idta , @bid , @qtr , @qty ) set @n = @n + 1 END
insert salescube (idal,idsan,idta,qtr,qty) select idal , idsan, idta,qtr,sum(qty)from st group by idal,idsan,idta,qtr with cube
this is not working properly. i really do not know how to aproach quarters in the year. possibly my tables are wrong too. how to populate transaction tables? if anyone has any advices i will be happy to apply it. thanks
I am on my first Project .can you please help me with this query as I am not able to insert values.I have a dead line tomorrow and I am nervous.the Script and the Error is below
Requirement: Pass in iftno,TherapeuticAreaid(TAID) to update backend with selected TherapeuticArea(TA)
INSERT INTO TherapeuticArea(TherapeuticAreaid,TherapeuticAreadescription) values(3,'cancer')
Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'TherapeuticArea' when IDENTITY_INSERT is set to OFF.
SCRIPT AND SP
/****** Object: Table [dbo].[TherapeuticArea] Script Date: 03/15/2008 21:27:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TherapeuticArea]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TherapeuticArea]( [TherapeuticAreaID] [int] IDENTITY(1,1) NOT NULL, [TherapeuticAreaDescription] [nvarchar](50) NOT NULL, [IndicationID] [int] NOT NULL, CONSTRAINT [PK_TherapeuticArea] PRIMARY KEY CLUSTERED ( [TherapeuticAreaID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO
ABLE 2 /****** Object: Table [dbo].[iftTherapeuticArea] Script Date: 03/15/2008 21:27:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[iftTherapeuticArea]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[iftTherapeuticArea]( [IFTID] [int] NOT NULL, [TherapeuticAreaID] [int] NOT NULL, [IsPrimary] [bit] NOT NULL ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'iftTherapeuticArea', N'COLUMN',N'IsPrimary')) EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Indicates whether this is the Primary TherapeuticArea for the IFT, or is a secondary (Other) TherapeuticArea. A value of True (1) will indicate that this is the Primary TherapeuticArea, while a value of False (0) indicates that this is a secondary (Other) TherapeuticArea. There must be exactly one Primary TherapeuticArea per IFT, though there can be zero or more Secondary TherapeuticArea.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'iftTherapeuticArea', @level2type=N'COLUMN',@level2name=N'IsPrimary' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'iftTherapeuticArea', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains all of the TherapeuticAreas associated with a given IFT (IFTBase).' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'iftTherapeuticArea'
STOREDpROCEDURE
USE [IFT_test] GO /****** Object: StoredProcedure [dbo].[Upd_TherapeuticArea] Script Date: 03/18/2008 14:00:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Manjula Tadigiri
-- Create date: 03/16/2008 -- Description:Pass in iftno,TAID to update backend with selected TA -- Tables Used: TherapeuticArea,IftTherapeuticArea -- ============================================= CREATE PROCEDURE [dbo].[Upd_TherapeuticArea]
@IFTno bigint = 0, @TherapeuticAreaId int = 0, @TherapeuticAreaDescription nvarchar(50)='0' AS DECLARE @err int,@tablename sysname,@errormessage nvarchar(2000),@Desc as char(85)
if exists (select 1 from TherapeuticArea as TA Inner Join iftTherapeuticArea As IftTA On TA.TherapeuticAreaId=IftTa.TherapeuticAreaId Where IftTA.IFTID=@iftno and TA.TherapeuticAreaId=@TherapeuticAreaId)
BEGIN BEGIN TRY begin transaction select @errormessage = 'update TherapeuticAreadescription Failed',@tablename = 'TherapeuticArea' UPDATE TherapeuticArea SET TherapeuticAreadescription =@TherapeuticAreaDescription FROM TherapeuticArea TA, iftTherapeuticArea ifta WHERE TA.TherapeuticAreaId=ifta.TherapeuticAreaID AND Ifta.IFTID=@iftno And TA.TherapeuticAreaId=@TherapeuticAreaId
if @@ROWCOUNT=0 BEGIN insert dbo.Application_Error_Log(UserName,tablename,errorNumber,
RAISERROR (@errormessage,16,1) RETURN @@ERROR END CATCH END if not exists (select 1 from TherapeuticArea as TA Inner Join iftTherapeuticArea As IftTA On TA.TherapeuticAreaId=IftTA.TherapeuticAreaId Where IftTA.IFTID=@iftno and TA.TherapeuticAreaId=@TherapeuticAreaId)
BEGIN BEGIN TRY begin transaction INSERT INTO TherapeuticArea(TherapeuticAreaid,TherapeuticAreadescription) VALUES(@TherapeuticAreaid,@TherapeuticAreadescription)
if @@RowCount = 0 BEGIN insert dbo.Application_Error_Log(UserName,tablename,errorNumber, errorSeverity,errorState,errorMessage) values (suser_sname(),'TherapeuticArea',ERROR_NUMBER(), ERROR_SEVERITY(),ERROR_STATE(),@errormessage) Return @@ERROR END
commit transaction END TRY
BEGIN CATCH ROLLBACK TRANSACTION -- Log the error select @errormessage = 'update TherapeuticAreaDescription Failed',@tablename = 'TherapeuticArea' insert dbo.Application_Error_Log (UserName,tablename,errorNumber,errorSeverity,errorState,errorMessage) values (suser_sname (),@tablename ,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_MESSAGE()) RAISERROR (@errormessage,16,1) RETURN @@ERROR END CATCH END
I get this error when I try to add a newlank mdf to my project in Visual Web Developer:
Connections to SQL Server files (*.mdf) requires SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251
I've googled the *** out of the subject and I've found multiple forums with different answers, most of which say to open the connect to server dialogue and in the blank field type: "SQLEXPRESS" or "SQLSERVEREXPRESS". Niether of these work, however all of these forums are for VWD2005, I'm using VWD2008 and SQL Server Express 2005. Anyone have any ideas? I'm running both on my Vista Laptop.
I'm using C#.Net 1.1 and i have a sample database inside my project (the actual database is in a folder in the project) and I simply want to connect tot the database inside the project and select and process data. i'm normally connecting to external databases so i'm kind of at a loss as to how i should connect to the database in the project (it's the Northwind Database and I'm using sql server 200). any advice greatly appreciated.
I'm not looking for details or code, just general direction I should go. Here is my goal: I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file. This txt file will then be needed to be saved and emailed. Also, I would like to be able to schedule this in the Task Scheduler for once a month execution. Is this all just one big crazy idea? Thanks for any help. Andrew
Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it. @UserName nvarchar(128), @Email nvarchar(50), @FirstName nvarchar(25), @LastName nvarchar(50), @Teacher nvarchar(25), @GradYr int DECLARE @UserID uniqueidentifier SELECT @UserID = NULL SELECT @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserNameINSERT INTO [table name](UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr)
I need to import data from a CSV file into a db I'm designing. I figured I'd use DTS (which I understand now uses VS in the form of a 'Business Intelligence' (BI) project). My problem is that my only choices for BI projects are: Analysis Service Project, Import Analysis Services 9.0 Database, Integration Services Project, Report Server Project Wizard, Report Model Project, Report Server Project. No "Data Transformation Project". I have SQL Server 2005 Developer's Edition. Might it be that DTS is not included in that version? If I can't use DTS, what choices do I have?
When I run the Import Data Wizard there is a selection for a MS Project datasource, but when I choose this datasource nothing happens and there is no way to complete the import. I'd really like to be able to schedule import jobs from MS Project, but I can't find any way to do it.
Does anyone have any experience with this?
I have MS Project installed on my client machine, does it need to be installed on the server in order for the proper ODBC configuration to files to be present and work?
Hi I am new to the SQL sever development environment.....I was given a project to work on and the problem looks like this....
I have a student data table and a HR table that loads student data and Employee data into a Master table.First the Student data and HR data are loaded into SISDATA and HRDATA tables respectively.Then any duplicates in the SIS DATA and HRDATA are filtered into a DUPESID table and DUPEEID table respectively.If there are duplicates between hrdata and sis data tables are loaded into the DUPES table.If there was a student who works as a part time employee and is a student then there is a conflict in the master table with the row missing its student id in the master table........pls help me to resolve this.Do i need to look over the DB design........????pls help me.
HelloBased on three tables (Projects, Tasks and UserCalender) I would liketo work out the total amount of available resources (UserCalendertable contains a entry for each user for each day, day being 7.5hours) and total required effort (sum of Tasks.EstimateLikley) splitover 12 months.For example:Jan:Available Resources: (4 Users, 7.5 hours per day, 5 working days perweek, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 AvailableHoursRequired Resources:Project Start Date: 1/1/2007Project End Date: 1/6/2007Total Required effort (Sum of Tasks.Hours for above project): 500Hours Average over 6 months = 83.33 Hours per month, so in Jan I needto deduct 83.33 from 600 = 516.67 Hours.etcHow could I do this, I have tried several ways but finding it hard.Thanks
I have a problem here, i want to update the DraftDB in Project Server from an exteral DB (which is not within the Project Server architecture). How can go about doing this?
-the exteral DB would have similiar Tables with the DraftDB such as having a Table call Sponsor -if i add a new record, edit a record, or delete a record on Table Sponsor on the exteral DB, the update would reflect to the DraftDB in Project Server?
Is there any PSI calls that capable of achieving this? Any ideas? (If you couldn't help, would you have any suggestions of who/where i should turn to?)
I am getting the following problem when I try to build the ReportServer Project:----------------------- Build started: Project: Report Project9, Configuration: Debug ------Error: Value cannot be null.========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==================== Deploy: 0 succeeded, 0 failed, 0 skipped ==========-----------------But in the report preview, report display correctly. Plz. suggest me what should i have to do.
Is there a way to include, edit, and organize the scripts to create functions, stored procedures, and views in an Integration Services project? If not, does anyone have suggestions on how to organize all the code that would surround a SSIS solution?
