Hi ,
i'm new to this platform and am wondering if there is an easy way to identify ADDs changes and deletes between two tables - or whether i will have to do a ror for row comparison using the business key?
Any suggestions would be greatly appreciated..
thank you George Bassili
i would like to know the best practices to choose the columns which should be used for delta?.If, i consider Customer ID as part of delta.
View 1 Replies View Related
Hi,
I am performing data migration from a legacy sql server (2000) to a new one (SQL 2005). I am using SSIS packages to perform the migration.
The source legacy sql server is a live machine (used by the customer). Therefore, during the data transfer there would certainly be new records inserted/updated/deleted on the source legacy sql server.
Now, after the migration i wish to migrate the delta to the new sql server. how can i do this ?
Does SSIS have any provision to take care of it ?
how can I capture delta in SQL Server 2005 to refresh base tables in a data warehouse?
View 8 Replies View RelatedSuppose a database server and client are separated by a low bandwidthlink such as DSL, and the client repeatedly issues a query for, say, acurrent product list.Suppose the product list is large, but only a handful of entries havetypically changed between queries. It would be nice if only the changesfrom last query to current one could be sent, saving bandwidth.Is there any way to do this?Thanks,--"Always look on the bright side of life."To reply by email, replace no.spam with my last name.
View 3 Replies View RelatedWe are trying to configure Delta View in our senario and are confused at a couple of points:
1. What is the difference between modify and modify_attribute? How does modify_attribute works at attribute level? Is it only for multivalued attributes?
2. Rather than having an extra table for Delta. If we would use the prime table itself with an extra field acting as a flag (0/1) (0=Prime Record, 1=Delta Record) and generate the delta view according to the flag. Would it be fine or we should have a different delta table only?
I'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
Thanks
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,
SELECT
   a.col1,a.col2,b.col3,b.col4,c.col5
FROM
a
left outer join b ON a.col1 = b.col1
left outer join c ON b.col1 = c.col1.
Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination
table.
I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.
I have a table with the next structure:
DECLARE @MaxCountHistogram TABLEÂ
 (
 MaxId  INT IDENTITY PRIMARY KEY NOT NULL,Â
 PublicationId  INT NOT NULL,
 ProviderId    INT NOT NULL,
 DateLog  DATETIME NOT NULL,
 Amount     FLOAT NOT NULL
 )
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider  and getting the diferential between two different months, for example:
Period             Delta Amount           Provider    PublicationId
20150530 Â Â Â Â Â Â Â Â Â 10.2564 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3 432
20150630 Â Â Â Â Â Â Â Â Â Â 3 Â Result of (13.2564- 10.2564 ) Â Â Â Â 3 Â Â Â Â Â Â Â Â Â Â Â 432
Hi all,
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
Thanks in advance.
function
/*Title:
Created By:
Create Date:
Notes:This function is used to concatenate the fields of a table except any identity and hashbyte column passed into the function. It works for temp tables are phyisical tables.
** NOTE: The temp table has be on the same SQL connection to work. If you use this in SSIS you will need to make your connection persistant.
The original concept came from [URL] .... and was modified.
Revisions:
*/
ALTER FUNCTION [dbo].[get_hash_fields] ( @p_table_name VARCHAR(128),
@p_schema_name VARCHAR(20),
@chksum_col_name varchar(255) )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @sqlString as varchar(max)
[Code] ....
running 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.
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
I've been looking into ways to accomplish a fuzzy search and SSIS makes that possible if I want to do a bulk import or something like it. But what it I just want to look stuff up at any given time not haveing to run the package?
Is it possible to expose the fuzzy lookup outside of SSIS to for example t-sql?
Here's an example:
I want to lookup the music artist "Notorious BIG" but in the database it is "Notorious B.I.G." if I use the SSIS fuzzy lookup I basically get what I'm looking for. But how would I call this from a web application? So then I tried Full text search but this doesn't really work out as well.
Will I have to re-write the logic that the fuzzy lookup uses to enable it to work? i.e. using Full Text Indexes and FreeTextTable, ContainsTable, SoundEx and the like to somewhat even come close to what the Fuzzy Lookup has?
Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?
View 1 Replies View RelatedI am new to using FTP and executing .bat files in SSIS.
First, the .ftp file should run. When it runs the FTP process, the user should be prompted for username and password. Then, it should run the .bat file.
What all are the steps I need to do and how to set the properties for FTP and the Execute process task (which holds the .bat file).
Thank you.