Flat File Data Source with variable number of delimited columns

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - ODBC Connection Fails from cluster node Its a system dsn, with SQL Server driver. The server is my virtual server name, and it uses sql authentication. Does that answer your question My odbc connection by itself works fine, but it doesn't when called from another app. Tag: Flat File Data Source with variable number of delimited columns
  - 2
    - SQL Server 2005 - Building a development Server I guess it comes down to money and purpose. If you have the $$, additional disks would be very useful. However, this is a development server -not a performance testing server. You can probably live with one disk, then if (and when) you decide that another disk is worthwhile, you can add one. (Or you can add another one upfront.) I have set up many development servers with only one disk. Tag: Flat File Data Source with variable number of delimited columns
  - 3
    - Silent install using VBScript I just got this to work. You will have to work with the command line parameters to find out what will make your install silent. Set WSHShell = WScript.CreateObject("WScript.Shell") strApp = "<PathToFile>\<InstallFile.exe> /q" WSHShell.Exec(strApp) Hope this helps. Tag: Flat File Data Source with variable number of delimited columns
  - 4
    - Sql Server Reporting Services 2005 returns 404 not found It sounds like IIS/Security may be locked down on this server. Have you checked the file permissions to the report manager files C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager Can you access a web page from the default web c:\inetpub\wwwroot try adding a simple .htm page and pulling it up thru a browser. Is home.aspx set as the default document under the Reports virtual directory properties Tag: Flat File Data Source with variable number of delimited columns
  - 5
    - exception handling - best practices Rather than using IF EXISTS, you could check for the existance of a duplicate row within the INSERT statement, see below. The values to be inserted are used to form a derived table. IMO this is wasteful of resources as the validation has to be performed twice - once within the query and again by the unique constraint, but it is another option for you to persue. Chris Code Snippet IF OBJECT_ID ( 'tempdb..#tmp' ) IS NOT NULL DROP TABLE #tmp GO CREATE TABLE #tmp ( a INT , b INT , c INT ) GO --Create a constraint CREATE UNIQUE CLUSTERED INDEX [PK_#tmp] ON #tmp ( a , b , c ) GO --The values we wish to insert DECLARE @a INT SET @a = 1 DECLARE @b INT SET @b = 2 DECLARE @c INT SET @c = 3 --Insert the first row INSERT INTO #tmp ( a , b , c ) VALUES ( @a , @b , @c ) --Insert the second row, but only if a row --containing the values doesn't already exist INSERT INTO #tmp ( a , b , c ) SELECT t . a , t . b , t . c FROM ( SELECT @a AS a , @b AS b , @c AS c ) t WHERE NOT EXISTS ( SELECT 1 FROM #tmp mt WHERE mt . a = t . a AND mt . b = t . b AND mt . c = t . c ) --Check @@ROWCOUNT to determine whether to raise an error SELECT @@ROWCOUNT Tag: Flat File Data Source with variable number of delimited columns
  - 6
    - Scheduling an automatic backup Thanks for your reply.iam new to the database side,what i need is i want script for automatic database backup and send me mail for every day after the backup is done succesfully or not, in sql server 2005 management studio.did you able to help me pls........... Tag: Flat File Data Source with variable number of delimited columns
  - 7
    - Using Integrated Security from .Net Web to access SQL Server 2005 Well, then you will have to set the SPN (service principal name) for security delegation. http://support.microsoft.com/kb/319723 Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: Flat File Data Source with variable number of delimited columns
  - 8
    - Accessing the underlying sql query from the rdl file Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group). Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd). I can then manually drill down and retrieve the SematicQuery xml from the commandText field. The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of. Is this possible Thanks, Arjay P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0. Tag: Flat File Data Source with variable number of delimited columns
  - 9
    - IBM DB2 as Data Source Hi, I installed the OLE DB Provider for DB2 from the link provided. However, when I am trying to create a new connection using the "Connection Manager", I am getting the following error: "Test connection failed because of an error in initializing the provider. The parameter is not correct." I have provided the Server IP address in the "Server or filename" field and a specific user name and password. Interestingly, I am able to connect to the same server in my DTS package using the iSeries Access ODBC driver (by creating a system DSN). I am using SQL Server 2005 Developer Edition - is that the problem Regards, B@ns Tag: Flat File Data Source with variable number of delimited columns
  - 10
    - Reporting Services 2005 Subreport issue... Maybe I'm not fully understanding the problem, but why can't you use navigation from the subreport back to the parent report and simply pass the value that you need back as a parameter Tag: Flat File Data Source with variable number of delimited columns
  - 11
    - SSIS Data Conversion: Convert a String (like yyyymmdd) into Datatype dt_DBDATE or dt_DBDATE Hi Rafael, Thank your for the link to the thread. The urgent thing I found: "It's vitally important to have both a success path and an error path coming from your Derived Column transform." I configured an ErrorOutput and now it works!!! Great. ulrikeG Tag: Flat File Data Source with variable number of delimited columns
  - 12
    - SQL Compilation and execution plan Hi all, I’m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer: QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something Tag: Flat File Data Source with variable number of delimited columns
  - 13
    - SQL Reporting Services - Page X of XX counts in header Thank you for responding to my question. The issue I am finding with row count is that the rows in a group can vary. I need a group to end (total) on one page and start the next group on the following page (keep groups together). I can also have some variation in row height for txt wrap. I have been playing with this. I was able to place a textbox in the header and point to the report item value and all looked great, page X of XX. Then I checked out the print rendering and yikes, it is all over the place. Thats where I am at right now. I will need to export to PDF and Excel too. I have another suggestion to try using a subreport, so I will look into that as well. If you can think of anything else please advise. Thanks! Tag: Flat File Data Source with variable number of delimited columns
  - 14
    - Export to Excel Try to merge all the cells in the header. If it was a list you had to make sure that all fields (header and data) are vertically aligned i.e. same Y-Axis location. Shyam Tag: Flat File Data Source with variable number of delimited columns
  - 15
    - ?? 933097 Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available I've read about this hotfix at http://www.kbalertz.com/Feedback_933097.aspx but I can't find anything on microsoft.com about it. It sounds like a worthwhile hotfix, but I'm wondering about the absence of an announcement on microsoft.com. Anyone else know about this hotfix Thanks! Tag: Flat File Data Source with variable number of delimited columns
  - 16
    - Layout question We have a report that is printed landscape and contains a main table with values. Inside this table we also want to create a "sub-report" table based on two other criteria (charges and credits), but this data is still grouped with the main table. Here is a simple schematic of the report XXXXXXX XXXXXXXX XXXXXXXXXX XXXXXXXXX Charges XXXXXXXXX XXXXXXXXX Credits XXXXXXXXX XXXXXXXXX XXXXXXX We have placed a rectangle in the last column of the main table to accomodate two other "sub-tables": one for Charges and the other for Credits. We need to keep the heighth of each row in the main table the same (without extra white space) so we cannot move the second table for Credits below the table for charges. I hope this makes sense and anybody with any suggestions, it is appreciated. Tag: Flat File Data Source with variable number of delimited columns
- Next
  - 1
    - Stuck on a Inner Query Guys- i notice you're using DATEDIFF(YY,DOB,GETDATE()) < 18. Doesn't this just do the difference in the year dateparts of a date To actually do ages, i think you'd need to do something along the lines of: dob > dateadd ( yy , - 18 , convert ( varchar , getdate ()) Make sense Tag: Flat File Data Source with variable number of delimited columns
  - 2
    - help with WHERE clause I have a situation where I want to be able to match an exact string value and if that is unsuccessful then match all strings like the search parameter. The scenario I'm working on right now is searching for users by username. An example for simplicities sake is if I have the user "admin" and "administrator" in a database, if I search for "admin", I only want that single user to be returned, however if I searched for "adm", I want both users to be returned. Here's the approach I've taken so far: SELECT * FROM Users WHERE ((@Username IS NULL) OR ((Username = @Username) OR (Username LIKE N'%' + @Username + N'%'))) I expected that if (Username = @Username) was found, a single row would be returned and the rest of the clause would be ignored. However, it seems as though the LIKE is still being executed because if I search for "admin", I still get two rows returned even though I only want the single row with the exact username. What do I need to do to achieve the results I'm looking for Tag: Flat File Data Source with variable number of delimited columns
  - 3
    - SQL in OLE DB Just select the table that you want to use as your lookup. Select the checkbox for the key that you will be matching on. Don't mess with the SQL at all. Then, map the input key column to the chosen key column from the lookup table. Stop there. That's all you have to do. The green arrow coming out of the lookup transformation is for matches (typically used for updates -- because the data exists). The red arrow coming out of the lookup (usually an error output) indicates that a match was not found and is usually used for inserting data that does not exist. Tag: Flat File Data Source with variable number of delimited columns
  - 4
    - Unused Connection Managers In short, yes. You would have one config setting that would be set independent of the rest, usually as an environment variable. If you search the forums there are plenty of threads on this subject already... Tag: Flat File Data Source with variable number of delimited columns
  - 5
    - Please help me SSIS... Are the source and destination tables in the same database, different databses or on different servers Tag: Flat File Data Source with variable number of delimited columns
  - 6
    - How to debug database engine stored procedures on SQL Server 2005? 1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues. I can't find this functionality in the SQL Server Management studio. 2). By the way I also can't find where went the output of my print statement. 3). Does server cursor functionality is still working in 2k5 Tag: Flat File Data Source with variable number of delimited columns
  - 7
    - using uniqueidentifier datatype Thanks Manivannan ! Your replay was a good help for my searching. Thanks Maju. ---------------------------------------------------------------------------- Learn as if I were to live until death. after that Tag: Flat File Data Source with variable number of delimited columns
  - 8
    - Error 18210 Backup up SQL Server 2005 Database to network drive Backing up database to a UNC path is fully supported. You must be thinking of running the database from a network drive. http://support.microsoft.com/kb/304261 http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx Tag: Flat File Data Source with variable number of delimited columns
  - 9
    - Adding records Sorry, didn't think about the VB 6 issue. I think the only way forward will be to use pConn.Execute with an INSERT statement. Where does the error occur - when calling .AddNew or .Update Tag: Flat File Data Source with variable number of delimited columns
  - 10
    - Value of next record up Tag: Flat File Data Source with variable number of delimited columns
  - 11
    - Date filter in MDX The sample you posted only had one side converted to a date, the other was a string value. The example you posted has >= "2/6/2007" as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like: >= CDATE("2/6/2007") I tested this in SP2 and it works for me. Tag: Flat File Data Source with variable number of delimited columns
  - 12
    - ADO:Dispaly Limited Records Hi all; qrymillcgetbytype_app ( stored query in MS-Access) in my code : MS-ACCESS Part: query defination PARAMETERS prmMillToolType Long; SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length] FROM MILLC WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True)) ORDER BY MILLC.ID; C++ PART bstrSQL contain stored query name "qrymillcgetbytype_app" hr = piRecordSet->put_Source(bstrSQL); piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown) it opens fine but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 " it displays the same output. Where clause won't work Pls help me out. Tag: Flat File Data Source with variable number of delimited columns
  - 13
    - Property DefaultSchema is not available for Database [DatabaseName] Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one. Thanks Laurentiu Tag: Flat File Data Source with variable number of delimited columns
  - 14
    - Performance In Simple Scenarios I have done some performance testing to see if asynchronous triggers performs any better than synchronous triggers in a simple audit scenario -- capturing record snapshots at insert, update and delete events to a separate database within the same instance of SQL Server. Synchronous triggers performed 50% better than asynchronous triggers; this was with conversation reuse and the receive queue activation turned off, so the poor performance was just in the act of forming and sending the message, not receiving and processing. This was not necessarily surprising to me, and yet I have to wonder under what conditions would we see real performance benefits for audit scenarios. I am interested if anyone has done similar testing, and if they received similar or different results. If anyone had conditions where asynchronous triggers pulled ahead for audit scenarios, I would really like to hear back from them. I invite any comments or suggestions for better performance. The asynchronous trigger: Code Snippet ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER FOR INSERT AS BEGIN DECLARE @CONVERSATION UNIQUEIDENTIFIER , @MESSAGE XML , @LOG_OPERATION CHAR(1) , @LOG_USER VARCHAR(35) , @LOG_DATE DATETIME; SELECT TOP(1) @CONVERSATION = CONVERSATION_HANDLE , @LOG_OPERATION = 'I' , @LOG_USER = USER() , @LOG_DATE = GETDATE() FROM SYS.CONVERSATION_ENDPOINTS; SET @MESSAGE = ( SELECT CUST_ID = NEW.CUST_ID , CUST_DESCR = NEW.CUST_DESCR , CUST_ADDRESS = NEW.CUST_ADDRESS , LOG_OPERATION = @LOG_OPERATION , LOG_USER = @LOG_USER , LOG_DATE = @LOG_DATE FROM INSERTED NEW FOR XML AUTO ); SEND ON CONVERSATION @CONVERSATION MESSAGE TYPE CUSTOMER_LOG_MESSAGE ( @MESSAGE ); END; The synchronous trigger: Code Snippet ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER FOR INSERT AS BEGIN DECLARE @LOG_OPERATION CHAR(1) , @LOG_USER VARCHAR(15) , @LOG_DATE DATETIME; SELECT @LOG_OPERATION = 'I' , @LOG_USER = USER() , @LOG_DATE = GETDATE() INSERT INTO SALES_LOG.DBO.CUSTOMER SELECT CUST_ID = NEW.CUST_ID , CUST_DESCR = NEW.CUST_DESCR , CUST_ADDRESS = NEW.CUST_ADDRESS , LOG_OPERATION = @LOG_OPERATION , LOG_USER = @LOG_USER , LOG_DATE = @LOG_DATE FROM INSERTED NEW END; Tag: Flat File Data Source with variable number of delimited columns
  - 15
    - Append one Table one with Table Two Thank you, that really helps to see what we are working with. I noticed that in [Main Table], [IR Number] can be NULL, while in [IncidentReports], [IR Number] is NOT NULL. Could there be NULL values in the [Main Table] for [IR Number] If so, attempting to add those record to the reporting table will fail due to the NOT NULL constraint. Tag: Flat File Data Source with variable number of delimited columns
  - 16
    - Outputting ￡ sign to file using BCP problem............ can any1 please help me here as it's driving me mad!!! I've got a bcp command which outputs to file from a query. One of these fields is a nvarchar that contains text including the ￡ sign. Whenever i output it to file it changes the ￡ to "\x9c" (black square) in PFE or " " in Notepad. I've tried changing this to a $ sign and it outputs fine. The script works by taking various information / hardcoded text and inserting to a temporary table then outputs to file. I've checked the information in the temporary table and it displays the ￡ no problem, its just seems to go crazy when outputting to file! I've checked the regional settings and everything is set to UK and currency ￡'s. Am i missing something here the output part of the script i'm running is: ................. ................. DECLARE @FileName varchar ( 50 ), @bcpCommand varchar ( 200 ) SET @FileName = 'e:\' + CONVERT ( char ( 8 ), GETDATE (), 112 )+ '_ITEMPOSIDENTITY.0' SET @bcpCommand = 'bcp "SELECT data FROM ##itemp" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -U -T -P pw -c' Whilst i'm on, can any1 tell me why i can't use a local temporary table with BCP I've had to use a global temp because during the stored procedure it finds the table fine but when it comes to outputting the query in BCP it says it can't find it any help much appreciated!!! Tag: Flat File Data Source with variable number of delimited columns

Greg Ritter

I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).

Is it possible to use a single flat file data source that will work with all of the different width files

Top

Re: Flat File Data Source with variable number of delimited columns

Phil Brammer

No.

The only thing you can do is read in each line as one big record and then maybe use substrings or something to pick apart the files.

Top