Microsoft SSIS and Sql Server Notes
Thursday, June 6, 2013
Sql Server: Alter Table in Design
"Saving not permitted. The changes you have made require the following tables to be dropped and re-created or enable option Prevent saving changes that require the table to be re-created. "
To disable the option "Prevent saving changes that require the table to be re-created"
Go to Tools > Options > Designers > "Un-check" "Prevent saving changes that require the table to be re-created"
SSIS Task: For loop Vs For Each Loop
For Loop
For Loop is used for looping through a number of tasks for a set number of times.
Simply:-> initialize, verify condition, increment\assign
For Each
ForEach Loop loops through various things such as files, objects, ADO connections, etc.
Above screen shot show we have task configured for “Foreach file enumerator” which can be used to move\copy\delete file (of given file) in a given folder
Enumerator configuration:
Folder: Specify the folder path from where we need to process the files
Flies: File name (format) \ file extension
Fully qualified: File name along with location is returned .Eg: C:\Example.txt
Name and extension: The file name with its extension is returned.Eg: Example.txt
Name only: The file name without its extension is returned.Eg: Example
Variable Mapping
Result of the for loop will be assigned to variable and its value can be used further.
Foreach File Enumerator: Enumerates files in a folder
Foreach Item Enumeration: Enumerates items in a collection, such as the executables specified in an Execute Process task.
Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO recordset.
Foreach ADO.NET Schema Rowset Enumerator: Enumerates schema information about a data source.
Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as an array or ADO.NET DataTable.
Foreach NodeList Enumeration: Enumerates the result set of an XML Path Language (XPath) expression.
Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects (SMO) objects, such as a list of views in a database.
Friday, May 24, 2013
SSIS: Derived column : Conditional Operator + Hardcoding Null
Using Conditional Operator In SSIS:
«boolean_expression» ? «when_true» : «when_false»
To hardcode Null : NULL(DT_WSTR, 50)
Following is the expression to get the sub string and compare to "N" and return NULL if true
else the sub string string itself.
(DT_I4)((SUBSTRING(LTRIM(Answer),1,1) == "N") ? NULL(DT_WSTR, 50) : SUBSTRING(LTRIM(Answer),1,1))
Tuesday, May 21, 2013
SQL SERVER - Fragmentation - Detect Fragmentation and Eliminate Fragmentation
Wednesday, May 15, 2013
SqlCmd : Error Cannot generate SSPI co ntext
Got the following error while trying to connect to SqlCmd of local machine.
Error Cannot generate SSPI co ntext
Searching online for the solution figured out that it has to do something with the network.
Workaround: Disable the network and try running the same command it works like charm.
Looking for the exact issue and better solution. Feel free to comment.
Sql Server: Using Sqlcmd to export data to text file
http://www.sqlservercentral.com/Forums/Topic1452185-1550-1.aspx
Adviced him to run the following sqlcmd in the job..
SQLCMD -S YourSqlServer -d YourDatabase -U YourUserName -P YourPassword -Q "Your Query" -s "," -o "C:\YourFileName.txt"
Feel free to post better work around.
I understand SSIS could be another option.