I am exporting data from SQLServer 7.0 to a text file using DTS. A subset of the entire table is extracted based upon an indicator flag.
Question: How can I reset the flag while exporting the data? I am trying to avoid doing this in two separate steps because other users might update the data between the two steps. This would cause some rows to be flagged as exported when they, in fact, were not.
Hello,I am trying to update variable 'Type' (which is a pre-defined variablewithout any value in it). Is there a way to combine Step1 to Step3 intoone single Update? I tried the query at the bottom but it didn't work.Thanks a lot!--STEP1:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else 'Others'end--STEP2:update TABLE1set type = casewhen name like '%Brokerage%' then 'Brokerage'else 'Others'endwhere type = 'Others'--STEP3:update TABLE1set type = casewhen name like '%card%' then 'Credit Card'else 'Others'endwhere type = 'Others'--Failed query:Update TABLE1set type = casewhen name like '%Airline%' then 'Airlines'else when name like '%Brokerage%' then 'Brokerage'else when name like '%card%' then 'Credit Card'else 'Others'end
I want to convert .rdlΒ to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.
I have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.
Here's the rub:
If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).
I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.
Any help is greatly appreciated.
I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)
I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.
I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.
I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.
What I have so far is: 1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however. 2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005) 3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)
Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)
The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.
What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.
But I'll take pretty much anything that fixes the specific db connectivity problem I've described.
Hi, I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory. Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Connecting to a networked SQL Server Box from my local machine
Open Query Analyzer from Start menu, logging in using sa account.
from the object browser i select my stored procedure (WEA_InsertClaim) - right click and select Debug.
i am prompted to enter the parameter values, which i do, auto rollback checkbox is checked - click Execute.
T-SQL Debugger opens and runs through the stored procedure.
but only buttons enabled are the "Go", "Toggle Breakpoint", "Clear All Breakpoints"
so i can set breakpoints etc. but when i select Go it will not stop at the breakpoints it just runs through the stored procedure from start to finish. giving the correct return code as its output
is there something i need to enable in order to make it stop at breakpoints??
Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.
Just wondering if there was something else out there....
I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Additional information: An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
I am new to SQL2005 SSIS. I would like to know whether the output of a data flow can be the data source of UPDATE SQL or not. If yes, how?
My situation is I use data flow task to select and transform some data from a Table A. Then, I transfer then output to a resultset destination and store the value in a variable X.
After data flow task finish, I want to use the data to perform update SQL by SQL task in control flow e.g.
update table B set fieldB1 = fieldA1 from X where B.key = X.key
I know I can store the output in data flow in a temp table, run SQL and then drop the temp table. But is it approcach is slow? Using variable/resultset is much faster?
I am having a brain-fart or something. I need help!!!
I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.
Any sample code or blog links would be greatly appreciated!
Dear Experts, please guide me for replcation..... i'm using sql server 2005 server tools developer edition. my OS is professional 2000. and i've sqlserver 2000 client tools also
i've two databases in my machine. publisher is in different instance, and the transactions might be maximum 50 per day. my aim is when ever developer enters the data into the main database, automatically it should be updated on the my two databases also. i mean the server is sysA and the database is srtp. my machine is sys20 and the databases are srtp1, srtp2. how should i make sysA as publisher? i've right click on the server databases, but it is showing newsubscriptions option only.
Vinod Even you learn 1%, Learn it with 100% confidence.
I've had little success gooling/searching for this (so far).
Given a simple spreadsheet:
StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4
I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
StoreNumber StoreName
1 StoreName_1
2 StoreName_2
3 StoreName_3
5 StoreName_5
.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :
StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4
5 StoreName_5
(the UPD and NEW are added to simplify the example).
Now the default action of an excel source into an ole db destination is an insert into the table - so PK constraints causes failures.
Now, given that the table is referred to by other table, and is in a 24x7 website, how do I change the SSIS package such that, on a row-by-row basis, anUpSert (update or insert) is performed?
The only idea I have so far is:
create temp table
insert excel data into temp table
iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP
Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.
I just want to do one thing:
In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.
My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.
How to update the source columns'metadata automatically?
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?
Hi! A question just struck me that I cannot test right now (don΄t have vs2005 on this computer)...Is it possible to add a database-field value to an Xml-Source object attribute?
For instance <Object id=1 name="void" description="" /> is my souce xml... in this case I want to replace the description attribute value (string.Empty) with a value from my db... can this be done?
Intel D975XBX 930 2GB ram HD 300GB - MatroxVD Windows XP Sp2 + updates Visual Studio 2005 Professional edition SQL Sever 2005 Standard Edition
20070203 Install Practice Files at D:Program FilesMicrosoft PressVisual C Sharp Step by Step.
Configuring SQL Sever Express Edition Hostname - xxxxxxxxx..
At sqlcmd s xxxxx...SQLExpress E got - Pipes error.
Open Microsoft SQL Sever 2005 - Configuration Tools SQL Configuration Manager select Protcols for SQLEXPRESS select Named Pipes enable close.
At sqlcmd s xxxxx..SQLExpress E get Sqlcmd: : Unknown Option. Enter -? for help.
Chdir C:Documents and SettingsAll UsersStart MenuProgramsMicrosoft Server 2005 Sqlcmd s xxxxxx..SqlExpress E Hresult 0x2, Lvel 16, State 1 Named Pipes Provider: Couldnot open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
How do I fix this?
Need to be able to to use the Northwind Traders data base
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
We are going insane trying to start Report Manager on a SQL Reporting Services 2000 installation. The programmer/admin who originally set this up for us is gone.
We recently upgraded a database/application server to a new server, causing the data source being used by reports in reporting services to no longer be valid. Unfortunately, we do not have access to the original report project to 're-deploy' with the corrected data source.
We desperately need to get the reports that are installed to retrieve their data from the new database location/machine. We understand this can be done by specifying a new data source in Report Manager. (To clarify, we have NOT moved the report servier installation or database, these remain in place - it's just the deployed reports that no longer point to the correct data source.)
For some reason, our Report Manager will no longer run - when we try to launch it, we get a windows login dialog - nothing will work here. We've tried both local and domain admins and constantly get ACCESS IS DENIED 401.3 error messages that we do not have permission/problems with ACL's.
We've gone so far as to allow EVERYONE read/write access to the ReportManager and ReportServer folders and the RS virtual directories, but nothing seems to help.
Can anyone help with this? Ideas on how to change our data source, or how to get back into Report Manager?
Since we are somewhat technical, but not experts, and don't have much more time to waste, we are willing to pay $500 for this project to someone willing to access the server and fix the problem so that the reports point to the correct database and restore access to Report Manager.
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.
Firstly thanks a lot Phil and Jamie on such a helpful article on "Checking to see if a record exists and if so update else insert"
Here is my question
I have about 10 tables and there respective working tables For examples: A, B, C, D, E.... and WorkA, WorkB, WorkC....
Notes: 1) When I execute a package these work table (Work A, WorkB ...) get populated with certain rows say about 5 2) Its not that all the work table are populated on every execution. 3) Tables A, B, C... have thousands of records in it. 4) Work table is of same structure as there parent table..Like WorkA same structure as A..... 5) The table A and WorkA and as on... are linked with a KeyID
Now I want to build a SSIS package that can 1) Get the the data from these multiple tables(WorkA, WorkB...) 2) Process each row of these tables WorkA, WorkB.. 3) Depending upon the KEYID of WorkA., WorkB.. etc Update a Flag colunm of table A, B...where the KeyID is equal to KeyID of Work Table 4) After updating insert that processed row of Work A, WorkB ...into Table A, B..
I can do this if I have one source table and one destination table. Here i have some say 10 randomly source tables to respective random destination . All I could think of creating 10 different packages as adviced in Jamie's article. But I am sure there might some other alternative.
Can somebody advice me the best practice of doing this. Thanks a lot in advance
I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.Β
Hi, I want to transform an xml flow to an html flow. For this, I create an XmlDataDocument, I add on it, all that I want, and I store it in a file (in a dataflow task). After that, in an xml task, I set the input properties like that: Operation type: xslt SourceType: File connection Source: ras.xml
All works fine. But now, I want to use a variable to store my xml data. So, instead of storing my data in ras.xml, I store it in a variable like that:
Dim v As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90 Me.VariableDispenser.LockOneForWrite("RAS", v) v("RAS").Value = doc.OuterXml v.Unlock()
and in the xml task, I set configuration like that:
But when I execute, I got this following exception:
Error: 0xC002F304 at Format HTML Mail, XML Task: An error occurred with the following error message: "Root element is missing.". Error: 0xC002928F at Format HTML Mail, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Furthermore, when I want to debug it, in replacing the xml task, by a script task, I can see that in my RAS variable, I get the xml data which is well formed.
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins.
