Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorB
Hi, I am new to the whole concept of ASP.NET and codebehind (i am using c# and i am also new to that) i currently trying to get my codebehind to connect to a SQL Server express 2005 database on page load however i am getting this error thrown back at me when i run the aspx page on my localhost, Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'.Source Error:
Line 18: { Line 19: SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True"); Line 20: conn.Open(); Line 21: Line 22: } Source File: g:Level HRevivalDefault.aspx.cs Line: 20 i believe it has something to do with the conn.Open() funtion or the connection string it's self the acutal full code is here, using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.Sql;public partial class _Default : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True"); conn.Open();
} secondly once i get the connection working i want t be able to check a user email and password against those in a table to allow the log in, through my experience with PHP i would of done this by putting what the user entered into a variable and then checking the variables against an sql query, how do i go about this with aspx and C# my textfield is called uname and my passsword is call pword. Any help of example code would be greatly appreciated.
So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.
This first one returns no results at all when I should get about 7 rows or so.....
Select Student.name, attendance.studentid, attendance.attenddate, attendance.aperiod, attendance.attendcode from Attendance, Student where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1 And aperiod = '4' And AttendCode = 'OSOS' AND student.name = Attendance.studentid
This one is returning all of the names in the database 6 times each.
Select Student.name, attendance.studentid, attendance.attenddate, attendance.aperiod, attendance.attendcode from Attendance, Student where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1 And aperiod = '4' And AttendCode = 'OSOS' order by name
I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!
Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).
I have a database that has a table with a lot of delimited data. I need to extract some of this data, preferably into a sql view, that I can name the fields. The data looks like this:
|7700|6|0|work center|24|8|XLT
What I need is:
field name 1= 7700  field name 2= 6  field name 3= 0 field name 4= work center  field name 5=24, ETC
I'm not sure how to count the delimiter | and get the data between | & |. Or maybe that isn't the way to do this.
Here is a sample of what I did in Crystal Reports but I need a view to make this usable.Â
Split ({usrc1.application_data}, "|") [22] Â This gives me the data between delimiters 22 & 23.
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver. When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below: ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine. Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
Having one invoice table (ord) and one 'person' table (actor) I would like to include the name of the person who is responsible for an invoice: SELECT Ord.OrdNo, Ord.Selbuy, AC.Nm AS 'Responsible', AC.EmpNoFROM OrdLEFT OUTER JOIN Actor AS AC ON Ord.Selbuy = AC.EmpNoWHERE ord.ordno = 23505 This works perfectly fine if Ord.Selbuy has a corresponding value in Actor:|26914 |21|Yvonne| 21| or if there is no corresponding value in Actor:|26914 |21|NULL| NULL| But what if Ord.Selbuy=0? Then I end up with 3285 rows from Actor! This happens because Actor.EmpNo=0 is allowed. Persons which have never been employed or used to be employed gets Actor.EmpNo=0.Can I create a SELECT statement which only returns data from the INVOICE row if Ord.Selbuy=0?|26914 |21|NULL| NULL| p.s. I'm not able to change table structure/behavior of update procedures, because the tables/code belong to a "bought from the shelf"-business system.
I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects? I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse.
I have virtually no ms access skills as am an Oracle dba.
I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.
When I open the table the field reads "Long Binary Data"
I've just started using the SSIS and i would like to know if it's possible to change or update the dictionary of the term extraction tool. That's important to me because i may have to look for words that don't exist in the defaut english dictionary of the tool.
when I extract nouns from a text with the Term Extraction Transformation, the destination indeed keeps the correct nouns but they are in a random order.
Is it possible to keep the order of these nouns and maybe already keep double occurrences?
Is there a way to perform term extraction on each row in a table, and have the term extraction denote the frequency of the term on a per row basis rather than a per table basis?
Otherwise, is there a way I can take extracted terms and apply a sql function that returns the occurance of that term in each row?
This is probably a stupid question, but here goes: I am trying toupdate a table differently based on what is in one of the columnswithin the table. For example, I want to set Fld1 to be "On" if Fld2is "A", and I want to set Fld1 to be "Off" if Fld2 is "B". This iseasy to do in two SQL statements, but I was wondering if there's someway to do it in a single SQL statement. Thus far, I have had no luckin figuring out how to do this. Any ideas would be appreciated!!
I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits ------ | ------- | ------- | ------------- | ----- | ------- Female | English | Elena | Phys 101-b | 1 | 4 Female | English | Elena | Hist 101-c | 1 | 4 Female | English | Elena | Engl 101-a | 1 | 4 Female | English | Elena | Engl 105-b | 1 | 4 Male | History | Rich | Phys 105-a | 1 | 4 Male | History | Rich | Engl 101-c | 1 | 4 Male | History | Rich | Hist 101-b | 1 | 4 Male | History | Jacob | Phys 101-a | 1 | 4 Male | History | Jacob | Hist 101-b | 1 | 4 Male | History | Jacob | Engl 101-c | 1 | 4 Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table: Gender Program Heads Credits ------ ------- ----- ------- Female English 1 16 Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
I think this may be an increadibly simple one, but it's been driving me up the wall. I have a dataset that contains some summary information about a group of companies which i'd like to represent in a report. The dataset looks like this:
I can't figure out how to select a specific field from the dataset (e.g. Company = Company1 and Lead_Type = Internal). This sort of thing must be done all the time, I'm sure I'm missing something fairly obvious. Any posts back gratefully received.
In my product description field, I need to extract the data that looks like 19-3-19 or 0-0-7. Sometimes that pattern is in the data, sometimes not. I only want those that contain the pattern, of course, but I have no other method of determining whether or not the pattern will be in the description. I thought I'd found a method that worked, until I encountered descriptions that had a '-' in it prior to the pattern.
Here's what I have so far, it works for all but the last one.
CREATE TABLE #TMPPROD ( PROD_DESC VARCHAR(60) ) INSERT INTO #TMPPROD SELECT '22-4-12 15%SCU' UNION ALL SELECT '14-4-8 W/F' UNION ALL SELECT 'UFLEXX 17-3-17 2%FE' UNION ALL SELECT 'FERT-PEST 19-19-19'
SELECT PROD_DESC, SUBSTRING(PROD_DESC,CHARINDEX('-', PROD_DESC)-CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END, CHARINDEX('-', SUBSTRING(PROD_DESC, CHARINDEX('-',PROD_DESC)+1,2))+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END+2+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) +1,2))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END) AS 'ANALYSIS' FROM #TMPPROD WHERE PROD_DESC LIKE '%-[0-9]%-[0-9]%'
DROP TABLE #TMPPROD
Any pointers on what to try next, or have I severely over-complicated the process - as I am wont to do.
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p> <p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows: http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records. Suggestions?
I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.
I'm looking for a tool that can extract only the schema from a databasein a form that can be used to generate that schema in another emptydatabase. This is to facilitate our disaster recovery processes wherewe need the objects only, not the data, and need to replicate this toour disaster recovery site over the WAN. There are plenty of toolsthat can handle a single database, but does anyone know of any toolsthat could handle multiple databases where many of the objects (storedprocs and views) are dependent on objects in other of the databases(tables). This is a home-grown ETL suite so making changes to the codeto remove these dependencies would take way too much effort. I amlooking for something that can either extract the schema for all 3databases and handle the object creation ordering to account for thedependencies (a simple method would be to extract by object type acrossall databases, e.g. tables for all dbs before views before procs), or abackup/restore tool that allows you to restore the objects only withoutdata. Worst case we could write something to generate the DDL or useSQL DMO, but ideally we would prefer to purchase a (relativelyinexpensive) tool to do it.Thanks,Simon
I've tried downloading SQL Server Express several times and installing. Every time it fails during extraction. I have a Vista machine with .NET Framework 3.0 already installed. I'm using MS Expression Web, MS Access, and have also downloaded and installed MS Visual Web Developer 2008 Express Edition. Why can't I even get SQL Server Express to extract from the downloaded file?
