March 10, 2011 at 9:22 am
After successfully importing multiple text files using SSIS (MS SQL 2008 R2 ETL Package) into a SQL table, I’d like assistance with the following:
Moving each flat file to an archive folder, maintaining the original file name, file structure and format
Create a report which provides detailed information on the status of a package and descriptive statistics (e.g. number of rows, time frame of completion, warnings, and error messages etc.) Automating delivery of the reports in the following formats (HTML, TEXT, and XML)
Scheduling packages using activity manager in SQL 2008 Management Studio
Creating Views and Stored Procedures
Reporting Services and Cubes; How to query data and create reports?
Detailed procedures, code, URL’s, and/or video webinars would be much appreciated. Thanks in advance for the support and guidance.
March 10, 2011 at 1:30 pm
Sounds like a school project 😉 That being said I doubt anyione in the forum will do it all for you...If you're tinkering with SSIS you should already know how to create views/procedures and schedule jobs.
You should be able to move each flat file to the archive folder, etc using SSIS. In the event this problematic, you can use TSQL to accomplish the same thing (provided that xp_cmdshell is configured to run on your SQL server and your proxy account has file system access.
This script will take the contents of a folder and move them to another folder with a timestamp on it (for future reference i.e. 20110310). This way you can go back historically to see what was loaded into your original drop folder on any given day:
DECLARE @Folder varchar(8), @SQL varchar(75)
SET @Folder = CONVERT(varchar(8), GETDATE(), 112)
SET @SQL = 'md X:\DropFolder\Archived\' + @Folder
EXEC MASTER.dbo.xp_cmdshell @SQL, NO_OUTPUT
SET @SQL = 'MOVE /Y X:\DropFolder\*.zip X:\ETLDrop\Archived\' + @Folder
EXEC MASTER.dbo.xp_cmdshell @SQL, NO_OUTPUT
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 10, 2011 at 5:43 pm
Thank you very much and I know is was a lot to ask for the first time around.
March 11, 2011 at 7:47 am
Louie.Velez (3/10/2011)
Moving each flat file to an archive folder, maintaining the original file name, file structure and format
Check out the SSIS FileSystem Task.
Louie.Velez (3/10/2011)
Create a report which provides detailed information on the status of a package and descriptive statistics (e.g. number of rows, time frame of completion, warnings, and error messages etc.) Automating delivery of the reports in the following formats (HTML, TEXT, and XML)
First you need to store all that data in a SQL Table. Therefore you'll need to incorporate some auditing into your SSIS packages.
You can either build the reports with SSRS (although there isn't an option to render in text and xml. Strange output formats if you ask me) or with TSQL. Using TSQL you can output the audit data to the formats specified.
Louie.Velez (3/10/2011)
Scheduling packages using activity manager in SQL 2008 Management Studio
There is an activity monitor in SQL Server, but I've never heard of Activity Manager. Scheduling SSIS packages is usually done with SQL Server Agent.
Louie.Velez (3/10/2011)
Creating Views and Stored Procedures
Google.
Louie.Velez (3/10/2011)
Reporting Services and Cubes; How to query data and create reports?
http://msdn.microsoft.com/en-us/library/ms170208.aspx
http://msdn.microsoft.com/en-us/library/ms167305(v=SQL.105).aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply