Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

Geocoding in SQL Server 2000 DTS Package

Anyone have any examples or information on how to best accomplish
geocoding in a SQL Server 2000 DTS package?
Spencer"stabbert" <spencer@.tabbert.net> wrote in message
news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> Anyone have any examples or information on how to best accomplish
> geocoding in a SQL Server 2000 DTS package?
> Spencer
>
How are you doing your geocoding? Are you trying to store lats and longs?
A key that maps to lats and longs? Are you using a zipcode, or county, or
state value to determine your geocoding?
Rick Sawtell
MCT, MCSD, MCDBA|||We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||Here's as start, but you need SQL Server 2005.
http://msdn.microsoft.com/library/d...
alFuncSQL.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"stabbert" <spencer@.tabbert.net> wrote in message
news:1152231254.150274.286890@.s13g2000cwa.googlegroups.com...
We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||I have written a VBscript that will be embeded in a DTS package that
will use the MSXML2.ServerXMLHTTP.3.0 object to make calls to the
Google and Yahoo API used for geocoding. The simple HTTP calls are all
that I need and are simple. I can then get the elements I need out of
the XML returned. I plan to expand this eventually ontok.com and
geocoder.us for commercial type applications and they offer the same
simple HTTP REST interface.
Spence

Geocoding in SQL Server 2000 DTS Package

Anyone have any examples or information on how to best accomplish
geocoding in a SQL Server 2000 DTS package?
Spencer"stabbert" <spencer@.tabbert.net> wrote in message
news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> Anyone have any examples or information on how to best accomplish
> geocoding in a SQL Server 2000 DTS package?
> Spencer
>
How are you doing your geocoding? Are you trying to store lats and longs?
A key that maps to lats and longs? Are you using a zipcode, or county, or
state value to determine your geocoding?
Rick Sawtell
MCT, MCSD, MCDBA|||We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> > Anyone have any examples or information on how to best accomplish
> > geocoding in a SQL Server 2000 DTS package?
> > Spencer
> >
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||Here's as start, but you need SQL Server 2005.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/TblValFuncSQL.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"stabbert" <spencer@.tabbert.net> wrote in message
news:1152231254.150274.286890@.s13g2000cwa.googlegroups.com...
We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> > Anyone have any examples or information on how to best accomplish
> > geocoding in a SQL Server 2000 DTS package?
> > Spencer
> >
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||I have written a VBscript that will be embeded in a DTS package that
will use the MSXML2.ServerXMLHTTP.3.0 object to make calls to the
Google and Yahoo API used for geocoding. The simple HTTP calls are all
that I need and are simple. I can then get the elements I need out of
the XML returned. I plan to expand this eventually ontok.com and
geocoder.us for commercial type applications and they offer the same
simple HTTP REST interface.
Spence

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 an SQL script for a DTS Package

Can an SQL script be generated for a DTS package? I need to be able to
create script to recreate every object that relates to the normal
functioning of the database. Most other objects are easy to get an
executable script to recreate. I can't seem to figure this one out.
Thank you,
JulianDTS packages can include non-SQL structures like VBscripts etc. So the only
ways, you can save them as structured storage file, VB file, Metadata
Service or as binary data in MSDB database. Each of the options can be found
in SQL Server Books Online under the topic "saving DTS packages"
Anith

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 multiple rows for insert from single row

Dear all,

I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?

Regards,

Pedro Martins

Are the Y values stored anywhere? Can you not merge the two data sets?

generate filename from today

Hi,
I have to create an ascii file which name format is like vyymmdd.txt
What would be the best way to do this?

I have used DTS package to do this but file neme must change every day.
rgds
KariHi Kari
could you specify from where you create the file and what for?|||Hi,
I have a sqlserver table and I would like to generate each day a text file from that.
rgds
Kari|||Is this what you're looking for?!

select 'v' + convert(char(6), getdate(),12)+'.txt'

Regards,
Clifford Dibble