SSIS Newbie - Need Help
-
Hi All,
I'll be transparent from the start here, I'm a VoIP guy and I have a requirement from my customer that requires checking a value in a table and files in a folder. The customer wants this in a SSIS package, no exceptions.
I really assumed that there would be a "IF" task in the toolbox, but I assumed wrong. All I'm trying to do is simply check if a particular query returns a result or not:
SELECT CallStatusZone1 FROM DL_5010_5010 WHERE CallStatusZone1 = 'P'
So if the result is P, to me it's true and I want the logic to go through some sort of true branch. If I don't get the result of 'P', I want to send the logic to another "IF" task that will check for the same query, except instead of 'P', it'll be 'C'. If I 'C' is returned, then I'm going to check a particular folder to see if a file exists.
At this point, I can't seem to get past the first part. I've spent several days banging my head against the wall watching youtube videos and reading tutorials - either I'm stupid or I just don't understand the SQL world
Everything I've tried has been utter failure
I'm on SQL 2008 R2
Thx
-
Am I right to assume that you've got BIDS open and have created a new Project and now you have a blank canvas similar to what is shown in the attachment?
Regards
Lempster
-
Its all about making your mind 1st.
if you are having problem writing sql ( as per you stated) you can do the following
IF (SELECT Count(*) FROM DL_5010_5010 WHERE CallStatusZone1 = 'P') > 0
Begin
---- Do you code here
END
ELSE IF (SELECT Count(*) FROM DL_5010_5010 WHERE CallStatusZone1 = 'C') > 0
Begin
---- Do you code here
END
ELSE
BEGIN
---- Do you code here
END
This will resolve your 1st part, how you can apply logic and thing.
Now next is you want to it in SSIS.
IF you have installed SQL SERVER 2008R2 then you can find SQL SERVER Business Intelligence Studio in the menu as well.
Try to open it , File -> Create Project -> Integration Services Project and Press 'OK'.
This will create the working environment for u. your biggest help to achieve what you looking for is 'Script Component' and your best friend to help you in this is Google.
hope it helps. 🙂
-
Thanks for the help Lempster and twin.devil... I think I finally have this at a point where it'll work for me without it being complicated. Here's what I did, and please correct me if I'm wrong....
I have four variables setup:
intCntPending, package scope, Int32 data type, 0 value
intCntClosed, package scope, Int32 data type, 0 value
bolFileExists, package scope, Boolean data type, False value
strFileLocation, package scope, String data type, \\<hostname>\campaigns\Dental\Import.csv
I created a new OLE DB connection that's connecting to a completely different SQL Server
I've set an Execute SQL Task control flow item in the control flow tab:
GENERAL TAB:
ResultSet: Single row
ConnectionType: OLE DB
Connection: <my OLD DB connection>
SQLSourceType: Direct input
SQLStatement: SELECT COUNT(*) AS intCntPending FROM DL_5010_5010 WHERE CallStatusZone1 = 'P'
RESULT SET TAB:
Result Name: intCntPending
Variable Name: User::intCntPending
From the Execute SQL Task, I have two Precedence Constraints
The first constraint:
Evaluation Operation: Expression
Expression:@intCntPending>0
Under Multiple constraints, I have Logical AND. All constraints must evaluate to True
the second constraint:
Evaluation Operation: Expression
Expression:@intCntPending<=0
Under Multiple constraints, I have Logical AND. All constraints must evaluate to True
So basically, if intCntPending is greater than 0, I'm sending it off to a Script Task. If intCntPending is less than or equal to zero, I'm sending it off to the next Execute SQL Task.
I've set an Execute SQL Task control flow item in the control flow tab:
GENERAL TAB:
ResultSet: Single row
ConnectionType: OLE DB
Connection: <my OLD DB connection>
SQLSourceType: Direct input
SQLStatement: SELECT COUNT(*) AS intCntClosed FROM DL_5010_5010 WHERE CallStatusZone1 = 'P'
RESULT SET TAB:
Result Name: intCntClosed
Variable Name: User::intCntClosed
From the Execute SQL Task, I have two Precedence Constraints
The first constraint:
Evaluation Operation: Expression
Expression:@intCntClosed>0
Under Multiple constraints, I have Logical AND. All constraints must evaluate to True
the second constraint:
Evaluation Operation: Expression
Expression:@intCntClosed<=0
Under Multiple constraints, I have Logical AND. All constraints must evaluate to True
If intCntClosed is less than or equal to zero, I'm sending the logic to a Send Mail Task. If intCntClosed is greater than zero, I'm sending it to a Scrip Task that will check to see if a CSV file exists in a folder.
-
Is there a way to send the True and False of a condition to a task? When I try another connector, I get "Only one workflow can exist between the same two executables"
-
Wow! You've certainly made some progress! I was going to take things step by step hence my initial reply, but you've done things pretty much the way I would.
As to your latest question: no, not in the way that you want, but you can set the precedence constraint between tasks to 'Completed' rather than 'Success' or 'Failure' - might be of use in certain circumstances. In a Data Flow task you can pass 'true' and 'false' down the same path in some sense, for example by using a Lookup task and ignoring lookup failures.
Regards
Lempster
-
Great to see that you made a working SSIS package with all the logic you have in your mind. 🙂
I think its a typo but need to highlighted,
I've set an Execute SQL Task control flow item in the control flow tab:
GENERAL TAB:
ResultSet: Single row
ConnectionType: OLE DB
Connection: <my OLD DB connection>
SQLSourceType: Direct input
SQLStatement: SELECT COUNT(*) AS intCntClosed FROM DL_5010_5010 WHERE CallStatusZone1 = 'P'
It should be 'C' not 'P'.
SQLStatement: SELECT COUNT(*) AS intCntClosed FROM DL_5010_5010 WHERE CallStatusZone1 = 'C'
you can further refine your package by implement the Pending & closed select result along with their contains in a single Script Component. See the attachment. it contains the select statements and a variable used for flag.
As the select statements will be in a script component to do not need to create extra script component to constrains validation and other stuff you have implemented. (just a thought :-D)
(once downloaded change the extension for txt to dtsx :-))
hope it helps.
-
Thanks for the help, twin.devil, I'm definitely saving it for other packages that I'll be doing.
Through much trial and error and lots of YouTube watching and tutorial reading, I've completed my SSIS package and it works really well. I do have a question though about SQL/SSIS in general. I'm not real sure if I'm even asking this correctly....
Is there any way that I can make my statement dynamic? What I mean by that is, can I make this:
SELECT * FROM DL_5010_5010
into this:
SELECT * FROM $1
Instead of explicitly defining the table, is there a way to feed that information from an external source like a text file or powershell script? I ask because the SSIS package I created explicitly calls on tables and I have all my tasks in a container. I've instructed the customer that if they want to add another business unit, which gets its own table, they will need to copy/paste the container and make a flurry of changes. It would be nice to have one container that holds all the queries but external source that fills in the blanks of the FROM clause. An external source feeding this would easily manageable and editable by someone who has minimal technical skill sets.
Thanks!
-
you can definitely can do
select * from Table1to
select * from AnothTableyou can achieve this through Variable of Type string.
Let say you define a variable in SSIS name "varTableName" and default value for this table will be "DL_5010_5010"
Open BIDS, Drag Drop a "Execute SQL Task", Double click it. Select the Expression, Click the ellipse button "Property Expressions Editiors" window will be open. in the Property column select "SQLStatementSource" and on the right column click the Ellipse button.
In the express section you can right following
"select * from" + @[User::varTableName]
Press Evaluate Expression button, if no error shown , Evaluated value section will show you the value.
once done, click OK.
you can set the value "@[User::varTableName]" at any time 🙂
This will also help you
http://www.sqlsafety.com/?p=266
Hope it helps
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply