I need to write a package the does the following, and not sure the best approach:
The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.
That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.
Also, each time I create the new output file, I need to delete the old named file.
I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.
I have a table and one of the fields is a datetime and named "Finish." What I would like to do is when the value in Finish has expired ( FINISH < NOW ) to call a procedure and change the status to of this record. I was thinking to set a MS SQL timer/delay on each row - but that does not seem pratical or is it?
I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.
I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:
Event A Created Event A Modified Event A Property A Created Event B Created Event C Created Event B Property A Modified Event A Property B Created Event C Voided Event A Property A Closed Event A Closed
In the end I want to create two tables like so:
Table 1: Event A / Closed Event B / Modified
(Event C is not here because it was voided, so any prior records of C are gone).
Table 2: Event A / Property A / Closed Event A / Property B / Created
(Only events with properties would be in this table).
In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.
Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.
"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).
I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).
I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.
So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?
I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :
I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table. Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).
The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes --->Customer on the CustomerID field.
So basically here are the logical steps:
1.- Read Policy records from foxpro table 2.- insert record to SQL Server PolicyAttributes table 3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table. 4.- Get the unique number from the new Customer record ( Primary Key of Customer record) and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ). This way I can keep my PolicyAttributes---->Customer relationship.
Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers. So if there are 4 customers then I will have to insert 4 records in the customer table. CustomerName_1 CustomerName_2 CustomerName_3 CustomerName_4
Any help is greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!.
Hi folks,This is my first post on the SQL forum and maybe it's a dummy question but... i didn't figure it out.Also, i'm not sure if it's the right place for this type of questions, excuse me if i'm on the wrong spot. Here we go: I'd like to have on my web sites some articles and bellow each article a list of "Related Articles".I started with something like this:
After that, i noticed that i have an "One to Many" relationship between the 2 tables (fields ArticleID) but, if i try to create another relationship like ArticleRelated.ArtigleRelatedID -> Articles.ArticleID it will looks like a circle between the 2 tables and i'm not sure if it's the best way (even if it will work). Before somebody ask "Wy do i need this third relationship", besides other good reasons, i'm using "LINQ To SQL" and i just drag and drop the tables to the designer window and it automatically create classes and colections based on the existent relationship.I appreciate any help.
I'm looking to build a menu / sitemap out of my 2005 SQL Server database, an example of my table structure is:
ID PageTitle ParentID ======================================= 1 Home 0 2 About Us 1 3 Contact Us 1 4 About Us / Sub 1 2 5 About Us / Sub 2 2 6 About Us / LVL 3 5
So essentially this menu should display as follows: Home About Us About Us / Sub 1 About Us / Sub 2 About Us / LVL 3 Contact Us
Is it possible to write a query that would output menu in that specific order?
I have tried and tried but I get all types of errors.Someone I hope has done this and perhaps can explain in more detail how to get this done. I have my Store Procedure.... I added output to the data I will be needing. at the bottom I added my code....I need to some how run this procedure...Get all the info in the select statement and display it in a form.I want to use a variable for each field...Ihave no clue why my code does not work... CREATE procedure dbo.App_Login_NET ( @LoginName nvarchar(15), @Password NvarChar(15) ) as select UserName OUTPUT, UserPassword OUTPUT, UserClinic OUTPUT, UserTester OUTPUT from Clinic_users where UserName = @LoginName and UserPassword = @Password GO
When I type in a 'correct Username/Password' it gives me this error Procedure or function Appt_Login_NET has too many arguments specified. I just want to contain the values in separte variables for later use...Also how do you use rowcount????
my Code
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest") Dim cmd As New String("Appt_Login_NET") Dim mycommand As New SqlCommand(cmd, con)
I'm trying to find if there is a combination of dataflow transformations that will produce the following result
SELECT
period,
project,
task,
employee = CASE
when empid in (SELECT DISTINCT empid FROM EmpTable) then empid
else 'Deleted Employee'
end
FROM ProjectTable
I know I can create a dataflow task with this query as a data source and then send it to a destination, but I was wondering if that is the best way to do it or if there was a better way to do this using the data transformations available in SSIS.
I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.
I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.
I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.
