Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Help


SSIS Help

Author
Message
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
Hi Guys,

Here is my question.
I am creating a Flat File through SSIS, Preaty simple package
Source = Excel
Transformation = Using some transformation
Destination = Flat File

Here I want Header and Footer should be Total Numbers of Records + GET DATE + *******************************************************

I know How I can get Total Number of records through Variable and use Count Transformation. My question is how I can || (Concatenate) @NumbersOfRecord||GETDATE()||*********************************

Please advise.

Thank You,
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 11590
There's no simple way of doing this in the dataflow.

One way I might do this is to create the basic flatfile as you seem to know how to do, without the header and footer.

I would then use a script task to create a new file in which I would insert the Header, followed by the content of the file mentioned above, followed by the footer. The original flatfile can now be deleted.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16558 Visits: 13210
Alvin Ramard (8/26/2014)

I would then use a script task to create a new file in which I would insert the Header, followed by the content of the file mentioned above, followed by the footer. The original flatfile can now be deleted.


Or just modify the existing flat file.
There are plenty of scripts on the web that show you how to add a line to the start or end of a flat file.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
Thank You all for reply.

I am sorry, I think it was my mistake didn't explain you what I need.

I know how to create Flat file Plus
I know how to add Custom header and footer on flat file.

My question was, My customer Header and footer was Total Count.I am getting this value through Variable. I want to know How I can add with Variable as an example

Total Count:- 00000026 8/27/2014 ****************************************************************** (This should be my header)

Flat file Data...........
............................

Total Count:- 00000026 8/27/2014 ****************************************************************** (This should be my footer)

Thank You.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16558 Visits: 13210
The same solution still stands: you write your data to the flat file. Meanwhile, you store the row count in a variable.
In the next step, you add the header and footer using a script task that reads the SSIS variable.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rxm119528
rxm119528
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 804
First In your SSIS package you are going to save you row count into variable (for example vartotal).

Then when you open the script task editor, in the script section you see four items

1)Script Langauge
2)Entry Point
3)ReadOnlyVariables
4)ReadWriteVariables

In the readonlyvariables you need to select the variable vartotal

In the next step when you click on the Edit Script, you are going to use this varaiable with the code

string count = Dts.Variables["vartotal"].Value.ToString()
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
Thank you all again,

I get that far, the problem I am getting in Script Task I can easy add My Total count variable value and its working fine. I want to know How i can add with Count Variable + Getdate + ************************

so my header and footer would be

Count Varaible + Getdate + *************************

Any advise.

Thank You.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16558 Visits: 13210
DateTime.Now Property



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
nishav2
nishav2
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 103
Hi,

In C: drive i have many folders .
For example folders named like ABC,ARD,AVB,BGT,BHT etc...And these folders has many subfolders and files in it.
My Task is to search for a string (For example : TASC) in all these folders and sub folders.
I have a for loop container and a script task to do this.But since the data that is found under C: is huge the package runs for more than 1 day.So i thought of splitting this task and do a parallel execution.
I.e.; I can replicate this Forloop container task 23 times(A-Z) and run it parallely.That is one container will look for the main folders(ABC,ARD,AVB) starting with A and another container task will look for
main folders starting with B (BGT,BHT) .similarly i will replicate this container task .All these containers will execute parallely when i run the package.This will take only few hours to complete. i need to achieve this.Can someone help me on this ?

Any help is much appreciated.

Thanks
Nisha V Krishnan
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16558 Visits: 13210
nishav2 (9/4/2014)
Hi,

In C: drive i have many folders .
For example folders named like ABC,ARD,AVB,BGT,BHT etc...And these folders has many subfolders and files in it.
My Task is to search for a string (For example : TASC) in all these folders and sub folders.
I have a for loop container and a script task to do this.But since the data that is found under C: is huge the package runs for more than 1 day.So i thought of splitting this task and do a parallel execution.
I.e.; I can replicate this Forloop container task 23 times(A-Z) and run it parallely.That is one container will look for the main folders(ABC,ARD,AVB) starting with A and another container task will look for
main folders starting with B (BGT,BHT) .similarly i will replicate this container task .All these containers will execute parallely when i run the package.This will take only few hours to complete. i need to achieve this.Can someone help me on this ?

Any help is much appreciated.

Thanks
Nisha V Krishnan


Please start a new thread with your question, instead of hijacking an existing one.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search