SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS Noob - Script component
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/22/2013 :  05:38:38  Show Profile  Reply with Quote
Hi,

I'm trying to write an SSIS package.

I've got a transformation step in the middle which needs to check a date from the source, work out what day of the week it is and then decide whether or not to proceed with the transformation. I've been poking around t'interweb looking for examples relevant to me for a while but no joy...

So...

a) In the vb.net script editor, how do I reference the source data columns

b) Is there an alternative way to do it - maybe by using the control flow just to check the date from the source, then doing a data transform if it passes the control.

Any help / pointers to relevant documentation would be great.

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/22/2013 :  05:58:52  Show Profile  Reply with Quote
a, you can reference them as Row.FieldName
b, Based on your explanation I feel like same can be done using conditional task transform. You can get day of week using DATEPART function and you can add a condition based on it in task and define output for it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/22/2013 :  06:04:30  Show Profile  Reply with Quote
OK - Thanks.

Where would I do a conditional task transform? Is that in the control flow or the data flow?

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/22/2013 :  08:11:11  Show Profile  Reply with Quote
quote:
Originally posted by yonabout

OK - Thanks.

Where would I do a conditional task transform? Is that in the control flow or the data flow?

Cheers,

Yonabout


conditional task is a data flow component'
So you'll replace your current script transform with it and add a new output inside it based on expression which checks for day of the week.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/22/2013 :  08:35:50  Show Profile  Reply with Quote
Err

I can't find a 'conditional task' in the toolbox - the closest by name is a 'Conditional Split'. Am I looking in the wrong place?

When I dragged the script component on it asked me what I wanted to do , so I picked transform data and set up the inputs and outputs to use so I can reference these in the script. I'm happy to proceed that way now, but it's always good to know if there's an alternative.

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/22/2013 :  08:40:30  Show Profile  Reply with Quote
quote:
Originally posted by yonabout

Err

I can't find a 'conditional task' in the toolbox - the closest by name is a 'Conditional Split'. Am I looking in the wrong place?

When I dragged the script component on it asked me what I wanted to do , so I picked transform data and set up the inputs and outputs to use so I can reference these in the script. I'm happy to proceed that way now, but it's always good to know if there's an alternative.

Cheers,

Yonabout


Nope I meant conditional split.
Just couldnt get exact name over top of my head

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/23/2013 :  04:37:22  Show Profile  Reply with Quote
Hi,

Had a go both ways - I've gone with the script because I found I had to use it anyway so it made sense to do it all in one place.

Thanks for your help

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/23/2013 :  08:47:00  Show Profile  Reply with Quote
quote:
Originally posted by yonabout

Hi,

Had a go both ways - I've gone with the script because I found I had to use it anyway so it made sense to do it all in one place.

Thanks for your help

Cheers,

Yonabout


Why you had to use it inside data flow? can you tell the scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/23/2013 :  09:17:46  Show Profile  Reply with Quote
OK:

Flat file (csv) arrives on a server daily

scheduled SSIS needs to run daily to take the data from the file and import it into a database table

The data in the csv file relates to the previous working day - so mid way through the import, I need to check to say:
Is today monday?
True: set varinteger to -3 (last friday)
false: set varinteger to -1 (yesterday)

if the date on each incoming data row = dateadd(dd, varinteger, today)

true: write data
false: ignore

repeat.


It made sense to me to do this as a script - what are your thoughts?

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/23/2013 :  14:06:23  Show Profile  Reply with Quote
I would have created a SSIS variable to store varinteger value and made EvaluateAsExpression property true for it. Then set an expression like

((DATEDIFF("dw",(DT_DBDATE)"1900-01-01",GETDATE()) % 7)== 0 ? -3 : -1 )
for setting the value
Once this is done add an output under conditional split and define condition as

Column == (DT_DBDATE) DATEADD("dd",@[User::varInteger],GETDATE())

then link your output to next task


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 10/23/2013 14:24:10
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/24/2013 :  04:53:36  Show Profile  Reply with Quote
First off, thanks for your help with this. It's turning into a slightly steeper learning curve than I'd anticipated.

It looks like the conditional flow is working, but I'm not 100% sure how. Would you be able to break the expression down for me a bit? Specifically:

Is the "1900-01-01" just a placeholder to get the expression to work, and to be replaced by the date from the file?

And what is the significance of the
0 ? -3 : -1 )
bit? Are they the default results of the expression?

Finally, once the flow has made it past the conditional split, I'd like to manipulate some of the fields before they get posted to the database table. For instance, pad out an 'account number' field to 10 characters long with some leading zeros if it's too short. What component / technique would you recommend for this? I've been looking through the available data flow components, but I keep coming back to the script component so I can take the fields in, manipulate them, then spit them out.

What would you recommend?

Cheers,

Yonabout
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/24/2013 :  05:08:02  Show Profile  Reply with Quote
Re the field transformations, I'm going to play about with the 'derived column' component. If I don't get any joy there I'll go with the script

If you could help me understand how the variable evaluates as per my last post that would be really cool.

Cheers,

Yonabout
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/24/2013 :  05:37:26  Show Profile  Reply with Quote
Hi,

The derived column component worked great.

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/24/2013 :  12:20:29  Show Profile  Reply with Quote
quote:
Originally posted by yonabout

First off, thanks for your help with this. It's turning into a slightly steeper learning curve than I'd anticipated.

It looks like the conditional flow is working, but I'm not 100% sure how. Would you be able to break the expression down for me a bit? Specifically:

Is the "1900-01-01" just a placeholder to get the expression to work, and to be replaced by the date from the file?

And what is the significance of the
0 ? -3 : -1 )
bit? Are they the default results of the expression?

Finally, once the flow has made it past the conditional split, I'd like to manipulate some of the fields before they get posted to the database table. For instance, pad out an 'account number' field to 10 characters long with some leading zeros if it's too short. What component / technique would you recommend for this? I've been looking through the available data flow components, but I keep coming back to the script component so I can take the fields in, manipulate them, then spit them out.

What would you recommend?

Cheers,

Yonabout


1900-01-01 is base date in sql server and its a Monday
the logic checks if day falls to be Monday. If its monday it will return -3 otherwise -1
conditional operator (Expression? TrueValue: False Value) evaluates condition and return either true or false values based on condition result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

yonabout
Posting Yak Master

United Kingdom
112 Posts

Posted - 10/25/2013 :  05:19:36  Show Profile  Reply with Quote
OK,

Makes sense.

Thanks a lot for all your help.

Cheers,

Yonabout
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2013 :  08:44:50  Show Profile  Reply with Quote
you're welcome
glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000