Posts

Showing posts from July, 2019

C# - Extract objects from SQL Command

Problem: How can I extract all objects mentioned in a SQL Command using C#? Solution: It is not easy to extract object names from an SQL command since they may be written in different ways  (with/without schema , databases name included ...) But there are many option to extract objects from an SQL query that you can try: Using Regular expressions, As example: You have to search for the words located after the following keywords: TRUNCATE TABLE FROM UPDATE JOIN The following code is a C# example: Regex regex = new Regex ( @"\bJOIN\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bFROM\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bUPDATE\s+(?<Update>[a-zA-Z\._\d]+)\b|\bINSERT\s+(?:\bINTO\b)?\s+(?<Insert>[a-zA-Z\._\d]+)\b|\bTRUNCATE\s+TABLE\s+(?<Delete>[a-zA-Z\._\d]+)\b|\bDELETE\s+(?:\bFROM\b)?\s+(?<Delete>[a-zA-Z\._\d]+)\b" ); var obj = regex . Matches ( sql ); foreach ( Match m in obj ) { Console . WriteLine ( m . ToStri

SSIS - Missing SSIS toolbox in Visual Studio

Image
Problem: I am running SQL Server 2014 on my machine. I have installed SSDT for Visual Studio 2017, which I also have. Now I am not able to find the SSIS Toolbox in my Visual Studio. Do I need to re-install the SSDT all over again? Please let me know. Thanks in advance. Solution: If SQL Server Data Tools are installed correctly , you can show SSIS Toolbox in 3 ways: (1) Visual Studio Menu strip Based on this  Microsoft article : If you can't see the toolbox, go to VIEW -> Other Windows -> SSIS Toolbox. (2) From Context Menu strip Or just Right click inside the Control Flow of any parent/child package and select 'SSIS toolbox', then the SSIS toolbox will be displayed just after that click.  Read more (3) Near SSIS tab pages You can find a small button near the SSIS tab pages as shown in the following image: Original post:  https://stackoverflow.com/questions/48102365/missing-ssis-toolbox-in-visual-studio-2017/48102622#48102622

SSIS - Remove time part from a date time value

Image
Problem: How to remove from DateTime variable hours, minutes, seconds and other parts in SSIS If I have DateTime like  21 jul 2019 8:30:05 , and I want have it as  21 jul 2019 00:00:00  and still as DateTime not string Solution: You can achieve that using a derived column: ( DT_DBTIMESTAMP )( DT_DBDATE )@[ User :: DateTimeVariable ] Casting to  DT_DBDATE  will remove the time part, then recasting to  DT_DBTIMESTAMP  will re-add a time part but with  12:00 AM  value =  00:00:00 Example: Original post:  https://stackoverflow.com/questions/57221410/how-to-remove-from-datetime-variable-hours-minutes-seconds-and-other-parts-in/57221736#57221736

SSIS - Convert a variable value to expression

Image
Problem: I have a SSIS package where I am running a SQL execution task to get some records, Then I am running a foreach loop iterating over ADO object returned and setting few variables values. These variables are used for data flow task queries. I need to set expression for the variable. In rows I have value something similar to  "foo" + @[User::Bar] + "baz"  (I set it using this expression directly, it works. I want to set it from sql output) But when I get this value from SQL task to Variable expression, SSIS is escaping the quotes, I do not want to escape these for my task to work. Saved Value : "foo" + @[User::Bar] + "baz" Variable set : \"foo\" + @[User::Bar] + \"baz\" Can anyone help me with this. I want expression as I have saved, due to this forceful escaping, my query is not getting set properly. Solution: 1st approach - Removing escape character using SSIS expression (This approach will only