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.
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 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?
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?
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.
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 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 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?
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?
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?
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 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.
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?
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'.
Can I do an insert with one column value being specified and the others being retrieved? Basically, I have to take an input from the user, join that input with the result of a select (based on that input) and post that to a table I don’t suppose we can do something like
Hi i have data on a Server in a different database which i like to access from within my ssis job.
I just need to look up information from one table on this database so i can references it. Is there a way of doing this is SSIS. Rather then me having to load the data from one database to another as the data may change.
i tried having 2 sources of data feed into a look up but that does not work..
On my home page I have several different folders to reports which require different data sources. the problem is that within these folders there are multiple copies of the same datasource. is it possible to store all of the datasources in one folder, one location? it would certainly be easier when changes to usernames and passwords need to be modified!
I searched and read about Data Sources and I'm seeing that there is no advantage in using it, which is what I found from playing around with it.
I expected that you would set a global connection in Data Sources and somehow link this to the things in your Connection Manager, giving you one place to switch from one environment to another. But reading the discussions here and playing around with it, this is not the case.
So, why is it there?
Next question.... another thing I gathered so far is something called "Configurations" that will do what I was describing above. Where do I do this?
I'm trying to combine two reports I've created into a form letter. The goal is to have a letter that indicates the students maximum UBSCT score for Math, Reading and Writing. Also in the letter we want to include the student's total number of credits awarded and overall GPA. I not sure if I need to try and combined the two queries or use two different data sources in Reporting Services. I've tried creating one sql statement but the grouping are giving me fits. I fairly new to Reporting Services.
Report 1:
704 Dixie High 11 311880 Student_1 UBSCT Math 176 704 Dixie High 11 311880 Student_1 UBSCT Reading 182 704 Dixie High 11 311880 Student_1 UBSCT Writing 173 704 Dixie High 11 311881 Student_2 UBSCT Math 168 704 Dixie High 11 311881 Student_2 UBSCT Reading 172 704 Dixie High 11 311881 Student_2 UBSCT Writing 165
SELECT track.schoolc as School_Code, school.schname as School, stustat.graden as Grade, studemo.ident as Ident, RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname) AS [Student], stutscors1.testc as Test_Code, left(zsubtest1.descript,20) as [Subject], max(stutscors1.testscore) as Score
FROM stutscors1 INNER JOIN stutests1 ON stutscors1.ststuniq = stutests1.ststuniq INNER JOIN studemo ON stutests1.suniq = studemo.suniq INNER JOIN stustat ON studemo.stuuniq = stustat.stuuniq INNER JOIN track ON stustat.trkuniq = track.trkuniq INNER JOIN school ON track.schoolc = school.schoolc INNER JOIN testdef1 ON stutests1.testuniq = testdef1.testuniq INNER JOIN zsubtest1 ON stutscors1.subtestc = zsubtest1.subtestc and stutscors1.testc = zsubtest1.testc
WHERE stutscors1.testscore <> ' ' and stutscors1.testscore <> '0' and school.schname = 'Dixie High'and stutscors1.testc = 'UBSCT' and stustat.graden = 11
GROUP BY track.schoolc, school.schname, studemo.ident, RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname), stustat.graden, stutscors1.testc, zsubtest1.descript
704 Dixie High 11 Student_1 311880 23.2500 23.2500 87.50300000 3.763569 704 Dixie High 11 Student_2 311881 20.2500 20.2500 76.84300000 3.794716
select stugrp_active.schoolc as School_Code, school.schname as School, stugrp_active.graden as Grade, rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student, trnscrpt.suniq as Ident, sum(trnscrpt.gpacratt) as CreditAtt, sum(trnscrpt.gradcrawd) as CreditAwd, round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts, (round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3))/(sum(trnscrpt.gradcrawd)) as GPA
from dbo.trnscrpt inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN school ON stugrp_active.schoolc = school.schoolc INNER JOIN gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where school.schname = 'Dixie High' and stugrp_active.graden = 11 and trnscrpt.graden >= 9
group by stugrp_active.schoolc, school.schname, stugrp_active.graden, rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname), trnscrpt.suniq
In brief, here is my quandary… I have a GridView on a page with some radio buttons, a couple of check boxes, some drop downs etc. And depending on what the user selects the Grid View needs to show different columns from the database… The easiest way I have found to do this is to have a separate Data Object for each “view�, i.e. each variation of the checkboxes and radio buttons (which determine which dropdowns to use for the variables) and then bind the GridView to the specific Data Source at runtime…
It seemed like a good idea at the time, but now I have over 7 different data connections cluttering up the design view, and more to come and I feel like I am missing a better way to achieve the same results…
Does anyone have some advice for a major novice?
Here’s the beginning of a large set of nested ifs, if this is the way it’s done that’s fine too, I’ll just keep creating data sources, I just get the feeling there must be an easier way to do this..
Hi,I have a button that that executes insertion of data into a database. For the data it inserts into one column, the data is located in another database table.As well as this being inserted, I would like more data to be inserted in the same column, which comes from textboxes which are located on the same page as the button that executes the insertion..For example: someone types in information into textboxes, then presses the button. The code behind then inserts the data from the textboxes plus the data from the other table (for which the coding is already done).Here is my current code: public bool [snip](int ProductId) {[snip] command.CommandText = "INSERT INTO Messages (sendername,recievername,message,senddate,subject) VALUES (@sendername,@recievername,@message,@date,@subject)"; command.Parameters.AddWithValue("@sendername", System.Web.HttpContext.Current.User.Identity.Name); DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView; string receivername = " " + dv[0]["Usersname"].ToString() + " "; command.Parameters.AddWithValue("@recievername", receivername); DataView dv2 = SqlDataSource3.Select(DataSourceSelectArguments.Empty) as DataView; string message = dv2[0]["paymentinstructions"].ToString(); command.Parameters.AddWithValue("@message", message); command.Parameters.AddWithValue("@subject", TextBox1.Text); command.Parameters.AddWithValue("@date", DateTime.Now); command.ExecuteNonQuery(); con.Close(); command.Dispose(); return true; } So along with "paymentinstructions" being inserted into 'message', I would also like the values from TextBox4, TextBox5 and TextBox6 to be inserted... It would also be good if text could be inserted infront of the values of these textboxes - e.g. Price: "TextBox4's value"Does anyone have any ideas? Reply if it needs further explanation.Thanks,Jon
I'm just wondering if it is bad programming practice to have many data sources on one page. I need a "Matrix" type of page that grabs data from many tables and so far I have 5 data sources. Is this common or is there something I should be doing to make it more efficient?
