Can SSIS Parse This Text Report Without A Lot Of Programming?
May 18, 2006
I've got some machines that output text files after each shot of parts. I'd like to take the data in those files and parse it and insert it into a SQL Server database for future massaging. The text files look like the example I've posted below. Can SSIS parse out the set points and actual values even though the file isn't CSV or tab delimited and the data is kind of 'strewn' all over the report? Each report does have the exact same format so the report format doesn't change from report to report, just the data. Thanks in advance.
Ernie
WP4.57 C Y C L E P R O T O C O L
Order data 18.05.06 11:27:57
Order number : 2006 Recipe no. : 15761
Machine number : 7 Recipe name : Stabilizer Bar Innsulator
Machine Operator: 1257 Art.descrip.: Stabilizer Bar Grommet
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it. I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns) For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal. <ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
In MS Access 2000 if I have a String such as:Column1Delta CC: 123Charley CC: 234Foxtrot CC: 890and I wanted to extact just the numbers in to a field called CCI could use this formula in a calculated field:CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)resulting in:CC123234890Any idea on what the code should be within a view in SQL Server?also -- what is a good reference that can help with these types ofproblems.Any help appreciated!RBollinger
I am trying to build a query on a SQL2000 text field which contains XMLstring. The query is like "select requestnumber from history whererequestnumber is like '%re1%'". As you can see in the following samplerecords, the xml string has database structure and the requestnumber isa node of the XML. I wonder if it is possible to have SQL server parsethis field and allow me to do the query. If not, any suggestion wouldbe appreciated as to how to store XML data in SQL2000. I am not sureif I misused the SQL2000 XML feature correctly. So far I pass the rawquery result to ADO and manipulate it in XMLDOM.The table is to capture history of changes in any record in mydatabase. So I need to keep it simple so any record from any table canbe stored in here. The structure of the table is like this:sysObjectNumber(int, not null)recordKeyValues(char(30), not null)archiveTime(datetime, not null)history(text, null)A sample record would be like the following:sysObjectNumber recordKeyValues arvhiveTime History=============== =============== =========== =======1728725211 ABC 2005-03-25 8:09:56.700<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:01:14.407<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</usedby></history>1728725211 ABC 2005-03-28 11:46:12.723<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:46:35.273<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</usedby></history>
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData 1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.] 2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.] 3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set) ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8 1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt 2 [abc1.Gray...abc8.] Gray Shoes Striped jacket 3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like: DECLARE @string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]', @searchString1 varchar(20) = 'abc1', @searchString2 varchar(20) = 'abc2';
SELECT newstring FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
I am working on sql server integration services..New to this topic...I want to transfer data from flat file to sql database by doing it programatically.Creating source,destination and transform adapters
my basic question is should the source,transform and destination adapter be bulid for each file i am trying to transfer.
I am trying to bring the data from Oracle to SQL Server. And, I need to write this in C#. All that I have is a source table in Oracle and a source table in SQL Server. I need to have some samples.
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
I need to parse some text files and load them to database - these files are mostly CSV files or fix width columns format and the column names (first line) may vary in text (e.g. different abbreviation), order and extra columns, etc.
Is it a good idea have this done using script task of SSIS? How it compare to Perl on performance? Or any other tools good for this?
I have been creating SSIS packages programmatically and have run into somewhat of a dead end. I have found the examples provided with the SQL 2005 install very helpful, but they only cover setting up three tasks: Sort task, OleDB Source and a Flat File Destination.
Does anyone have any examples or knows of examples of using the Merge Join task and the Conditional Split task?
I'm doing it all programmatically and so far I'm having trouble finding much in the way of documentation or examples.
I have a data field in a SSRS Report that contains the requestor's User Id. Their ID is prefixed with "PRIV"...And I'm assuming that is the direct result of the network domain. I need to create a SSRS expression to determine if the User ID is prefixed with "PRIV" and then parse that out and use what's behind the "" as their true User Id.
example...."PRIVID123456" should appear as "ID123456" in the report data line.
Hello. I am attempting to use SSIS to import a table from MS Access in to SQL Server. However, when i set the destination component properties I get the following error:
Exception from HRESULT: 0xC0204006 The table exists within the destination database and if I comment the line out, it is able to acquire the connection.Anyone have any ideas? Thanks
//Add a dataflow task to the package. MainPipe dataFlowTask = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;
// Iterate through the virtual column collection. foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { string y = vColumn.Name; // Call the SetUsageType method of the design time instance of the component. instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); }
Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute();
After creating this package i found that 'InventTable" is not set to OLE Db Source component. where as i can set name to OLE DB Flat File destination component.
Although I am able to connect to a SQL 2005 server (both through the SQL Management Studio and through the connection wizard in Visual Studio 2005), I am unable to parse a query if I use a connection set up as ADO.NET. When I use OLE-DB, it works properly. When I attempt to execute a SQL task (comprised of a single SELECT statement), I get "Failed to acquire connection 'servernamegoeshere'. Connection may not be configured correctly or you may not have the right permissions on this connection." How can this be if I'm able to parse data from this connection through other means? This happens both locally and remotely.
I have a VB.NET program which creates a Job (Microsoft.SqlServer.Management.Smo.Agent.Job) and schedules it, to load and run a existing SSIS package, which is on a different server.
I'm using the following assemblies for this purpose.
To make this application run in my application server, do i have to install any SQL server components or SSIS rumtime components? Or Is it enough if i copy only the above mentioned dlls to my applications bin directory, in my application?
Note: I have the enterprise edition of SQL Server 2005 installed on my database server.
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS as corp_emp_ids WHERE (EmployeeID BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
UNION
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS AS CORP_EMP_IDS_1 WHERE (CustAtr BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
I know this question has been asked here before, but it was ridiculously answered : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=98764&SiteID=1
I have a text object on my crystal report, to which I programmatically want to assign text in CS. It all works fine, except that if I have html tags in there, the text doesn't get formatted, it just displays the html tags as text as well.
Now in thread linked above, the answer was: right click the text object, and format the text object, hit paragraph text and select text interpretation to HTML. The only problem is, that for a text object, it doesn't have the text interpretation option. It's only an option for a formula field. It says so in the tech support link posted inside the post itself!!!
So if you have any idea how I can do this, please someone help me out. I am new to crystal, and there's not much helpful info on it online. I've searched for this for hours, and no solution.
Hi,I have setup a report model and am ready to deploy it for the first time. I have had no issues deploying my report definitions so presumably this should be alright.However, trying to deploy it gives this error:
TITLE: Microsoft Semantic Model Designer ------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager() at Microsoft.ReportingServices.WebServer.Global.StartApp() at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}
</style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul> <li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.</li> </ul> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </body> </html> --. (Microsoft.ReportingServices.SemanticQueryDesign)
I'd greatly appreciate any insight you could give me into fixing this problem.ThanksJohn
I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins.
I have report which uses an ssis package as a datasource. when I develop the report on the visual studio seems like it runs fine. But when I deployed the same report on to the server (report server 64 bit machine) it is throwing me an error:
An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)
I have both sql sever and reporting server on the same machine and using windows credentials for both the servers(sql and reporting).Then I believe there is no credential issue.
HiHow can i make textreports after backup with t-sql without usingDatabase Maintenance Plan.thanksalex*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have tryed to insert a textbox in an SQL Server Report. With normal text it is easy to show the text from an access database with the help of a dataset in the report. But in my database I have text as RTF. Is it possible to show the formated text in an Report?
Hopefully anyone have the answer or an idea how it is possible to show rich text formated text in a report.
Where horseid id my parameter which is needed by report to generate the required data.
This query paramter value is picked from the text box on my jsp page.
But my problem is when i go to this url it says the message
"The 'HorseID' parameter is missing a value ".
In my report i have disabled the prompt user option , enable the Hide paramter option and has given no default value for this parameter.
I have done a lot of search on this topic. Everybody says that we can pass the values from the url like i am using. But still its not working. This forum is my last hope. Any body has any idea please give me some idea.
I have a report with 2 tables (table1 and table2) in the report body. I have txtempname kept in table 1. I access the textbox value in the header using the following expression
=(report!txtempname.value)
This works fine. However when table2 gets populated on of the subsequent pages, the value in the header disappears. It is visible only for the 1st 10 pages where the table 1 data is there.
How to make sure that the value of the header displays for all the pages
I am creating this OLAP matrix report and I tried to speed up the time needed for the OLAP dataset by limiting the number of columns in the dataset. I have two parameters, year and month, for the dataset which are set to multi-select values. Year and month are not assigned to the dataset as for the reason mentioned above (performance). However I do want to show the year and month in the matrix report. Can I use the parameter value when assigning the grouping for the year and month like "Parameters!DateCalendarYear.Value" etc and the same to the text box in the matrix report?
I tried it to the report and it returned with an error saying "The Group expression used in grouping 'matrix_....' returned a data type that is not valid"
I want to display a varbinary field in the reports. the field contains value "<B> example </B>. When viewed in report viewer, it displays the value as such.
How can I get this exported as it is to the .txt file?
Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.
How can this be achived using SSRS? Is any custome code required? Thanks in advacne for your help.
