SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Is Not Just for SQL Server


SSIS Is Not Just for SQL Server

Author
Message
Tim Mitchell
Tim Mitchell
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 3009
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/tmitchell/3021.asp



Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Joao Almeida-403615
Joao Almeida-403615
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16

which the newness? the DTS in the SQL Server 2000 already was a great tool, the SSIS is only one natural evolution.

I was a beta tester of SQL Server 2005 and the SSIS is a retty tool.


Richard Tew-243440
Richard Tew-243440
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 8

Good article, everyone should be made aware that as SSIS has the ability to connect to numerous sources or destinations, not necessarily involving a SQL Server database.

I used DTS for this sort of thing on many occasions, now we have even more sophisticated possibilities.

Having said that, the syntax for the parameters appears inconsistent, I seriously need to spend a lot of time fiddling with these packages.

Does anyone have a list of useful sources for examples of SSIS packages (as well as here and Microsoft)?


Jim Russell-390299
Jim Russell-390299
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 1403

A really dumb beginners question re Public Sub Main:

Where (and in what language) is this defined?

Where is the object Dts created?


tymberwyld
tymberwyld
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 275
Jim, all "code" is done in VB.NET. The object "Dts" is created for you by the code designer and is used in Script Tasks ONLY! Do not use this object in a Script Component in the DataFlow, well, because you won't even find it. Instead, for Script Components, use the keyword "Me" (ex. Me.Variables).

Anyway, nice article. I too have been using SSIS for things that are only 20% reliant on SQL Server. For example, I am working on a package that downloads all the Census Bureau data files and then imports them into a relational database. I found that downloading the 12GB of hundreds of files manually was obviously tedius! I have a Package now that downloads all the files, extracts the ZIP files, and then for archiving re-compressing all files (per State) into a RAR archive.

These tasks are something that would have taken months to do in a standard program. I know, because I embarked on it...



Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 2384
Harvesting data from Weblogs, eh? Hmm. I seem to remember hearing something about an infamous email campaign. Something along these lines:

(1) In your database create a nonsense, but unique, filename for each "target".
(2) Use that to include in a well crafted HTML email that references an external graphic. http://www.myserver.not/winnerimage/GH567#QWCF99.jpg
(3) Now harvest the logs from the web server every day.

The HTML email has the height and width of the IMG tag set to 1 pixel each. Who would pay any attention to a failed graphic in an email. To be more evil set the alt text to say "Hidden for security".

Now the directory "winnerimage" does not even exist on the web server. You don't want to waste the space. You are only interested in the errors in the web log. Note that "GH567#QWCF99" is the key back into the database.

Each error from attempts access these images tells quite a story. You now know when that email was opened or PREVIEWED. You have the date, time, and IP address at a minimum.

There are still places that will buy lists of verified email addresses at 2 to 5 cents each.

This is not your project, is it? Whistling

ATBCharles Kincaid
srienstr
srienstr
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 438
I've found the Import/Export Tool with SQL Server 2000 to be the best tool at my disposal for extracting query results from our Oracle Server, regardless of where I need the results to go.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Jim Russell-390299
Jim Russell-390299
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1189 Visits: 1403
I was disappointed when, per the MS Connect site, that the horrible flaw in the SSIS FTP task was not fixed. (The flaw is that the source/destination directory is arbitrarily forced to start with a "\", effectively rendering the FTP task useless when working with mainframe systems.)
(Just venting...)

Aside to tymberwyld...I had missed the notification of your post answering my question about the code and saw your response only today. Thank you very much!
Wayne West
Wayne West
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 3702
Jim Russell (5/21/2008)
I was disappointed when, per the MS Connect site, that the horrible flaw in the SSIS FTP task was not fixed. (The flaw is that the source/destination directory is arbitrarily forced to start with a "\", effectively rendering the FTP task useless when working with mainframe systems.)
(Just venting...)

My hatred of the FTP task is that it is only FTP -- not SFTP. I'm developing a file transfer job right now and have to run the SFTP step as a command task.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Wayne West
Wayne West
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 3702
Charles Kincaid (5/21/2008)
...(1) In your database create a nonsense, but unique, filename for each "target".
(2) Use that to include in a well crafted HTML email that references an external graphic. http://www.myserver.not/winnerimage/GH567#QWCF99.jpg
(3) Now harvest the logs from the web server every day. ...

Which is why my Yahoo mail does not download images...

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search