How Can I Create A DTS Package To Update A Table With Data From A Notepad File ?
We are creating a process which will create a notepad file with a set of Unique key which we use to update the database. Am trying to create a DTS package to accomplish this...but it seems to be a trivial task. I can use Transform data task to lookup the text file but i need to write an update query which will update some fields of existing records.
Am not sure how can i map fields of Text file to the Database table field.
If any1 can help then it will be great
View Complete Forum Thread with Replies
Related Forum Messages:
How Can I Create A DTS Package To Update A Table With Data From An Excel Spreadsheet?
Every month a client sends a spreadsheet with data which we use to update matching rows in a table in the database. I want to automate this using a DTS package but am having quite a bit of trouble accomplishing what I think should be trivial task. I've been attempting to use a Transform Data Task with a modification lookup but I just keep inserting the rows from the source excel spreadsheet in to the existing destination table without ever modifying the existing data. Any guidance would be greatly appreciated as to a best practice approach.
View Replies !
Create Table From Text File, Extract Data, Create New Table From Extracted Data....
Hello all, Please help.... I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem.... I need to extract data from DataFile table, here's my query: select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%'; Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one. Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)? Any help would be highly appreciated. Thanks in advance.
View Replies !
Create Package For Multiple Table Insert From Flat File Source
Hello Guys, I am working in one company and currently I am assigned to new project for Data Migration from company X to our company Y using SSIS. I am totally new and i just completed 5 tutorial which was gien on MSDN website. Basically client is going to send us first flat file with 1 million records with Header, Detail and Trailer records. I want to create a Package in such a way that it dumps all this first load into 7 to 8 different tables at a time. we also have to include functionlity for validation and error check. On successfull load error file should only return Header and Trailer but no detail records. If there are any errors then error file should contain Header, Detail records which were unable to load plus trailer which we have to sent back to client. When 2nd file comes that time we have to check whether this is new records or change (update) one depending on Flag which tells it. This is basically high level idea of my Package what i need to create. If u guys have any question then let me know. I know you guys are very experienced one. Anyone of you please give me some detail idea on it I would really appricate it. I have very limited time line for it. Thanks Shah
View Replies !
How To Create DTS Package For Table Alteration And Movement Of Data
Peace be on you, In my organization, we have a database which exceed unto 2 GB. The application on which we are working is developing since the time of asp / cb 6 and in short it is the baby of many developers. It is a kind of ERP which has no documentation. Problem 1: ========= Now, to reduce the size of the database we have examined some of the orphan procedures, tables, and columns. Is it possible for me to create a DTS package which create the backup of all the orphan stuff to some other database and delete it from the actual one. Moreover, We still have doubts that some of fields which we think are orphan, might not be. So we need another DTS package which rollback all the changes. (Any Idea for that) Problem 2: ========= We want another DTS package which would move the data from one database to another and same rollback DTS for this one. (Any Idea for that) Problem 3: ========= How can we use DTS programming to do these tasks ? what benefits do I got from DTS Programming over DTS Wizard. Any help would be greatly appreciated.
View Replies !
Create CSV File From A Data Table
Hi, I am working on SQL Server 2005 Express and what i want to do is to created a CSV file from a database Table or a View......I search around and find that I Can use BCP for that purpose..Ne other good idea if you people suggest....as BCP is not working on my PC while i am connected to database server...So plz suggest me the right way to do so... thanx
View Replies !
Error When Importing Text File - Saving In Notepad First Works
I'm trying to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "Column 19" (67) to column "Column 19" (404). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard) The column 19 which reported a problem contains this information: ?searchroot=/_gen_/txt/&template=std.htm&use=prospect&intref=1_26067 However what is mysterious is that if I open the file in notepad or Excel and resave it again the job runs perfectly. This is not a way we could make it work later on since it's an automatic job that will run each night on a new text file. The text file is sent from Norway to Sweden - and I use ANSI latin 1 when importing. The column has datatype DT_STR with a width of 500. I use Locale Swedish and when I save in Notepad it is saved in ANSI, I use Windows XP Swedish version.
View Replies !
Trigger To Insert Data Into A Notepad
Good Day All I want to create a trigger that will send records INSERTED into a notepad. Can you please help with the syntax CREATE TRIGGER Test ON [Table1] FOR INSERT NOT FOR REPLICATION AS /*Instead of Inserting into another table, I WANT TO INSERT DATA INTO A NOTE PAD WITH 3 VARIABLES */
View Replies !
Ini File Update To Use For DTS Package
I have one ini file with following value: I am using this value on DTS package through dynamic property task. My problem is value of [SourceFilepath] \samxc001yiop01sssCoinsTransFiles01 needs to change to \samxc002yiop02 rrCoinsTransFiles02 after running the DTS package. How can I do this? ------------------------------------------------------------------ TransFile1.ini ------------------------------------------------------------------ [SourceFilepath] value=\samxc001yiopsssCoinsTransFiles [Destination Server] value=SQ6IRVN37A [Destination Database] value=P_Nissan -------------------------------------------------------------------- I need to do this because I need to use 2nd value when I ran DTS second time through batch job.Thanks Sanjeev
View Replies !
Create SSIS Package To Script Out DB Objects (1 File Per Object)
We are in the process of trying to automate our production releases (what a concept ;-) The database is SQL server 2005 All objects are being stored in VSS Using Nant and Cruise Control for the actual migrations. I have two directories - Create (for a brandnew database) and Change (db object changes) In my 'Change' script, I do the following - 1 - Take backup of database 2 - Migrate objects from 'change' directory to production 3 - Script out all objects of database and save in the 'Create' directory For the #3, I was hoping I could create an SSIS package that would script out all database objects and save them on the VSS server. I'm new to SSIS and want to verify it's something that can be done before I start down that path. If anyone has any examples or references, it would be much appreciated. Thanks
View Replies !
How Do I Create A Package That Imports An Excel File Where Column Names Are Changing.
Hi, Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS. We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table. Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic. Any help appreciated, TIA, Anthony.
View Replies !
Meta Data Synchronization With Excel File Source Once We Update The File
hi all; 1. Excel file Source--> monthly Revenue details 2. Derived Colum Transoformations 3. Oledb Destination its my flow in one of my packates (ETL job) Excel file contains monthly revenue details, i wanna import the excel data to my database staging table, so i've created the package. its working fine... Problem if we change the new data for the next month and running the package its not running; the same file, same format, only we delete the contents, of the file except first row of the excel sheet, and pasting the new data; new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us) i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized Do you want to Fix this issue automatically with the available external column's meta data Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to. now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the package Execution wizard, ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the asp.net webpage, that is executing the package in an On_click event) The package is not running from the server, its due to the meta data change in the Excel file( i guess) please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta data should not change when we have new updates in it; otherwise suggest me some solutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kind of Data Profiling activity; i know its some what crazy, but i need to maintain the system with permanent solution, instead of facing this meta data mismatch issue!!! some what lenthy explanation--> its needed for my dear powerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries, i'll try my level best.
View Replies !
Automatcaly Create And/or Update Table Link From Access
I'm aware of how to create a function that automaticaly update my Table links from my Access FrontEnd to tables in a MS-SQL database : Public Sub RefreshLinksSQL() On Error GoTo RefreshLinks_Error Dim X As Integer, tbds As TableDefs Set tbds = CurrentDb.TableDefs For X = 0 To tbds.Count - 1 Select Case tbds(X).Attributes Case dbAttachExclusive, dbAttachSavePWD, dbAttachedTable, dbAttachedODBC tbds(X).RefreshLink End Select Next X Exit Sub RefreshLinks_Error: MsgBox Err.Number & ":" & Err.Description, vbCritical End Sub ----------------------------------- How do I create a link to another MS-SQL database (a subscriber with the same tables). Either by pointing to another database (System DSN) and just let it change the table links to this, or by writing a code with the name of each table I wornt to link. If this is posible, is there then a way where at the same time can point out what is the key in each table (or view)? Romanov
View Replies !
How To Create A SSIS Package For Data Import.
Hi, I want to create a package to import some tables from database X from Server XYZ to database X of server ABC. (As my X database on server XYZ is gets updating everyday so i need to update it on X of server ABC using the package.) So i have created a package using the import export data transformationn services. It runs fine while creating. i.e importing data for the first time. But when i have saved that SSIS package on SQL or File system and scheduled it to run daily, but if fails everytime. I am not getting the error its giving. Because everytime when i go to view history of that package it just gives me messages like step1 started by user xyz and failed. Can you please help me to sort out this problem. If possible give me steps which will help me to create package to run above scenario. you can mail me the solution on abhijeets@nedbank.co.za Thanks in advance. Abhijeet.
View Replies !
Create A Custom Webpage To Edit/update A Table In A SQL Database.
Hi everyone, this is is my first post, so please reply and help. I'm working on a project right now that uses asp 2.0 and SQL server 2005 express edition. This is a general idea of the project. In our company some of us receive ECO notifications (engineering change orders) for our products and we need to implement these to the test scripts that are on the production floor. So the project is about entering the new ECO into a database which will send an automatic notification to our test team. When they receive the notification they will have to sign in to the website and introduce their login and password to sign off the ECO (Following some checkpoints already defined by me, for example, Area ready, Test script modification necessary, new firmware introduction, comments, etc...) but I also need to record WHO and WHEN sign that ECO. We have 3 different test areas in our factory: Electrical, Functional and Systems, so all THREE areas must be signed off in order to the ECO go to a IMPLEMENTED state (at this point i need to send a new email saying that the eco has been implemented in all three areas). So far I've completed the following things: -users validation (logins, areas) -New custom entry form for the ECOs and automatic email notification (part of what I did is described below). Dim ECODataSource As New SqlDataSource()ECODataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ECO_ICSConnectionString1").ToString() ECODataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure ECODataSource.InsertCommand = "EcoNew" ECODataSource.InsertParameters.Add("EcoNumber", EcoNumberTextBox.Text) ECODataSource.InsertParameters.Add("EcoDescription", EcoDescriptionTextBox.Text) ECODataSource.InsertParameters.Add("EcoMandatory", EcoMandatoryDropDownList.Text) -Depending on which test area is the the engineering from, I can filter the ECOs and just shows the ones that their test area is pending. (using GridView) But I'm stuck right now when the engineers have to sign the ECO for their test areas. I was able to use the Gridview and DetailsView to EDIT most of the things that I need. But there are somethings that I don't like: 1. When using the EDIT option on Gridview or Detailsview, all fields can be edited including ECO number, description and mandatory, which I don't want them to change. If I set those columns to read only, when editing that row again. It gives me an error that says that the ECOnumber can't be NULL, but if I remove these 3 columns the Engineer will not know which ECO they have sign. They are only going to be able to see the EcoId, which doesn't say much. 2. Also I saw that I wasn't able to do is to enter the USER login and CURRENT system date and time automatically. I don't want them to manually enter the date and their login manually. 3. Finally, when the last area signs the ECO, I want to update that record and set a flag that tells me that the ECO has been completed. So what I really want is to create some sort of form (textboxes, labels, checkboxes, etc.) that will UPDATE the selected ECO from the gridview for instance. So when I select the row from the GridView, It will show the data (Econumber, description and mandatory as READ ONLY) and use the rest of the things as INPUT for the engineer to complete. At the end an "update button" and when I click it, It will enter/update the data on that specific row, but including the time and user login as well. Also to check if the other 2 areas have signed and if so, change the ECOReadiness flag to 1 and send the email. Is there a code like the one I used above to do this ? Or if you think there a better way to do this, I'll be very glad to hear it. I'm new using sql and asp, so If i'm asking some dumb questions please forgive me. . Here's my table definition for your reference: EcoId - primary key. EcoNumber EcoDescription EcoMandatory EcoReadiness <- Flag for the entire ECO, when ALL 3 areas have signed, this will be 1. ATE < - Flag for Electrical area. ATEscripts < - Just a Yes/no input. ATEengineer <- user login ATEdatetimestamp <- Date.Now() FAT < - Flag for functional. FATscripts FATengineer FATdatetimestamp SYSTEMS < - Flag for systems. SYSTEMSscripts SYSTEMSengineer SYSTEMSdatetimestamp THANKS IN ADVANCE, Regards, Jesus
View Replies !
Create New Table With Csv File
this is the worst noob question ever but Google isn't helping and I searched all the threads in this forum...no help. I have a comma delimited csv file that i need to load into an existing database on MS SQL 2000. I need to know how to create the table and populate it with my data...Any pointers of the differences between syntax Of MSSQL and MySql or a tutorial of how to do it with enterprise manager would be great...Dang I feel noobie.
View Replies !
Create A Table And Put It In A File
When I create a table I can create it in a filegroup, but how do I put it in a file. Example--- I have two files in one filegroup called "Secondary" file A and file B. I want to create a new table called tableA and put it in file B on filegroup Secondary. I looked at bookes online and can't find it. Thanks Thomas
View Replies !
SSMS Express: Create TABLE && INSERT Data Into Table - Error Msgs 102 && 156
Hi all, I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise. Thanks in advance, Scott Chang ///////////--SQLQueryCroomLabData.sql--/////////////////////////// USE MyDatabase GO CREATE TABLE dbo.LabResults (SampleID int PRIMARY KEY NOT NULL, SampleName varchar(25) NOT NULL, AnalyteName varchar(25) NOT NULL, Concentration decimal(6.2) NULL) GO --Inserting data into a table INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration) VALUES (1, 'MW2', 'Acetone', 1.00) VALUES (2, 'MW2', 'Dichloroethene', 1.00) VALUES (3, 'MW2', 'Trichloroethene', 20.00) VALUES (4, 'MW2', 'Chloroform', 1.00) VALUES (5, 'MW2', 'Methylene Chloride', 1.00) VALUES (6, 'MW6S', 'Acetone', 1.00) VALUES (7, 'MW6S', 'Dichloroethene', 1.00) VALUES (8, 'MW6S', 'Trichloroethene', 1.00) VALUES (9, 'MW6S', 'Chloroform', 1.00) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00 VALUES (11, 'MW7', 'Acetone', 1.00) VALUES (12, 'MW7', 'Dichloroethene', 1.00) VALUES (13, 'MW7', 'Trichloroethene', 1.00) VALUES (14, 'MW7', 'Chloroform', 1.00) VALUES (15, 'MW7', 'Methylene Chloride', 1.00 VALUES (16, 'TripBlank', 'Acetone', 1.00) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00) VALUES (19, 'TripBlank', 'Chloroform', 0.76) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51) GO //////////Parse/////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'. ////////////////Execute//////////////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'.
View Replies !
If I Had Any Hair Left I'd Pull It Out. How Do I Create A SSIS Package Which Creates A Table That Never Expires?
Hello everyone, I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here: I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows. 1 - Excecute SQL Task - DELETE FROM STOCK 2 - Data Flow Task 3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE 4 - OLE DB Destination (Creates table STOCK) The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed". I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts. If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report. Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words. Thanks in advance, Chris
View Replies !
How To Create Table On Text File Import?
Hey guys, I have a dilemma and hope someone can help. I don't know of any utilities or commands in SQL that do this but I hope someone does. What I need to do is something like a bcp import a text file in. I can do that with DTS as well. But what I wanted to do is create a table on the import. So lets say, I am importing a tab-delimited file with column names as the first row that is called ax.txt. On import, it would create the table ax with the column names in the file and then import the data into that table. I hope I explained it clearly. Please let me know if there is anything I can use to do this without writing lots of code. I have an idea how to do it the long way but hope there is a utility that already does it. Thanks in advance.
View Replies !
Create A Flat File For Each Record In A Table
I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium
View Replies !
Update Sql Table With A Csv File
Hi,I have an online store, as you can imagine my prices can change once a week. I have loads of products in a products table in a sql 2000 db. I have been updating the price of these products manully witch is very painfull. I would like to know how to take a csv file with just a sku number and price column. And only update the products table with the new price. The products table has loads of columns but i only want to update the price. the product table does not use a sku number as key it uses a productId as the key. Obviously the csv file does not have the productId field. But they both have the sku field so i would like to use that as the lookup key.Could someone please give me some sample code on how to do this? Or point me in the right direction? or know of a product that can do this? I am using Windows XP SP2, with SQL 2000 and i have Visual Studio.Net 2005. My development skills are limited but can generally find my way through samples. I do code in C# if that helps. Thank you very muchHave a good day
View Replies !
Update Table From File
Good evening, I would like to update SQL table from flat file using SSIS script task. Ideally, I need to pull all records from the flat file row by row, load record€™s values into some kind of variables and then execute table update statement for each row. I know how to access a table, but how do I pull necessary information from the file?Thank you in advance for your help.
View Replies !
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Hello Friends, I have two tables, And also I have Sample data in them. create table X (y int, m int, v int) insert into X select 2007,1,5 insert into X select 2007,1,3 insert into X select 2007,2,9 insert into X select 2007,2,1 select * from X Create table Y (fy int, fm int, v int) insert into Y select 2007,1,0 insert into Y select 2007,2,0 insert into Y select 2007,3,0 select * from X select * from Y I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m Using temporary table cannot be done. Thanks in Advance, Babz
View Replies !
How To Pick Up Data From SQL Server And Update A Oracle Database Using A SSIS Package
Hi, I have a table in SQL Server 2005 which has [Id] and [Name] as its columns. I also have a Oracle database which has a similar table. What I want to do is as follows: In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection. Can someone guide me as to how I can specify a update statement in the destination dataflow. Thanks.
View Replies !
CREATE TABLE FROM ANOTHER TABLE WITHOUT COPYING DATA
select * into dbo.ashutosh from attribute where 1=2 "USE WHERE 1=2 TO AVOID COPYING OF DATA" //HERE "ASHUTOSH" IS THE NEW TABLE NAME AND "ATTRIBUTE" IS THE TABLE WHOSE REFERENCE IS USED// //the logic is to use where clause with 1=2 which will never be true and hence it will not return any row//
View Replies !
Update Table From Flat File Source
Hi all, I'm trying to work out how to update a table from a flat file source. I could use the simple way of using OLE DB Command with UPDATE <tblName> SET Column1 = ?, Column2 = ?, <etc> WHERE ID = ? then using the mapping to join the parameter fields. However, this (maybe!) a slow process when you have potential 500k+ records in the source file (and approx 150 files to process!) What I would like to do is the following statement but I'm not sure if this is possible? UPDATE <tblName> SET Column1 = sourceColumn1, Column2 = sourceColumn1, <etc> FROM <sourcefile> WHERE ID = sourceID I've also thought about importing the sourcefile into a Recordset Destination and trying to use the variable in the SQLCommand but I'm not sure how to do this... Does anybody have any ideas on how this could work or an alternative solution? Thanks in advance....
View Replies !
Update Or Upsert A Table From A Flat File
Hi All, I have read what people have posted on this but don't seem to find exactly what i want. I tried the methods from this website like many suggest http://www.sqlis.com/default.aspx?311. But all this seems to do insert new records but does not update the existing records. This is what i want to do. I have an online store that uses an SQL DB with a products table. I have an excel/csv spreadsheet with the same columns as in the sql database. It is much quicker for me to update the spreadsheet than logging in to sql to do it. So i would like to update all the product prices on flat file to the sql table. There will be more updating than adding new rows. Please can you help. Thanks
View Replies !
Update Query Containg Static Data And Data From Another Table.
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View Replies !
Create Table From Row Data
Hello, In SQL Server 2000, is it possible to take a table with one field (column), and pivot the table so that the characters in the row data become the field (column) names of another table ( or in a View)? The number of records could vary. If so, how would I do this? Sample table; Create Table dbo.MonthlyData ( Categories varchar(30) NOT NULL ) Sample data; Sales Volume 2005-02 TotRefVol 2005-02 Sales Ratio 2005-02 Sales Volume 2005-03 TotRefVol 2005-03 Sales Ratio 2005-03 Sales Volume 2005-04 TotRefVol 2005-04 Sales Ratio 2005-04
View Replies !
Bad Data In Source File Kills Package?
I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:
View Replies !
Writing A Update To A Table With Data From Another Table.
Here is my current Query. (IT's Wrong!) -------------------------------------------------- UPDATE tblMain set Measure1 = (select measure1 from tblBulkDump where tblMain.reportID = tblBulkDump.reportID), Measure2 = (select measure2 from tblBulkDump where tblMain.reportID = tblBulkDump.reportID), DataLocation = (select DataLocation from tblBulkDump where tblMain.reportID = tblBulkDump.reportID), BudgetSource = (select BudgetSource from tblBulkDump where tblMain.reportID = tblBulkDump.reportID), Comments = (select Comments from tblBulkDump where tblMain.reportID = tblBulkDump.reportID) -------------------------------------------------- I need to write an Update for the given fields from the tblBulkDump Tabble. Thanks in advance, Gene
View Replies !
Update Table With Data Calculated From The Same Table
Hello, I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly. How would someone update a table with data which is calculated from that same table. This is a made up example. Lets say I have a table: CREATE TABLE [dbo].[tst_source]( [tst_key] [int] IDENTITY(1,1) NOT NULL, [object] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [color] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [color_count] [int] NULL ) I would like to populate the field color_count with the count of items listed with that color. Data: tst_key object color color_count 1, chair blue NULL 2, table black NULL 3 lamp black NULL ---- So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two. tst_key object color color_count 1, chair blue 1 2, table black 2 3 lamp black 2 -- With T-SQL I would use the following code: update tst_source set color_count = res.cnt from tst_source Inner join (select count(tst_key) cnt,color from tst_source group by color) res ON res.color = tst_source.color But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use? Thank you.
View Replies !
|
TRACKER
