Can you have more then one OLD DB desination connected to the slowly changing task for the NEW OUTPUT scenario. I know I can have more then one if they have different outputs...like New Output for one and the other have fixed attrib output or unchanged output.
I have a query that gets data but I want to insert that data in 2 different tables at the same time. I can not re-query to get the data since it may change the data I get the second time I query.
So if I have a query that returns a data set how can I insert into 2 diff tables?
I am working on a typical data conversion project where we are migrating data from an old data model to a new data model, using SSIS. Both the DBs are in SQL.
Now we have a situation where say there are 25 source tables and 20 odd target tables.
For transporting data, we are using OLEDB Source & OLEDB Destination transforms. However, each transform maps to one view or one table. As a result, the Data Flow is really messed up with 45+ transforms in it. Is there an elegant way of doing this ? With say just one datasource or maybe fewer transforms?
I want to run multiple DTS packages which export data into text files. There is only one Data Source ..and multiple destinations. When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?
The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.
My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?
I have made a package to import data from a flat file datasource to our development SQL Server database and am very pleased about how well it works.
Now, it's time to import these data into our pre-production database using the same package. I am still wondering how to parametrize the connection string so that I can switch configuration easily. I specifically want to avoid creating 2 similar packages to do the same job but with different destinations.
I have read about variables and configurations but I am still confused. They say that the package has to be reloaded or something for a change to happen. At best, it would be perfect to have a drop down list somewhere, select the correct database and hit a button to execute the package. I would be very interested in knowing if .NET could be of any use for this kind of job.
I have around 120 tables. I am using script component to pull the values from oracle stored procedures. I do not want to create 120 source & desitinations in my dataflow. Please advice how can this be possible with one script component and one oledb destination.
I noticed I can add mutiple outputs in 1 script component which makes the script component to work like a dataset (container of different recordsets (tables) ), if I am correct. Can this be redirected to an dynamic oledb connection.
During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.
I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.
A long long time ago I asked for the ability to define an order in which you insert data into multiple destinations in a single data-flow. This was to get around the problem of loading tables in the same data-flow that have FK relationships between them.
My chosen alternative at the moment is to load the table with the unique key first and drop the data for the table with the FK into a raw file and load it in a seperate data-flow. Another alternative is to disable FK constraints and reenable after but I've chosen the raw file method.
Now that I'm using SSIS on a real project this is becoming a much bigger problem that I ever envisaged it might be. Its rare that I'm building stuff where I don't have to use raw files for this very reason and this means that I have god knows how many raw files hanging around all over the place.
I suppose the reason for this post is to flag the importance of this requested feature. I really hope that this makes it into Katmai. Or into a service pack would be even better!
Its a bit of a failing at the moment.
Are there any chances that this will make it into Katmai? its one of my biggest bug-bears about SSIS v1.
I wanna know if we can have more than one "OLEDB Destination" within a Data Flow Task, I want to use the same data flow and write to two different tables in a database with some changes. If we cannot do this within the same data flow what is the best way to do this.
Is there a way to programatically set (using expressions, variables) the FastLoadMaxInsertCommitSize property of an OLEDB destination in a data flow for Fast Load Operations. Basically, what I want to do is based on the # of records which are going to be inserted want to set the FastLoadMaxInsertCommit size.
I just installed sql2005 and bi dev studio on a vista box. Then VS 2005 and all svc packs. I create a new SSIS package but don't see any Data Flow Destinations in the toolbox while on the dataflow tab. I choose "show all" and don't see any data flow destinations anywhere. I can use the wizard to create packages, and it adds destinations just fine. Anyone have any idea what may be wrong?
I have a ForEach Loop Container that is running from a Foreach ADO Enumerator with records telling me which companies have records to export. As I loop through I use a data flow task to export the records to Excel, I want to create separate Excel files using some of the parameters from my recordset as parts of the name.
I have DelayValidation=True for my DFT and my Excel Connection Manager, ValidateExternalMetaData=False for my Excel Destination Adapter, and an expression setting the ExcelFilePath and ServerName properties to the dynamic path & file name from variables.
The layout will be the same (i.e. metadata) for each file. The files are just getting broken up by company and service type and I want to use that in naming the files.
I am currently getting the following errors:
[EX_DST New Enrollments File [238]] Error: An OLE DB error has occurred. Error code: 0x80040E37.
[EX_DST New Enrollments File [238]] Error: Opening a rowset for "NewEnrollments$" failed. Check that the object exists in the database.
[DTS.Pipeline] Error: component "EX_DST New Enrollments File" (238) failed the pre-execute phase and returned error code 0xC02020E8.
What do I have to do to create the new Excel File? I thought it would do it when the properties were set. Do I have to create the "table" for the worksheet named "NewEnrollments"? If so, how do I accomplish it.
Debugging stops with Yellow filled box, its got stuck not proceeding further. i removed two other destinations. its working. whats this issue? any solution for this?
For some reason I am having a really hard time grasping IS and I have a task that I would imagine is easy.
I have a flat file source with 6 columns, I would like to import this file into two flat files. One file containing columns 1,2,3,5 and the second containing 2,4,5,6. I created the connection managers for both destination files, but I can€™t determine what transformation tool I need to accomplish this task? Could you help?
What I am trying to do is move data from a staging table into a live environment and then update the staging table AFTER the row has move (and not errored). There does not seem to be a reliable method for doing this.
I have a dynamic flat file I need to import to a table (in the same format as the file). The problem, I'm realizing, is that dynamic column mappings are a pain with SSIS. I have to know the format of the flat file ahead of time, which I won't.
What are my options here? Can package configurations help with this?
i want to load these three files three different destinations customer file should go one destination table, employee file should go one destination table, student file should go one destination table tomorrow if i get some more files in same folder , those files also should go separate destinations these should happen dynamically.
When I set up a Flat File, Excel, or XML source, I have to specify the complete file name, in particular the folder where the file exists. I would like to specify the location dynamically, via a variable or property -- but how?
It looks like you can only extended connection managers for data flow sources.
Is there anyway to develop a custom connection manager for the SQL destination in the data flow destinations?
I can€™t use hardcoded connection strings. I can€™t use configurations because they are not encrypted. I already have managed code that will give the corrected connection string. I already have a custom connection manager that I use from the data flow sources. I just need one for data flow destinations but I can€™t see a way to extend into the OLE DB?
Until there's an Integration Services 2.0, what custom components would you most like to see examples of? The documentation team is starting work on the 2nd Web refresh of Books Online and SQL Server samples, anticipated for release around April, and may be able to incorporate some requests as samples or BOL topics.
During the execution of SSIS Package which is populating huge data into OLEDB Destination from OLEDB Source, then some of the records are getting rejected. Again if we are executing it twice or thrice, the rejected records are getting inserted.
Wants to know, why the records are getting rejected? Target table does not contain any constraints.
After performing a join operation on two tables i get the below resultset
pid, fname, typename, pname, pcost
1, cad, bars, product-1, 100
2, har, witte, product-2, 120
3, nes, bars, product-3, 119
Now i need to create files with the obtained resultset like
Column 'fname' is the folder name and 'typename' should be the file in the particular folder.
For example the first record should be inserted into file name 'bars.txt' in the folder 'cad' and third record should be created in file name 'bars.txt' in the folder 'nes'.
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail @subject = 'Job Summary', @profile_name = 'SQL SMTP',    [code]....
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
