get % distribution of invalid data?

We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)

My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.

col1 col2 col3 col4

1 1 1 1

1 -1 -1 1

-1 1 -1 1

-1 1 -1 1

I would want my % distribution query/ssis result to return:

col1 col2 col3 col4

.5 .25 .75 0

I do not want to do a select/ssis process for each column. Any way to do this all in one shot?

You could write a script component to track the number of good and bad values, and output them to an asynchronous output. Or execute a SQL statement after the data flow to get the statistics.|||

jwelch wrote:

execute a SQL statement after the data flow to get the statistics.

That's what I would try first....just to keep it simpler

Generating reports from flat files

Hi

I got two text files with data.I got to compare two files and if there is any inconsistancy between two files I need to dispaly as a report using sql reporting services.I do not know how to do that?

Any source code or suggestion.

Thanx in advance

What is the format of the data files? By "comparing", is this line-for-line, character-for-character, or is the structure of the files regular data rows?

>L<

|||line-for-line|||

Different people will give you different advice about the best thing to do here, depending on which environment they are comfortable working in. All the different types of advice will boil down to the following steps:

consume both files into the same structure, which has one row for each row of the file(s).

It doesn't matter whether this is an XML structure or a database table, or how the data gets into that structure, that is a detail of implementation we can discuss when you tell us more about your background. You could use an SSIS package, read the files in CLR code a line at a time, etc.

Here are some thoughts about exactly what you should store:
I think it would be useful to have an integer value representing line number in this case as well, for reporting purposes.
Make some determinations about what represents a significant change between the files. This will make some differences in how you store the rows. For example: do you omit "whitespace" lines -- can they be safely skipped? Suppose that (for instance) the two files are identical but row 80 has an extra carriage return in one file. Does that mean that all the rows from 81 down are counted as different from each other, working on line number basis only? Or are they the same, even though the line numbers are offset by 1? is punctuation significant? Is case-sensitivity?
|||

Thanx for you advice.

I have done like this but how will you give the xml structure generated from flat files to the reporting engine.

Any idea

Thanx

|||

You can use the XML files as a datasource directly, if you design the XML to look like ADO dataset XML.

You can also have a "real" database consume the XML (regardless of its schema) and then do the report from there.

What does your XML look like?

>L<

Generating File names on the fly

Hi,

I want to create a package that can process a flat file based on the current data. i.e. name of the file contains current date and some predefined characters.

What is the best way to process it?Use a property expression on the ConnectionString property of your FlatFile connection manager to set it to the correct filename (containing the date).

-Jamie

Generating DTS flat file connection

I'm trying to generate a DTS Package with VB.Net using the Microsoft DTSPackage Object Library and
and the Microsoft DTSDataPump Scripting Object Library
I have to load csv files into SQL tables.
I could generate both a SQL connection and a FlatFile connection and the transformationtask.

When I look at the transformationtask and click on the transformation tab I get this error

"Incomple file format information"

The problem is I don't find where I could set the FlatFile connection properties like "Text Qualifier" and
"row delimiter"

I tried this but it still shows CRLF as row delimiter when I look at the generated DTS Package

Dim oConnection As DTS.Connection2
Dim package As DTS.Package2
Dim filename As String
filename = "myfilename.csv"

oConnection = package.Connections.New("DTSFlatFile")
oConnection.Name = filename
oConnection.ConnectionProperties.Item("Row Delimiter").Value = vbTabWhy not just bcp the file out using a stored porcedure?

Generating and Printing Reports

I've created a data flow where I have linked to an OLE DB Source then created a Flat File Destination. My file is now on my c: drive. I'd like to use that data to create a report, then print to a PDF. How would I do that?

Currently I was doing this in Access, but I am moving my processes to SSIS.

You wouldn't use SSIS to do that. SSIS is an ETL tool, not a reporting tool.

-Jamie

|||But if I created my report in Reporting Services for instance, how could I put that in my data flow in SSIS?|||I was also assuming that I could put this in a Script Task, but I didn't know how far I could go with generating a report from there.|||

I don't know what you mean by "put a report in the data flow" or "put it in a script task". Can you expand on that?

There is a way that you can consume a SSIS dataflow from within SSRS but that doesn't seem to be what you're after here. You seem to want to build a report on top of a text file (that just happens to have been created by SSIS). I'm sorry, I don't understand what it is you actually want SSIS to do.

-Jamie

|||

I've gotten my answer:

Write an application in VB using the Report Viewer from the toolbar to create and print the report

Then execute that application from SSIS.

|||

So you're calling the SSRS API, is that correct? If so, you don't have to write another app to do that - you can embed it into SSIS.

-Jamie

|||What do you mean by calling the SSRS API? I'm using Visual Studio 2005.|||

ifaber wrote:

What do you mean by calling the SSRS API? I'm using Visual Studio 2005.

Shall we start again. What exactly is your VB app going to do?

|||My application is going to pull data from many tables, then export to a flat file. Our network person will pick up the files, and put the data on a website. Then from 2 of those tables, I pulled a list of instructors that currently is on our website in PDF reports. In Access, I have pulled the data, created a report and exported to a PDF. Our network person pulls those reports and puts them on the website. It's pretty simple compared to some other stuff I have done.|||

Fair enough, if it works for you that's good. I was just wondering if there was an option to utilise SQL Server Reporting Services (SSRS) seeing as that is a reporting application. I mistakenly assumed that seeing as you were using SSIS that you would be trying to use SSRS for reporting - my mistake. Sorry.

I also thought you were trying to automate the creation of PDF files - something that I suspect could be done from SSRS and ergo from SSIS if there is an API available for doing that. Again it looks like my assumption was wrong.

To me, it seems strange that you would use a differrent application to do all this when it can all be achieved with SSRS but then again I am a SQL Server zealot so I would say that :)

Good luck with it.

-Jamie

|||Wellllll, I know how to use SQL Server Reporting Services too. In fact, I have a lot of reports using SSRS. Pretty much, after I have updated the flat file, I'd like to run something that would create a report and would print to PDF.|||

Right. Well what I was driving at is if SSRS can create PDF reports (and I'm reliably informed that it can) then you can call the SSRS API from SSIS in order to create those reports for you. I haven't actually done it but I'm sure its possible.

-Jamie

|||Bingo!! Now how do I call the API :)|||

ifaber wrote:

Bingo!! Now how do I call the API :)

I was worried you were about to ask that.

I don't actually know, but I've no doubt that you can do it. To know more about the SSRS API you;d be best checking out the SSRS forum.

What I DO know is that SSRS itself is a web service which means that everything that can be done in SSRS can be called via that web service. essentially that web service IS the SSRS API.

You can calls web services from SSIS using the Web Service task. HOWEVER, I am aware that there are limitations with SSIS's web service task (search this forum to find out more) but don't despair because you have another optionavailable to you. You can call web services from within the SSIS script task.using dotnet APIs.

I hope that is useful and I hope you didn't mind me continuing the thread. Its just that you seemed to want to do all from within one package and I figured it would be possible. Anything that can be accessed via dotnet code can be done from SSIS's Script Task..

-Jamie

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file

Hi ya,

I'm generating a flat file from SSIS package and i'm having some problems.

My package contains this Data Flow which is connecting to a Database and importing the records. I did design a script component since the file I generate has some padding.

Here is the code for the component so that you really understand what i'm trying to achieve over here.

Dim toPadTo As Int32

Dim myDate As String

Dim MultVal As Int64

Dim myMonth As Int16, myMonth2 As String

myMonth = CShort(Row.TransDate.Month)

If myMonth <= 9 Then

myMonth2 = myMonth.ToString.PadLeft(2, CChar("0"))

Else

myMonth2 = myMonth.ToString

End If

myDate = CStr(Row.TransDate.Date.Day) + myMonth2 + CStr(Row.TransDate.Year)

MultVal = CInt(Row.TransAmount * 100)

Dim myChkLength As Int16 = CShort(MultVal.ToString.Length)

Dim myCombine As String = CStr(MultVal) & CStr(myDate)

Dim myCombine2 As String = CStr(MultVal) & CStr(myDate)

Select Case myChkLength

Case 4

toPadTo = 30 - (myCombine.ToString.Length)

Case 5

toPadTo = 30 - (myCombine.ToString.Length - 1)

Case 6

toPadTo = 30 - (myCombine.ToString.Length - 2)

Case 7

toPadTo = 30 - (myCombine.ToString.Length - 3)

End Select

' + myDate.ToString.Length - 1))

Row.myConvert = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim MyNewRow As String = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim ChkSign As Int16

ChkSign = CShort(Math.Sign(CDec(MyNewRow)))

If ChkSign = 1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

ElseIf ChkSign = -1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("-"))

Else

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

End If

End Sub

Obviously i don't think this is the best approach hence i'm asking? not to mention the sort of problem i'm getting with the + and - insertion part of the code.

To give you an example of how the value would be:

Actual value: 159.23

After transformation it should be like this : +00000000000015923

Depending on the amount x no zeros should be inserted.

Any other way to achieve it? Apart from this I also need to generate a sequence no with some string at the end of the ragged file. How would i go for it ?

Cheers

Rizshe

Read the data in from the SQL database, add a script transformation using the input column, add an output column of type string length 30, transform like: -

Dim Style As String = "+0000000000000000000000000000#;" _
+ "-0000000000000000000000000000#;" _
+ "+0000000000000000000000000000#"
with row
dim n as int = CInt(.myoldcolumn * 100)
.mynewcolumn = Format(n, Style)
end with

then simply output the new columns to the flat file

|||

Hi Paul,

Your code doesn't seem to do anything different then my own above. Perhaps you misunderstood me.

I would need to put the 0 padding and depending on the + or - in the amount column also put the sign.

The problem i'm getting is that the amount varies from 0.94 to 124.78 and i would need to pad it accordingly.

Cheers

Rizwan

|||

Rizwan,

I may have missed something, but I believe that the code does exactly that. it creates 30 character strings with the correct sign...

|||

Hi Paul,

My deepest apologies. I must be blind that i didn't look at your code carefully.

Yes it does work.

Thank you very much and again forgive me for my stupid mistake

Rizwan

Generate Flat File via Stored Procedure

I have a need to do the following:

Generate a Stored Procedure and have the output written in a csv format.

I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.

This stored procedure will be triggered by another application.

Could someone please help.

thanks

there are many ways to do that

1. in sql server 200 you can use a dts to out put the result

2. in sql server 2005 you can use SSIS

3. you can use the openrowset function

4. you cann configure a linked server and output the result.

|||

This might help

create procedure usp_runbcppkg

as

declare @.as_bcp varchar(255)

declare @.as_query nvarchar(255)

declare @.as_select varchar(255)

declare @.as_appcode varchar(20)

set @.as_select = 'bcp "select appcode, servername, region, usage_ctr, st_date, ed_date from pubs..temp"'

set @.as_query = 'master.dbo.xp_cmdshell ' + @.as_bcp

begin

--cursor to get the list of state

declare cur_appcode cursor for

select distinct state from authors

order by 1

open cur_appcode

fetch next from cur_appcode into @.as_appcode

while @.@.fetch_status = 0

begin

Truncate & Insert the date for the state in Temp Table

truncate table temp

insert into temp (au_id, au_lname, phone, address, city, state, zip)

select au_id, au_lname, phone, address, city, state, zip

from pubs..authors where state = @.as_appcode

--<Servername> to be entered

--<Password> to be entered

set @.as_bcp = @.as_select + ' queryout c:\temp\' + @.as_appcode + '.xls -S<Servername> -Usa -P<Password> -w'

exec master..xp_cmdshell @.as_bcp

fetch next from cur_appcode into @.as_appcode

end

close cur_appcode

deallocate cur_appcode

end|||

An OPENROWSET example which emails the results.

@.reportxls must exist on the server (an empty xls flle with column headers only).

Openrowset will append results to the file. If this is what you want then remove the copy, rename and delete parts of the code.

CREATE PROCEDURE sp_Test
(@.selectcmd as varchar(400), @.email_list as varchar(300), @.reportxls as varchar(40))

as

declare @.result as int, @.attach as varchar(140), @.renamecmd as varchar(200), @.deletecmd as varchar(200), @.copycmd as varchar(200), @.insertcmd as varchar(200), @.sql as varchar(600)
set @.attach = 'd:\reports\' + @.reportxls
set @.copycmd = 'copy d:\reports\' + @.reportxls + ' d:\reports\' + @.reportxls + '#'
set @.renamecmd = 'ren d:\reports\' + @.reportxls + '# ' + @.reportxls
set @.deletecmd = 'del d:\reports\' + @.reportxls
set @.insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\reports\' + @.reportxls + ';'', ''SELECT * FROM [Sheet1$]'')'
set @.sql = @.insertcmd + @.selectcmd

EXEC @.result = master.dbo.xp_cmdshell @.copycmd
IF (@.result = 0)
BEGIN
EXEC (@.sql)
EXEC master.dbo.xp_sendmail @.recipients = @.email_list,
@.message = 'Test Body Message',
@.subject = 'Report Request',
@.attachments = @.attach
EXEC master.dbo.xp_cmdshell @.deletecmd
EXEC master.dbo.xp_cmdshell @.renamecmd
END

GO