SSIS Question(s)

[gillrowley]

Very new to SSIS, still sorting out how it works. Transfering data from an AS400 to SQL 2005. 2 questions, please:
1) In the Data Flow section, what would be the easiest way to convert an 8 digit number from the AS400 (yyyymmdd) to a date format in the SQL table?

2) I have an extra field in the SQL table that is not in the AS400 table - I currently populate that field in a SQL 2000 DTS package using the scripting cabability in the Trasnformation. Is there a similar way to do this in the data flow? I'm having trouble finding info on that.

Thanks.
Gill

 

[mbde]

0) Disclaimer I do not know AS400.

1) When I need to convert information whether it be format or type I try to do it in the query, even with a text file you should be able to write a query against it with an ADO provider

So Select Convert(DateTime, Right(field, 4) + '-' + Substring(5,2) + '-' + Substring(7,2)) or something like that

2)Yes so coming out of your dataflow you can add a scripting component. Right click and choose Show Advance Editor. Go to the Input Output Properties tab. Expand Output 0, highlight Output Columns and click Add Column (button at the bottom). Choose a name and a type.
Now get out of there, double click on your script component. Choose script from the left side, then click the button Design Script. If I described this correctly and you understood it, your new column should show up in the function when you access the Row. variable. It will also show up in subsequent transactions.

http://www.sqlis.com/

is a good resource.

 

[gillrowley]

Thanks - still haven't figured it out but I'm getting closer - you've pointed me in the right direction. I wasn't aware of the Scripting task that can be in between the source and the destination.