Query Output To Flatfile Wraps

Feb 23, 2001

I am running an ISQL script every day and automatically emailing the output of a query as the body of an email, to a group of people. ISQL executes a SQL file which selects some data and the output of the ISQL is sent to a *.txt file.

The Issue is that the output of the query wraps in the flatfile. It seems that the *.txt file wraps at character 76.

Does anyone have an Idea how to prevent query output data from wrapping in the flatfile?

-thanks
-tom

View 1 Replies

Flatfile Output Truncating After 255 Characters

Apr 2, 2007

Hi,

iam bringing an output in flatfile which is truncating after 255 characters,some of the data is vanishing because of this.can anyone pls help.

pls advice.

Regards,

sg

View 7 Replies View Related

Output Columns For Flatfile - Text Qualifiers Dont Show Up

Sep 14, 2007

I explicitly set one column to have text qualifiers in a flat file connection mgr and specified to use double quotes as the qualifier, yet in the output file, the column is not qualified. What did I leave out ?

View 2 Replies View Related

Reporting Services :: Report Viewer Toolbar Wraps

Dec 23, 2010

Have an ASP page using AJAX - have embedded the latest Microsoft Report Viewer control. The page access various SSRS reports. The application works fine for most users, but in certain cases, the toolbar wraps and occupies about ten lines - using about half of the visible area. Users affected by the problem are using IE7 and IE8. When the toolbar is distorted like this, the buttons (Page, Export, etc) also seem inop. The problem does not seem to be related to the RDL; either all the reports work or none of the reports work.

View 2 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

Flatfile Into MSSQL

Aug 3, 2004

I currently have a flatfile with a seperate COBOL copybook. I need to be able to import all of it correctly into a db in mssql. Are there any *free programs that will turn the flatfile into some form for insertion into the db? What about csv format into the db?

View 14 Replies View Related

How To Replace A Row From A Flatfile In A Table

Jun 30, 2006

Hello again,

As a beginner I would like to replace records in a Table with Records from a Flatfile.

ILet's say, I got a custnum from the Flatfile and I like to replace the whole record of custnum (Primary Key) in a Table.

Can someone give me a hint how to do?

I'm looking forward to an early answer.

Regards

Chaepp

View 3 Replies View Related

What Is The Difference Between FlatFile And File ?

Jul 18, 2007

What is the true difference between FlatFile and File in terms of ConnectionManagerType? If FlatFile is just a subset, why does SSIS architechture need it? What is the reasoning behind?

View 1 Replies View Related

Get FlatFile Columns Through ConnectionManager

Mar 29, 2006

The CreatePackage sample provided with SQL Server programmatically creates a package that has a source type of OLEDB to a flat file destination. I am building exactly the opposite, source=flatfile, destination=SQL Server. I expect that will be a more common scenario is using SSIS.

The problem I have is populating the source columns in the FlatFileSource connection manager programmatically. I know it can be done because it happens when you build a package in Visual Studio. What I'd like to know is how to do it programmatically in the object model. How can I interrogate the datasource through the connection manager to find out what columns it has? If I know, I can add the columns to the connection manager. My sample below does this, but it doesn't know the number of columns in the source so that value is hardcoded. I'm guessing there is a better way to do this than what I've got below.

How can I find the number of columns in my source so I can add the columns to the connection manager?

Thanks.

Private Sub AddColumnsToFlatFileConnectionManager()
Dim ff As wrap.IDTSConnectionManagerFlatFile90 = Nothing

For Each cm As ConnectionManager In _Package.Connections
If cm.Name.Equals(_ExternalConnectionID) Then
ff = TryCast(cm.InnerObject, wrap.IDTSConnectionManagerFlatFile90)
DtsConvert.ToConnectionManager90(cm)
End If
Next

If Not ff Is Nothing Then

Dim col As wrap.IDTSConnectionManagerFlatFileColumn90
Dim name As wrap.IDTSName90
Dim Min As Int32 = 0
Dim Max As Int32 = Min + 3 ' *** HARDCODED LIMIT ***

For cols As Integer = Min To Max
col = ff.Columns.Add()

If cols = Max Then
col.ColumnDelimiter = vbCrLf
Else
col.ColumnDelimiter = ","
End If

Dim width As Int32 = 50
Dim DataType As wrap.DataType = wrap.DataType.DT_STR

col.ColumnType = "Delimited"
col.DataType = DataType
col.MaximumWidth = width
col.DataPrecision = 0
col.DataScale = 0
col.ColumnWidth = width
name = TryCast(col, wrap.IDTSName90)
name.Name = "Column " & cols.ToString

Next

End If

End Sub

View 4 Replies View Related

Changing The Format In Flatfile

Jan 28, 2008

Hi,

Iam migration data from a table into a comma delimited flatfile,but i need to specify all the columns within [ " ] in the flatfile

for example

i have a column [Name] the values are John,Mani,Raghu.....

The flat file should be outputted as
"John"
"Mani"
"Raghu"

Is there anyway to do this.Pls help.

Thanks,
SVGP

View 3 Replies View Related

FlatFile For LookUp Mismatch

Mar 27, 2007

Hi all,

my package has a lot of lookups of fact table fields against dimension tables.

i did a redirect row for each lookup error to a flat file.

i wanted to put all lookup mismatch on the same file, but i couldn't do it because there'll be error saying that

The process cannot access the file because it is being used by another process.

i don't think it's practical to have flat files for each lookups. how is this normally done? please help...

thanks!

View 13 Replies View Related

Using Expressions With FlatFile Connections?

Jan 3, 2007

This seems so obvious, but is causing me a lot of trouble:

The flat files input to my SSIS packages may contain an empty line between each row of data, or may be "continuous" without any extra empty lines. Here are small examples of the data I'm working with:

With the extra line:

"M","SFH","Single Family Mortgage Loans","Single Fam Mtg"

"M","MFH","Multifamily Mortgage Loans","Multi-Fam Mtg"

"R","MIX","Mixed/Various/Unknown","Mixed/Various"

Without the extra line:

"M","SFH","Single Family Mortgage Loans","Single Fam Mtg"
"M","MFH","Multifamily Mortgage Loans","Multi-Fam Mtg"
"R","MIX","Mixed/Various/Unknown","Mixed/Various"

Seems to me it should be possible to set up a single FlatFile connection to handle both these formats, but it's not easy.

I can easily manually edit the FlatFile connection and change the "RowDelimiter" to either "{CR}{LF}" or "{CR}{LF}{CR}{LF}" and the package runs against the corresponding flat file.

However, when I try to use an expression (@[User::StrRowDelimiter]) to set the RowDelimiter to the value of a package variable, the expression is completely ignored.

I can only change the RowDelimiter property on the FlatFile connection by manually opening the editor on the connection and changing it to match the incoming file.

Why isn't this connection seeing the expression I've set up for its RowDelimiter property?

Thanks for any help!

View 8 Replies View Related

Conditional Split To FlatFile

Sep 10, 2007

Hi,

I have a Conditional Split to FlatFile Destination.

How can I put the result, that goes in the FlatFile Destination, in a variable also (like in Recordset Destination).

Do I have to runs this thing twise (and put the first time in FlatFile Destination and the second time in Recordset Destination)?

Thank you.

View 3 Replies View Related

Skip A Row In The Flatfile Source

May 20, 2008

I am importing a flatfile and cannot seem to deal with an issue that seems quite simple.

The files have a header row with column names and those rows start with '#'

However sometimes this header row will also be present in the middle of the file.

The Source tries to parse this row and fails

Is there any way to tell the flafile source to skip rows that start with a particular character like comment rows?

View 5 Replies View Related

FlatFile Connection And Security

Jun 24, 2005

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

View 11 Replies View Related

SQL Query Output To XML Using BCP HELP!!

Aug 24, 2007

I am having trouble in writing code to save the SQL query output to an XML file.

A simple example like below is working fine and it creates an output a.xml.

------Begin Code-----
Declare @fNameTemp varchar(50)
Declare @SQL varchar(3000)
Set @fNameTemp = 'c:XMLDumpa.xml'

Set @SQL = 'bcp "Select top 10 * From test..month For XML Auto, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQL
------End Code ------

But I am having trouble in writing when I change the Select query to a more complex one as in the example below. The code is part of SP.

------Begin Code-----
USE [test]
GO
Declare @TblName1 varchar(10)
Declare @TblName2 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @TblType varchar(10)
Declare @Thisyear varchar(10)
Declare @Lastyear varchar(10)

SET @TblName1 = '2006exp'
SET @TblName2 = '2005exp'
SET @District = 1
SET @Month = 2
SET @TblType = 'exp1'
set @Thisyear = '2006'
set @Lastyear = '2005'

set @TblName1 = '[' + @TblName1 + ']'
set @TblName2 = '[' + @TblName2 + ']'
set @TblType = '[' + @TblType + ']'

Declare @fNameTemp varchar(50)
SET @fNameTemp = 'C:XMLDump2.xml'
Declare @SQuery nvarchar(3000)

SET @SQuery = 'bcp "Select d.descrip_1 as [Commodity Description], ty.HS4, ty.Amount1 as ['+ @ThisYear +' Value ($)], (ty.Amount1/ty.Total1)*100 as ['+ @ThisYear +' Share (%)] ,
py.Amount2 as ['+ @LastYear +' Value ($)], (py.Amount2/py.Total2)*100 as ['+ @LastYear +' Share (%)]
FROM
(Select top 10 a.commodity1 as HS4, Sum(a.all_val_mo) as Amount1,
(select Sum(a1.all_val_mo) FROM test..' + @TblName1 + ' a1 where a1.stat_month <=' + @Month + ' and a1.district=' + @District +' ) as Total1
FROM test..' + @TblName1 + ' a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1 Order by Amount1 DESC) ty
JOIN
(Select b.commodity1 as HS4, Sum(b.all_val_mo) as Amount2,
(select Sum(b1.all_val_mo) FROM test..' + @TblName2 + ' b1 where b1.stat_month <=' + @Month + ' and b1.district=' + @District +' ) as Total2
FROM test..' + @TblName2 + ' b where b.stat_month <=' + @Month + ' and b.district=' + @District +'
Group by b.commodity1) py on ty.HS4=py.HS4 LEFT OUTER JOIN '+ @TblType +' d on ty.HS4=d.commodity1
Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'

Exec master..xp_cmdshell @SQuery
------End Code ------

Output for above code is shown below:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

I don't know what is the mistake I am doing. I would appreciate if anyone can help me figure the error.
http://www.fiu.edu/~atmakurk/ustrade/SQL_XML1.jpg

The above query works perfectly if I remove BCP & For XML Auto part. Here is a screen shot of Successful query results.
http://fiu.edu/~atmakurk/ustrade/working_example.jpg

View 1 Replies View Related

Import From A Flatfile With Rows That Have Different Formats

Jan 23, 2008

Hello Folks,

Im trying to use Integrations services to import a file on a daily basis.

My flatfile has to different type of rows that comes from an transaction system, it looks like this:

132,1/1/2008,00,123654,text,1,123.00
132,1/1/2008,00,123652,text,1,23.00
132,1/1/2008,00,123655,text,1,3.00
123,1/1/1/2008,01,149.00
1125,1/1/2008,00,123654,text,1,123.00
1125,1/1/2008,00,123652,text,1,23.00
1125,1/1/2008,00,123655,text,1,3.00
1125,1/1/1/2008,01,149.00;Cash;EUR;01;12

After the date you can see that there is two digits number either 00 or 01. The rows also have a different lengthts.

When ever that columns contains 00 the line should be inserted to a special text file, if the columns contains 01 it should to another file.

How can I solve this in a good way?

One of the problems I have is that when I try to import the rows the flat file connections indicates(erros message) that I have partial row in the file which is true since the the rows with the columns content 01 have more fields then the other.

Thanks for you help.

holtis

View 3 Replies View Related

Flatfile Destination Variable Filename

Jun 20, 2007

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.

View 5 Replies View Related

Flatfile Destination Expression Problems

May 11, 2007

Hi:

Am trying to write SQL data to multiple flat files.

I use a For Each loop,

store field Values in variables

construct a fileName(variable as expression) for each row,

then create a text file for each row in resultset, (Filesystem task)

and then try to fill each file from a SQL Source to a Flatfile Destination.

Destination.connectionString=Filename.

Works ok for creation of Text Files, so I know my fileName variable is getting evaluated for each iteration.

But the flat file connection manager is stuck and evaluates to the static part of my expression.

What am I doing wrong?

TIA

Kar

View 5 Replies View Related

Populate Flatfile Source From Variable

May 21, 2007

We are storing incoming flatfiles into a text field in a table and then we are processing this table on a regular basis. What I would like to do is to get this flatfile from the textfield and populate a flatfile source with it, but so far I have only been able to do that with XML files as there are no option for doing that with the flatfile source. Using the disk as a temporary storage for the flatfile is prohibited.

Does anyone have any suggestions on how to solve this?

View 4 Replies View Related

Two Issues With Bids Flatfile Import

Dec 28, 2007

The first is the

With every keystroke in the flat file connection manager editor on the filename
the bids system goes out and trys to find the file
this is stupidly slow when using \sqldevelopc$zipcode.txt

The second is creating an import from a flat file to database file using data flow task

when you run the task I get the following error trying to open a c:demozipcode.txt file

[AdventureWorks [30]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

I can see there is an issue with BIDS that when you developing it is using the local drive C drive and running it using the servers C drive

I have tried unc paths also and it doesnt work either

Everything in the connection looks fine, i can see the data, the columns etc

I can get this to work using bulk load task and the exact same connection, but not in the data flow task

View 8 Replies View Related

Reading Specific Line In Flatfile

Jun 14, 2007

Hi,

I have a flatfile source. I want to extract a specific row... let say row 2. how do i retrieve that row from the list of rows in my flat file?

thanks,

Cherriesh

View 1 Replies View Related

Import From Flatfile With Rows That Have Different Format...

Jan 23, 2008

View 8 Replies View Related

Import Dedicated Records From A Flatfile To A DB

Jun 29, 2006

Hello all
I got a Problem when I try to store Data from a Flatfile to a DB.
The following Error appears in the Progress Control:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Products_1'. Cannot insert duplicate key in object 'dbo.Products'.".

I have a Flat File Source, and would like to store the needed records in a DB.
In Column 0 in the Flatfile I have multiple Entries with equal Values.
In the DB this Column is set as Primary Key and can only have one Record with the same Value in this Column.

How can I read out (or store) only one Record with the same Value from the Flatfile to store it in the DB?

How can I check if there is a Record from the Flatfile in the DB with the same value in the Primary Key?

How can I change any of the remaining Columns with different Values in the DB to match with the Flatfile?

Thanks in advance for any answer

Chaepp

View 9 Replies View Related

Issues With Sql Query Output

Jun 7, 2008

Iam using the below XML request with OpenXml function in sqlserver to get field values.
But iam not able to get values for  "DeviceType,DeviceID,FeatureLink" also i want to format the value of the devicetype field
<Device Type="KWS,KL,2007,6"> it should return "KWS" alone how can i do that please advice.
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<Response ><DetaisInfo>   <Tag/>   <InstrService>DEVICE_INFO</InstrService></DetaisInfo><Devices>   <Device Type="KWS,KL,2007,6">     <Field name="DeviceType">0.00</Field>     <Field name="DeviceID">12.00</Field>     <Field name="FeatureLink">25700</Field>   </Device>   <Device Type="SNY,KL,2007,6">     <Field name="DeviceType">1.11</Field>     <Field name="DeviceID">0.10</Field>     <Field name="FeatureLink">21667</Field>   </Device></Devices></Response>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM   OPENXML (@idoc, '/Response/Devices/Device',2)         WITH (Type       varchar(10)         './@Type',               DeviceType varchar(10) '/@name',               DeviceID   varchar(10)    '/@name',               FeatureLink     varchar(10)         '/@name')
OutPut
Type                   DeviceType     DeviceID      FeatureLink ----------                ----------             ----------          ----------- KWS,KL,200       NULL                NULL          NULLSNY,KL,200         NULL                NULL          NULL
(2 row(s) affected)
Please advice

View 6 Replies View Related

Output Query To A File

Jul 25, 2001

I have a query something like this:
select "bcp EISAT_08_18.."+name +" OUT C:"+ name+".TXT -c -t -SCJACOBI"
from sysobjects
where type = 'U'
ORDER BY NAME
When I run the above query I want to output the result of the query to a file.
Can someone help me on that?

View 8 Replies View Related

Output The Query Into A File

Jul 31, 2001

Hi all,
When I run a query in the sql query analyzer I need to write the output of that query in to another file. In Oracle its spool. Can someone help me on this please. Thank you!!!

View 1 Replies View Related

Reformatting Output From A Query

Jul 26, 2004

Hey everyone,

as of now i have a sql chunk

Code:

SELECT VIEW.DWG_NAME
FROM VIEW, qry_TURNOVER
WHERE (((VIEW.ID)=[qry_TURNOVER].[CURRENT_ID]));

which works but when i get the results it orders them according to number ( or what seems to be in the order of ID), and i need it in the order that it appears in the column CURRENT_ID

If that is not clear This is what is happening

For example, we have:

qry_TURNOVER
CURRENT_ID
4
5
1
3

VIEW
ID DWG_NAME
1 23g
2 24H
3 25J
4 26K
5 27L

As it stands,The results auto format to:

23G
25H
26K
27L

I need the results to be:

26k
27L
23G
25J

Hope that was clear, any ideas? Im not sure if this is even possible. Any hints or tips to a solution would be much appreciated. The reason why current ID is unordered is because its has other data in its table that is ordered via that way. So current ID cannot be orderd to match out out, it must be the other way around.

Thanks
Doc

View 3 Replies View Related

Query Output Into A File

Feb 27, 2006

Hi,

In a stored procedure how do I output the result of a query to a text file?

Regards,

Bharathram G

View 3 Replies View Related

OSQL Query Output

Dec 10, 2007

Hi All,

The command showed below provides the output shown:

set @cmd = 'osql -S bcgsql1cgsqldev -U checkdb -P checkdb -q "set nocount on; select errorlogdesc from ##errors where datediff(dd,errorlogdatetime,getdate()) = 1 and errorlogdesc like ''%Error:%''" -h-1 -w 900 -o J:ScriptsTestStuffErrorLogMsg.txt'

Here is the output:

Error: 17883, Severity: 1, State: 0

Error: 17883, Severity: 1, State: 0

I need to remove the space between the two lines so I set the -w parameter to 900 but that didn't fix it. Any idea how I can accomplish this?

Thanks

View 4 Replies View Related

Query Output In A Desired Way

Aug 11, 2006

Gopinath writes "Let us consider we have the following data in a table,

Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple

in the above table, i want to query and get output in the below format

Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple

is it possible? if yes, kindly let me know the query.

Thanks in advance.

Regards,
Gopi."

View 2 Replies View Related

Output Query To Xml File

Apr 13, 2006

I have an sp that runs a query in xp_cmdshell/BCP combo that puts the output into an XML file (using for xml auto hint on the end of the query).

I personally feel that I should now (under SQL 2K5) be able to do this within a query, not going any where near xp_cmdshell or bcp, and then use SSIS in the final step to ftp it up to the clients site.

It builds a command string in the sp, then runs that command string via xp_cmdshell.

I guess what I'm asking is, is it possible to run a query within an SP with the output going to an XML file on the server automatically? AND that file to be a well formed XML file?

Any suggestions?

View 4 Replies View Related