I am pulling data from an AS400 DB2 mainframe and copying it into SQL Server 2005. For one table the date is stored in three columns (TDAY), (TMONTH) and (TYEAR). I am first using Data Conversion to convert these columns into string format (as opposed to numeric) and then creating a Flat File.
I then want to take this Flat File and copy the columns into SQL server, however I want to "merge" those three columns, create a date and store that into one column in SQL (TDATE). So is this where I need to use a Derived Column?
Hi All, i am just started to studying some material on SQL 2005. A question came across my mind regarding the SSIS 2005.
Can my .net 1.1 web application trigger the SSIS 2005 package? if yes, how? The SSIS 2005 class library is compatible with .net 1.1? Appreaciate if anyone can help to clarify this. :)
I am new to SSIS. I wanted to do a daily ETL load from sourceA to targetB database
Please let me know the best method to that i should follow to do the below task
1. Get the Yesterday's date or last months's from-date & to-date dynamically. 2. Pass the from-date and to-date to my source SQL [e.g select emp_name, emp_dept from emp where hiredate between @param1 and @param2]. Acually my source sql has subquery and the parameters has to be passed to the subquery's where clause. 3. Insert the resultset into a staging table in the targetB database. 4. Join this targetB table with another table in sourceC db 5. finally load the result set in to the targetT database table 6. How to configure/schedule this package so that it runs daily, automatically pass the yesterday's date to the package.
I have done this using Oracle PL/SQL but my requirement is to do this in SSIS. I am not sure which control flow or transformation items to be used to do this in an effecient way.
I was creating an SSIS package, seemingly simple. One of the tasks I needed to do involved copying our production database to a seperate DB instance for staging. I wanted an SSIS package that would stage production so we could push and test changes.
My first problem is that I tried to create a simple Transfer Database task. When I tried to run the task, I got an error stating that our Stored Procedure, DtsRun, could not be scripted. It's an encrypted stored procedure.
A little annoyed, I decided that I'd do a transfer SQL objects instead. Now I don't want to hard-code my objects to xfer based on the current schema and edit this SSIS script every time we make a DB change; I just want it to copy ALL objects except for my encrypted proc. I'll just build a list with with another task.
I decided that I'll write a script that generates a list of proc names, and I'll pass that to the Copy SQL Server Objects task. Well apparantly I can't assign a collection value with a script. I have to create ANOTHER package that modifies THIS package because (1) The entire package isn't exposed to the script task and (2) object variables can't be used in expressions.
Am I missing something? So because I use a single encrypted stored procedure, I have to write 2 fairly complex packages to copy my database? That's just stupid. I can understand if SQL can't move the encrypted package while the database is online. I could deal with that. But the extremes I have to go to JUST to copy my database are truly rediculous.
I was really excited about SSIS, but I've been really dissapointed. The performance of the designer on my machine is really poor (I have a Core T2700 with 2GB RAM). Just about every error message I've gotten, both design-time and run-time, I've had to google because they're so obscure. And if you can only store blittable types and strings in variables, that severely limits the functionality of a package (without going to programmatic extremes).
Most of all, I'm really dissapointed in the expression system, If the CLR is loaded into the SSQL/SSIS runtime, then why am I coding it using ANOTHER proprietary coding syntax???? I thought .NET was meant to keep people from having to redesign the wheel???
Microsoft is constantly pushing C#, yet I can only script in VB.NET, which means I now have code-bases in different languages.
Overall, I'm really dissapointed with SSIS. The usefulness of the added functionality in SSIS packages is shrowded by the massive list of nuances.
I want to make a very simple package: Export all rows in a table to a flat file. This package I can create pretty much by only using the wizards. Now to my problems:
H is a header post, in this case with date and time following. D is a details post, that is all the rows that was exported. E is and end post, containing only the number of rows in the file, including H and E posts.
2) I need to set the file name dynamically, preferably using date and time to name the file.
I´ve done this very same thing in T-SQL, like so:
Code Snippet USE AVK GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO SELECT * FROM tempProducts GO CREATE VIEW EXPORT_ORDERS AS SELECT 1 AS ROW_ORDER, 'H' + REPLACE(CONVERT(char(8), GETDATE(), 112) + CONVERT(char(8), GETDATE(), 108), ':', '') AS Data_Line UNION ALL SELECT 2 AS ROW_ORDER, 'D' + COALESCE (CONVERT(char(10), LBTyp), '') + COALESCE (CONVERT(char(50), Description), '') + COALESCE (CONVERT(char(5), Volume), '') AS Data_Line FROM dbo.tempProducts UNION ALL SELECT 3 AS ROW_ORDER, 'E' + RIGHT('0000000000' + RTRIM(CONVERT(char(13), COUNT(*) + 2)), 11) AS Data_Line FROM dbo.tempProducts AS tempProducts_1 GO IF @@ROWCOUNT > 0 BEGIN BEGIN TRANSACTION SELECT * FROM tempProducts DECLARE @date char(8) DECLARE @time char(8) DECLARE @sql VARCHAR(150) SELECT @date = CONVERT(char(8), getdate(),112) SELECT @time = CONVERT(char(8), getdate(),108) SELECT @time = REPLACE(@time,':','')
DECLARE @dt char(14) SELECT @dt = @date + '_' + @time SELECT @sql = 'bcp "SELECT Data_Line FROM avk..EXPORT_ORDERS ORDER BY ROW_ORDER" queryout "c:AVK_' + @dt + '.txt" -c -t -U sa -P dalla' EXEC master..xp_cmdshell @sql
--WAITFOR DELAY '0:00:10'; DELETE FROM tempProducts
COMMIT TRANSACTION END DROP VIEW EXPORT_ORDERS GO
But I´m sure it can be done in SSIS aswell, giving me some nice options for i.e. error handling aswell. Pointers please
Why isn't there some documentation on how to do this. This should be really simple and it has taken me 2 weeks and I still haven't gotten an answer. Please Help Does anyone know the answner or some place where there is some documentation!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I get the following error when I try to substitute the strings in the databasedetails collection with variables: Error: Object reference not set to an instance of an object. StackTrace: at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus(Database srcDatabase, DatabaseInfo dbDetail) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSpAttachDetach()
I created the following variables: strDestinationDB = AirCL2Exp_new3 strDestinationDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.mdf strDestinationLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.ldf strSourceDB = AirCL2Exp strSourceDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Data.mdf strSourceLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Log.ldf
I then assigned those variable to DatabaseDetails Collection:
Inaddtion I also assigned the following to the two DatabaseFiles Collection: for 0: DatabaseFileSize = 0 DestinationFilePath = @strDestinationDBPath FileType = DatabaseFile SourceFilePath = @strSourceDBPath SourceSharePath = @strSourceDBPath
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
...when I started this endeavor. I have a previously developed Lotus Notes App. The idea was simple; as I am sefl taught on Lotus Script, I figured I'd be able to stumble my way through VB.Well, it started OK. I used VB Express to get familiar with the stuff, but decided to go with a full version of VS 2005 and try and get this thing properly developed as a web app. I purchased several reference books etc., and have become relatively familiar with the forums here.First issue I have is that I simply want to use code to update or add records to an SQL DB. I know about datagridview etc., but I want to update the DB using forms, not the tabular view those controls provide. I thought it would be relatively straight forward, but found my ignorance runs deeper than I thought. When I tried to do so I am finding I am not really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The issue with searching these forums is most posts deal with datagridview or something similar.I have spent a ton of time trying to find relevant posts or articles, but have had no luck yet.Again, all Ireally need is a nudge in the right direction. I am more than willing to plod through reference materials or articles/posts to find what I need to know, I just can't find where to even start on the info I need. Regards, Joe
Hi I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work. ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
Output: First row: initial values of the fields Second row: average of the same fields
Please help me...
select * from ( select HEM_LOKOSIT, HEM_NNS from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33 union select AVG(HEM_LOKOSIT), AVG(HEM_NNS) from LPMS.HEMOGRAMS where HEM_PATIENT_ID = 33) order by HEM_LOKOSIT desc nulls last;
I'm relatively new to SQL7 but I did use 6.5 a fair bit. I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table 2)Add some data to the test table 3)Back up the transaction log 4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Hi, I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function for this in MS SQL? I need somethgn like a 1,2,3 b 4,5 c 7 d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
DELETE FROM #RptDetails WHERE StructureType <> @StructureType AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to : DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
I have a form that after being filled out has its contents written to adatabase and then goes to confirmation .asp page. I want the confirmationpage to be personalized but I am not sure how to pass the "name" parameterto the confirmation.asp page. I know I am going to kick myself whrn I seethis.Thanks, Houston
I'm trying to find out if a particular column in a table has more than 10 digits. for example: the TN column. How can I write this query? The data type for this column is bigint. Also, would it be better to change this data type to char(10) instead as it's used primarily for phone # lookups? Thanks in advance!
Hi, Im trying to create a simple search page to return a list of products from a table in a sql database. Id like to be able to search a product description column in a table I have called products using the contents of a text box. I was wondering how i can go about getting the search to check for all words the user types in rather than just a single word or an exact phrase. Im currently using the following sql query SELECT [product_title], [product_description] FROM [products] WHERE ([product_description] LIKE '%' + @product_search + '%') this works fine for single words and exact phrases but if i had product called 'fred w bloggs' and i enter 'fred bloggs' it will not return anything. Please could anyone suggest how i shoud go about this? Im not sure if my web hosting company will enable full text search or will this be required? Thanks for any help! pete_ (very new to asp.net!)
hay friends scene is that i wana read single multiple rows of a single column from a sql database and then want to shows those values in text box,,, so plz tell me ho to do it. By using data set ,,data table or what to use for this and how.... wll be waiting for ur coordination
Hi; I know it must be really easy but I couldn't somehow figure it out: 3 columns: pms_ID, pms_UserID, pms_FlagSeen 1 ab1 True 2 ab1 True 3 ab1 True 4 ab1 False 5 ab2 True 6 ab2 False All I want is to count number of Falses and Total (Trues and Falses) belonging to Users (I mean by pms_UserID) Any help would be appreciated.. Regards...
I don't want to use any of the big fancy Data presentation controls like GridView, Datalist, etc. I just want to do a programmatic reading of a SQL DB field and then post its contents to a label's text property. I have programmatic connection and select statements that seem to work okay. The code I have written so far is shown below. I just need to know what code should go in place of the long underlined portion...(oh, and pardon my newbie ignorance)...
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Web" %><%@ Import Namespace="System.Web.Configuration" %><%@ Import Namespace="System.Web.UI" %><%@ Import Namespace="System.Web.UI.WebControls" %> <script runat="server"> Sub Page_Load() If Not IsPostBack Then Dim PreviousContent As ContentPlaceHolder PreviousContent = CType(PreviousPage.Controls(0).FindControl("ContentPlaceHolder_Main"), ContentPlaceHolder) Dim PassedUserName As Label PassedUserName = CType(PreviousContent.FindControl("lblPassedUserName"), Label) lblPassedUserName.Text = PassedUserName.Text Dim ASPNETDB As SqlDataSource = New SqlDataSource() ASPNETDB.ConnectionString = WebConfigurationManager.ConnectionStrings("ASPNETDB").ConnectionString ASPNETDB.SelectCommand = "SELECT [UserName], [LastActivityDate] FROM [vw_aspnet_Users] WHERE ([UserName] = @UserName)" lblUserName.Text = _____________________________________________________ End If End Sub </script>
OMG PLEASE HELP!! I have a trigger that is supposed to run a stored procedure. The stored procedure runs great. but when I put it in the trigger it doesn't run and the table does not insert. Why? Here is the trigger.USE [Ciena_new] GO ALTER TRIGGER [dbo].[tr_Page_INSERT] ON [dbo].[SWIRLChatroom]AFTER INSERT AS
exec sp_send_cdosysmail 'dstock@eskillz.com','dstock@eskillz.com','Test of CDOSYS', 'Test' GO Please help me!! Derek
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>" SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
