List of SQL table names and their field names

Some developers may want to get the list of table names and their field names, without scripting the database and at the same time they would like to copy the data to a new database with maybe new names and etc...
The script will provide the list of table and its fields and will insert into a the new db

Instruction:
· Change à SET @NewDBName = 'NewDB’ and rename to the new database name
· Find “First and second format” for the format that you require (or you can use the other variable that may help you to provide the query that you need such as the “@NewFieldName” , “@FieldName”)
· Run script
· Copy the results in to a new query page
· Run script

DECLARE @FieldName AS nVarChar(max)
DECLARE @NewDBName AS nVarChar(100)
DECLARE @NewFieldName AS nVarChar(max)
DECLARE @NewFieldString AS nVarChar(max)

SET @NewDBName = 'NewDB'

SET @FieldName = ''
SET @NewFieldName = ''
SET @NewFieldString = ''

DECLARE @tblName AS nVarChar(100)
DECLARE @object_ID AS int

DECLARE LoopCursor CURSOR FOR
SELECT tbl.Name , tbl.object_ID
FROM sys.objects AS tbl
WHERE tbl.TYPE = 'u' and tbl.name = 'users'
ORDER BY tbl.Name

OPEN LoopCursor
FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID

IF @@FETCH_STATUS <> 0
PRINT ' <>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @FieldName = @FieldName + '[' + Cast (Col.Name AS nVarChar) + '], '
, @NewFieldName= @NewFieldName + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
, @NewFieldString = @NewFieldString
+ '[' + Cast (Col.Name AS nVarChar) + '] AS ' + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
FROM sys.columns Col
WHERE Col.object_ID = @object_ID
SET @NewFieldString = Left (@NewFieldString , Len(@NewFieldString) - 1 )
----------------------------------------------------------------------------------------------
----------------------------------------------------
-- First format ----------------------------------
-- PRINT ' SELECT ' + @NewFieldString
-- + ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + '_NEW]'
-- + ' FROM [' + @tblName + ']'
----------------------------------------------------
-- Second format ----------------------------------
PRINT ' SELECT ' + @FieldName
+ ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + ']'
+ ' FROM [' + @tblName + ']'
----------------------------------------------------
----------------------------------------------------------------------------------------------
FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID

;
END
;
CLOSE LoopCursor
;
DEALLOCATE LoopCursor
;

Emailing Error log file as attachments in SSIS

To email Error log files as an attachment please check the post “Making Dynamic Backup Folders” first

Over all View



I am sure that you can find other ways to do this; the concept is to give an idea
Step 1 -
Define few variables in the system

1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.
2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).
3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).
4. “uVar_ErrorOutPutFile” this is the path and file name of the Error file, remember each time this path and file names changes so that we can keep a Backup of each error log
5. “uVar_SMTPEmailServerName” you can enter a IP number if you like
6. “uVar_SMTPEmailTo_FromError” the list of people or email groups, use multi emails

Once adding the objects in the control flow...

Step 2 –
Adding connection Objects first
First for the Error file

Second for the SMTP object

Step 3 –
Starting from Script Task “SCR--- Make the correct path and file name for the Error Text File”
Note : The first 2 script have full details and explanation in “Making Dynamic Backup Folders”

In the Design Script ...

Step 4 –
Now we have to make a object that will fail, “SCR--- Make me an Error”

Step 5 –
Now Email settingsIn the executable combo-box (drop down list) select the main root of the package, and then select OnError, drag and drop the Objects needed from the ToolBox. (See next figure)

Step 6 –
Open the DFT and make drag and drop the objects from the ToolBox.

Step 7 –

Select the “SCR---Catch Error Output description” and set...

Step 8 –
Select the Flat file destination Object and set the connection manger to “FF_DST Error output File for debugging” and ...

Step 9 –
Back to the “SMT----Send E-Mail to Notify that we have an Error in the ET section” and its settings

Testing Scenarios

Just run the package.

Making Dynamic Backup Folders In SSIS

To make dynamic backup folders from SSIS you need to do is to follow the steps.
Over all View


I am sure that you can find other ways to do this and maybe much simpler, but the concept of the blog is to give you the idea

First step is to define few variables in the system


1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.
2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).
3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).Once adding the objects in the control flow...

First ---


Second – go to the Design Scripts and add the script setting


As you can see, the IF statement checks that if the variable is not empty, change the backup folder from the MainFolderPath to the New BackupMainFolderPath, this is when the customer wants to change the backup folder from the main root of the ETL to somewhere else. Check the Testing Scenarios.

Next step the FSYS


Testing Scenarios
Once finishing the setting run the package, you will see....

Assuming that the ETL is been running for months and you customer decided to change the backup folder, you won’t need to change anything in the package just simply add the new backup path (e.g. C:\NewBackUp) to the “uVar_BackupMainFolderPath” Variable and run the package.


In this case remember to add the package or ETL name in the variable
e.g. C:\NewBackup\MyETLname

Coping files in SSIS

How to Copy Files in SSIS
To copy files in SSIS all you need to do is follow these simple steps.

Overall View:



The first step is to define few variables in the system:




1.“uVar_BackupFolder” this is the folder that the files will be copied to, you must create this folder before running the package, this package does not make folder.
2.“uVar_FolderNameOfFilesToBeProcessed” Points to the folder that is required to be processed.
3.“uVar_SourceConStr_DataSource” File name and folder gets saved in on each loop
4.“uVar_SourceFileExtention” Defines the file extension type.


Next, we must configure the settings of the Loop:

First ---



Second --



The final step, setting FSYS





Testing Scenarios
Assuming that we have 2 excel files in 2 different folders...



Simply run the package from BIDS and check the backup folder and see

How to loop through files in SSIS

Most of the SSIS developers want to get the list of the files from a folder (and maybe subfolders) and save them in to an object variable or loop though them one by one so that they can use it in an object within SSIS. (e.g. *.xml, *.bmp, *.txt, most of all Excel files *.xls and etc...)
This scenario explains how to loop though the files one by one.



First step is to define few variables in the system



1. uVar_FolderNameOfFilesToBeProcessed : Points to the folder that is required to be processed.
2. uVar_SourceConStr_DataSource : File name and folder gets saved in on each loop
3. uVar_SourceFileExtention : Defines the file extension type

Next is the settings of the Loop

First ---


Second --


Next step the script setting

In this section I am going to make it easy, so I am going to use a message box to display the File and folder name.



Please Note: SSIS is not a front-end software, the message box is just for test purposes.
next click on the "Design Script" and enter the code....

Public Sub Main()
'
' Add your code here
'
MsgBox(Dts.Variables("uVar_SourceConStr_DataSource").Value.ToString)
Dts.TaskResult = Dts.Results.Success

End Sub



Testing Scenarios

Assuming that we have 2 types of file (1- Excel files 2- Text file) .....


---First test finding *.xls files
In the variable "uVar_SourceFileExtention" we have "*.xls", and in the folders and subfolders we have 2 excel files, once running the SSIS package from BIDS you'll get 2 message box one message for each loop as...

---Second test finding *.txt files
Change the variable "uVar_SourceFileExtension" to "*.txt", and run the package again in BIDS, you will have to get only one message.

---Third test finding files with a special extension *30.xls or File*.xls
In this case you will get the same message box as the first scenario except you will only get the first message, file “File20070930.xls”