SSIS Noob - Script component - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Noob - Script component

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-22 : 05:38:38

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

52326 Posts

Posted - 2013-10-22 : 05:58:52

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-22 : 06:04:30

OK - Thanks.

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

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 08:11:11

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-22 : 08:35:50

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 08:40:30

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-23 : 04:37:22

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 08:47:00

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-23 : 09:17:46

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 14:06:23

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-24 : 04:53:36

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-24 : 05:08:02

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-24 : 05:37:26

Hi,

The derived column component worked great.

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 12:20:29

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

yonabout
Posting Yak Master

112 Posts

Posted - 2013-10-25 : 05:19:36

OK,

Makes sense.

Thanks a lot for all your help.

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:44:50

you're welcome
glad that I could help you out

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources