In a large organization who would typically be tasked with support ing SSIS - the database team, an application team, etc... SSIS is new to our Organization, we are attempting to put it into the correct organization. We current have SSRS that is supported by an application team, the SS DB is supported by the DB team.
Thanks
hi, all
i am having what is considered to be typical error, i have two tables in sql2000 database
(options) orderid, productid, productname,optionvalue
(orderitems) orderid, quantity, unitprice,productcode,productid
I'd like to join these 2 tables and be able to display in a repeater. the problem is that i keep getting some duplicates with sql statement
"SELECT DISTINCT
options.orderid,options.productname,options.optionvalue,orderitems.quantity,orderitems.productcode
FROM options INNER JOIN on orderitems on
options.[orderid] = orderitems.[orderid] "
PLEEEASE HELP ME SLEEP AT NIGHT:)
thanks
I tried to implement triggers for filling audit-trail table on this way.Everything works fine as long as I don't update the primary key field value.When I try to update PK value, an error occures.The code is the following:CREATE TRIGGER NameOfTheTriggerON dbo.TableName FOR DELETE, INSERT, UPDATEAS BEGINdeclare@type varchar(10) ,@UpdateDate datetime ,@UserName varchar(128)if exists (select * from inserted) and exists (select * from deleted)select @type = 'UPDATE'else if exists (select * from inserted)select @type = 'INSERT'elseselect @type = 'DELETE'select @UpdateDate = getdate() ,@UserName = system_user/* this code is repeting for every field in the table*/if update (TableName) or @type = 'DELETE'insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,UpdateDate, UserName, type)select 'TableName', convert(varchar(20), 'FieldName'),convert(varchar(1000),d.FieldName), convert(varchar(1000),i.FieldName),@UpdateDate, @UserName, @typefrom inserted ifull outer join deleted don i.PrimaryKeyFieldName = d.PrimaryKeyFieldNamewhere (i.FieldName<> d.FieldName or (i.FieldName is null and d.FieldName isnot null) or (i.FieldName is not null and d.FieldName is null))ENDHow to slve the problem with updated (changed) primary key values?What is the typical code for audit-trail triggers?Thanks.
View 2 Replies View RelatedI need to import typically 5000 - 50000 rows of data to OLTP database from a flat file. Flat file contains all active information - new & existing rows. About 10% of the rows are new. Each row has about 30 columns.
I have created a Data Flow which divides the data to new records (for insert) and existing records(for update) via the Lookup transformation (as described in Jaime Thompson blog - thanks).
The question is - in performance perspective - should I check if the existing records have been changed or not?
Currently I do a full refresh of all data - (my opinion is that in SQL side - if you update a row with exact same data - it will not cause SQL server to "perform row update") - however I'm concerned that SSIS performing between 4000 - 40000 update statements each time in OLE DB command may cause issues.
Your thoughts on this appreciated
I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber.
I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication under any circumstances. Someone on another forum told me it was not, so I thought I would ask a separate community to confirm. Regarding the hardware, the latency on ping is less than 1ms between the servers and the servers themselves are decently powered quad processor boxes w/ 4GB of ram each. It seems to have the same latency of 3-5 seconds regardless of whether I publish a single table or all of the tables in the database. I currently have it configured with the publisher on one server and the distributor/subscriber on the second with the log reader and distributor agents set to run continuously. If it is possible, please recommend configuration of the agents or the topology of the PDS to achieve this. Thanks in advance for any insight you provide.
Dan
I'm not sure which forum to post this problem in, since it almost seems like a network issue with our SQL Server. We have SQL Server 2000 SP4 (8.00.2039) installed on our enterprise database server. I've been using ADO (through VB, VBA, VBScript, C++) to connect to it without any problems. INSERT's, UPDATE's, SELECT's, no problems whatsoever... EXCEPT: After performing a number of repetative statements, my ADO connection object will error with the dreaded "SQL Server does not exist or access denied" message.
By repetative statements, I mean something like this:
cn.Open "..."
Set rs = cn.Execute(...)
Do Until rs.EOF
cn.Execute "Insert into ..."
rs.MoveNext
Loop
I may be doing 1,000 inserts within the loop, sometimes more. Without fail, though, my program will error after a certain number. I display a progress indicator showing which record (i.e., "10 of 1,000") I'm currently processing and it seems to error at the same number everytime. For example, it may get to record 564 before displaying the error. If I end the program and run it again, it gets to about the same spot (often it's EXACTLY the same spot) and errors again. So, it's almost like a timeout condition.
I know why the error occurs -- my machine can no longer "see" the SQL server. I have tried pinging the server after I get this message and it's unreachable. If I wait five minutes, though, pinging works and my program continues to run. I have coded around it (if error, wait, and try again) but it's very annoying.
Does this sound like a problem with our SQL Server, Network, ADO, my workstation, or a combination of sorts?
what is re-organization.how do i implement it ,what are the steps which i need to take care if it is a production server
View 4 Replies View RelatedI understand the log files (LDF ) and data files (mdf ) should be on a different drives . I believe it leads to greater availabilty and speed . Are there any other reasons for to keep this on a separate drive.
Also what considerations I should take care while creating a database of around 100 GB . (use of filegroups , growth % etc ). Is there any connection of number of users to number of disks SQL data file to be spread to . Also do I need to take care (through hardware / software for a Quad core CPU ) to take full advanage of Quad core CPU.
Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks
View 2 Replies View Related
I have several sub reports and some graphics I don't necessarily want the users to see or open. Is there a way to hide items from view but still have them available for execution? I tried creating a sub folder and moving the sub reports but the calling reports couldn't find them.
Thanks!
I need to write a sql that generate the hierarchy in an organization.
Below an example
emplid empname supervisor_id superv_name
1 subu null null
2 vid 1 sub
3 ram 4 satis
4 satis 2 vid
I need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.
Whoever joining new to org the hierarchy
empid empname supervisor_id superv_name
3 ram 4 satis
4 satis 2 vid
2 vid 1 subu
4 satis 1 subu
3 ram 1 subu
3 ram 2 vid
5 kumar 1 subu
5 kumar 4 satis
5 kumar 2 vid
1 subu null null
What are some recommended methods for deploying SQL Express throughout an entire organization? It appears that Active Directory/GPO deployment is out of the equation.
Thanks,
Shane
Hi everyone
I am struggling with adding budget numbers to a cube. The main reason being that the budget is *not* on the finest granularity (employee) with regard to the organization hierarchy but on a coarser one (team).
The organization hierarchy is a "flat" (not parent-child) hierarchy that looks about like that:
employee -> team -> teamgroup -> region -> country
As mentioned I now have budget numbers that are defined on the team-level (not on the employee level as "regular" measures). I would know assume that I could put the budget data into its own table and "link" it with the organization through the "team" attribute. I would do that on the "dimesion usage"-tab.
The problem with this approach is that the organization is changing (SCD type 2). This essentially means that by linking to the "team" attribute the aggregation of the budget data on higher levels of the organization hierarchy can be ambiguous (at least that is what I understand).
Example organization table:
Code Snippet
surrKey busKey empName teamId teamGroupId regionId countryId ... scdStuff
1 1 Raphael 1 1 1 1 ...
2 2 Jeanne 2 2 1 1 ...
3 3 George 3 3 1 1 ...
4 2 Jeanne 2 3 1 1 ...
That would mean that on some point in time team 2 (consisting of one employee, Jeanne) moved from teamgroup 2 to teamgroup 3. Just for the sake of a simple example.
Now, what am I to do with my budget data in this situation? I cannot link it to the teamId, because teamId = 2 for example cannot specify if the value should be aggregated into teamgroup 2 or teamgroup3...
I have a feeling that this got something to do with the design of the organization-table but I am unsure about what the actual problem is. Any hint, pointer or solution would be appreciated. If the question is unclear, please let me know and I will try to clarify.
Kind regards
scherand
I am attempting to sum the gas production for each organization number, and separate gas production into each year as such:
Organization_Number20082009201020112012
103 774 7313868470
But currently I am having trouble displaying this, and it's coming out as such:
Organization_NumberMonth20082009201020112012
103 1 774731386847NULL
103 2 810656654674NULL
202 1 27262702293725122048
202 2 2913205120202064NULL
I want to remove the month and have one total per organization number, as well as remove NULL as show as 0.
Code:
select *
from yearproduction
pivot(
sum(Gas_Prod)
for Year in ([2008], [2009], [2010], [2011], [2012]))
as YearlyProduction
order by Organization_Number, Month
I need to write a sql that generate the hierarchy in an organization.Below an example
emplid empname supervisor_id superv_name
1 subu null null
2 vid 1 sub
3 ram 4 satis
4 satis 2 vid
i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hierachy should be follwed
empid empname supervisor_id superv_name
3 ram 4 satis
4 satis 2 vid
2 vid 1 subu
4 satis 1 subu
3 ram 1 subu
3 ram 2 vid
5 kumar 1 subu
5 kumar 4 satis
5 kumar 2 vid
1 subu null null
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies View Relatedrunning ssis package with ssis run time compoenents and sql server 2000...
Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?
Can we just install runtime for ssis and run the packages ?
Please explian with links if possible
thanks a lot
I want to achieve the following in (SSIS/SSDT for SQL 2012) -Â
I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).
I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.
I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?
When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.
I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.
I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages €¦ just not my new one.
Any thoughts why the agent will not run the child newly crated childe package?
Run the SSIS import/export wizard.
(xls -> sql table)
Select the tasks created.
Copy.
Here is the error that occurs.
p.s. Does anyone have any needles I can borrow? I think sticking them in my eyes would be nicer than working with SSIS.
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'Preparation SQL Task' to the clipboard.
(Microsoft.DataTransformationServices.Design)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
Hi. I need to import excel file in database. i first need to do an unpivot task. the column names are dates and SSIS seems to be unable to pick up the column name as it is replaced by F2 F3 F4etc Can you advise of a solution. thanks ken
View 1 Replies View RelatedHi,
I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?
Thanks,
Lawrie
I need to build an asp.net/C# application to read values from an Excel spreadsheet. Once the values are read from the spreadsheet, the C# code will do some elementary statistics on the values read. Then the values read and their computations will be written to a sql server database.
My manager suggested that SSIS might be a good candidate technology for doing this type of work. Does that sound correct? My only hesitation with using SSIS is that I want to keep the application as simple as possible, so that the code can be more portable. Maybe might argument is not a good one, but maybe someone can help me out here.
Ralph
Hi,
I have two variables
1. strStatement datatype (string)
2. objTable object (user tables)
the string strStatment returns a dataset
and is assigned to objTable.
I want write the data from objTable to a sql table
and it seems not to work.
Here is what I did.
I used data flow task to either select from a a string or objectTable.
but it give me an error.
Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!
Regards!!
Thanks.
Hello,
View 13 Replies View RelatedWe have SQL 2008 in development but only SQL 2005 in production. I have an SSIS package that was created in 2008 but need to deploy it to a SQL 2005 server. The '05 server will not import the package because of its version. Is there a way to convert back or 'save as' SSIS '05?
View 6 Replies View RelatedHello All!
I have two questions to ask in this one thread. I would appreciate any feedback.
1. Is it possible to create GUI from SSIS using macro so that it can display forms or dialogs? If so how can I create a form that can be used to pass the parameters for the execution of the SSIS??
2. Is it possible to pass parameter(s) to SSIS? If yes, how can we do it...Please provide me with any example.
I wait to hear from you!
Thanks,
Niben
I would like to see if someone can help me out.
Scenarion:
1.- SSIS Package execute tasks on 2000 SQL Server Database
2.- Execution takes places using Business Intelligence Studio
Question:
1.- How can I tracked that SQl 2000 tasks took place using a SSIS Package?
Thanks
Hi Gurus,
I am new to SSIS. I am trying to install just the SSIS in one machine("SSIS Machine") and just the DB Engine ("SQL Server Machine") in another machine. What I am trying to do is, separating the SSIS service and packages from the Database Engine and trying to run in in another machine. I have few questions on this topic. I searched on this forum but I couldn't find a concrete answer to those questions. Forgive me if it already answered/asked multiple times.
1. When I install SSIS in "SSIS Machine", do I need to install client components also in the same machine?
2. I already established this setup (SSIS with client components in one machine and SQL Server in another) but when I tried to connect to the SSIS thro' Management Studio from Sql Server machine, I keep getting "Access Denied" error. Is it possible to connect to SSIS server from another machine (using Management studio)? I tried the DCOM security permission options I found in the internet(I don't have domain id so I gave "Everyone" full access) but still I get the same error. Any help would be appriciated.
3. Do I need 2 SQL Server Licenses (Enterprise) if I go with this environment?
4. Is it possible to configure SQL Job to run SSIS installed in another machine?
I would appriciate any help on this issue.
Thanks
Hello everyone!!
I am completely new to SSIS and have been given a large project (of course with a tight deadline) that has the absolute requirement of using SSIS. I am/was very, very good with DTS and could easily accomplish what I need to do with an ActiveX script task in DTS in no time, but as this is new development, we are not to use ActiveX script tasks within SSIS since it will not be supported in the next SQL Server release. I'm thinking script task, but please give some comments on how you would accomplish the following in SSIS (please remember I'm new to SSIS, so don't assume I know anything. )
I must accomplish this: in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and we have X amount of regions. (Pseudo code followed by standard explanation)
Select a max value from region lookup table in SQL (this is the # of regions)
for N=1 to MyMaxValue
select states from region lookup table where region code = N (the current region we are on)
'this returns a list of states in a region, need these in array or recordset object or something
Open an output file which will be a tab delimited text file we will write results below in loop to (in DTS I would programatically kick off a transformation task in the package)
'loop thru states returned, so if in a rs object...
do while not rs.eof
execute customer stored procedure, passing as a variable the current state we are on
'this will return all customers within a state, this whole result set (approx 1 million) needs to go to the tab delimited file
'I have to execute this stored procedure for each state & then write results to the SAME file, until we are onto a different region
rs.movenext
close file
loop
next
OK, so basically, as you can see, Its sort of simple in a way what I need to do, i just have no idea how to go about doing this in SSIS. I can not hard code any state or region values. I MUST read them in from the lookup tables as region codes are constanatly changing and we are constantly adding in new states and new regions, so with above coding idea, it would always dynamically pick up any new states, new regions or changes.
So in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and there are X amount of regions. Pretty strait forward, huh? The requirements are strait forward, but SSIS is throwing me for a loop... it does not seem flexible enough to be as dynamic as I need it to be to do this. I'm sure it is, just my understanding of it is very basic so far.
Please provide your suggestions! I think a lot of newbies would benefit from some SSIS design info... how to do common things in SSIS, but beyond just retrieving a recordset and writing it to a file... what do you do when you need to add just a few layers of decision processing, and retriving recordsets and writing files based on that decision processing?????
THANK YOU!!!!!!
Kathy
I am trying to load a dtsx file in the SSIS package ActiveXscript.
But i am getting the following error
Error retreiving the filename for a component failed
In DTS the syntax was
LoadFromStorageFile(UNCFile,Password,[PackageID],[VersionID],[Name],[pVarPersistStgOfHost])
and for SSIS Sql Server 2005 its :
app.LoadPackage("C:ProjectsTestPackage.dtsx", Nothing)
some how its not working for me.Is it because of me using ActiveX scipt
Please help
Thanks in advance.
Hi,
If I have 2 SSIS packets, can one start the other?
Thank you.