Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What transformations will I need? Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
Hi all, we are rewriting a large number of DTS packages, I have done a quick sample audit of the packages, typically they involve any combination of the below;

1) Deletion of existing files,
2) Running a SQL statement or batch file,
3) Connecting to a SQL/Excel source/destination, importing or exporting data to or from it;
4) Sending emails indicating which of the previous elements failed, or overall success.


There are several packages containing ActiveX Scripting, though they SEEM only to either check whether a csv/txt file exists, rename it, or make a copy of it to a network drive and delete the original.

What are transformations in SSIS I need to create the packages?

Thanks,

JB

Post #1537916
Posted Tuesday, February 4, 2014 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 13,551, Visits: 10,425
1. File System Task
2. Execute SQL Task (SQL) or Execute Process Task (batch)
3. Data Flow with corresponding sources and destinations.
4. Send Mail Task

The SSIS development team really did their best to choose logical names for the components

ActiveX --> Script Task.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1537964
Posted Tuesday, February 4, 2014 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
For the deletion of the files you could also use a Script Task. I find it to be more useful than the File system task in some cases.

If you wanted to get real tricky, you could do most of that stuff via Script tasks (delete the files, execute stored procs or adhoc queries and send mail) you'd just have to be a bit more adept at the programmatic means rather than the GUI.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1537977
Posted Wednesday, February 5, 2014 12:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
Cheers guys!!

Another question, I have a LOT of these to build, typically I'll only need 2-3 tasks and a couple of connections...


Is it possible/worthwhile using the Wizard to create?
Post #1538324
Posted Wednesday, February 5, 2014 12:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 21,619, Visits: 15,275
IMHO - I wouldn't use the wizard.





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1538327
Posted Thursday, February 6, 2014 2:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 473, Visits: 909
Jake Shelton (2/5/2014)
Cheers guys!!

Another question, I have a LOT of these to build, typically I'll only need 2-3 tasks and a couple of connections...


Is it possible/worthwhile using the Wizard to create?


If you have a lot of similar packages to create then you might want to look into using BIML via BIDSHelper http://bidshelper.codeplex.com/wikipage?title=Biml%20Package%20Generator&referringTitle=Documentation. With this small changes can be made and packages generated without having to drag-drop-edit over and over again.

Or with clever use of variables and looping you may be able to keep the entire task down to one package*

As for having only a couple of connections you can use project level connection managers and just call these in each package.

*pure speculation seeing as I don't know the exact needs of your environment





I'm on LinkedIn
Post #1538524
Posted Thursday, February 6, 2014 4:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:00 AM
Points: 470, Visits: 478
How about creating SSIS template ? and using it while creating all the packages
Post #1538571
Posted Monday, February 17, 2014 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:49 AM
Points: 163, Visits: 831
chetan.deshpande001 (2/6/2014)
How about creating SSIS template ? and using it while creating all the packages


Indeed, but can it store connection information, so as well as commonly repeating tasks, can the template store repeating sources/destinations?
Post #1542201
Posted Monday, February 17, 2014 5:21 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
If you are creating a lot of them, you might want to check out BIML:
http://bimlscript.com/

It takes a wee while to learn how to drive it, but once you do, you can create templates and produce the SSIS packages that way.
Post #1542313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse