Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

Package Hangs With Transations On

When I set the transaction option to required my package just hangs when I try to execute it. The status bar says "Validating" and then the name of the first destination data flow component (whatever that happens to be). I've let it sit for long periods and nothing happens. Any suggestions?

It's quite possible that the destination is trying to join the transaction (if enabled by you) and things start going wrong in there...

Can you tell us a little more about the components inside your package? Which ones are joining the transaction and what are their transaction properties set to?

sql

Package configurations stored in the database

If the configurations are stored in the database then does the package pick up the configuration as and when required or it a one time pick when the package is executed.

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

Thanks for your time.

$wapnil

They are a one time pick up at the beginning of execution. Look at the "Execution Results" of one of your packages.|||

How to look at the execution results?

Thanks,

$wapnil

|||

spattewar wrote:

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

You will not be able to change the table content within the same package that will use it, becasue what Phil has just said.

But, why would you want to change it that way....actually where does that script will get the values from?

Have you looked to the Dtexec SET option to assign values to the package properties instead?

|||

spattewar wrote:

How to look at the execution results?

Thanks,

$wapnil

It's a tab in Visual Studio.|||

I think I got it all wrong. Let me try to explain

I have developed a single package and have stored the configuration details in the database in the table SSIS configurations. Now this package does the task of FTP connect, download files and upload into a destination table in the database. Now I want this package

1) to be able to connect to different FTP servers in a sequence one after the other. The FTP connection details are stored in one more table FTP_Details in the database.

2) OR be able to run multiple instance of this package at the same time, each connecting to different FTP servers and downloading files.

Could you kindly provide your inputs.

Thanks for your responses.

$wapnil

|||

A simple way may be to have a master package with a ForEach loop container and an Execute package task inside. The ForEach loop would iterate through the rows in FTP_Details table to get FTP connection details, and place it/them into a variable(s). Then the Child package will use Parent variable Package configuration to receive the proper connection details on each iteration.

Notice that this approach will not execute the package in parallel.

|||Rafael's got it covered, but in case you need more information (again, the forum search is your friend), here's a link on this very topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=729225&SiteID=1|||

Thanks Phil and Rafeal. I got the approach on how to do it now.

just one more question.

If I want to run multiple instances of this package at the same time then what should be the best approach.

again, thanks for your time.

$wapnil

|||You can't run the same package multiple times at once UNLESS you create a master package that calls the same package under an Execute Package task multiple times. Even then though, I'm not sure that's a good idea given that the packages will all share the same GUID (because they are the same!).

I don't understand why you'd want them to run at the same time, when kicking one package off in a foreach loop would be best. If you must run the same package multiple times then perhaps you'd be better off copying the package (physically) and then changing its GUID.

My only concern is how can you run the same package concurrently with another instance of itself without having one step on the other's toes?|||

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

|||

spattewar wrote:

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

Yes, calling dtexec would work just fine. If you want to make your FTP site list database driven, then no, it won't help. UNLESS, you passed in a key to dtexec or something, and that key matches up to a row in the database.

What we've been talking about here is how you can create one package which will loop through a table to setup the FTP connection to as many sites are listed in the table.

There are numerous ways to do this, of course.|||

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

|||

spattewar wrote:

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

Sounds okay to me, if that's what you want to do... I still question the need to run them at the same time.... If you're downloading gigs of data, I'm not sure that you'll get any faster results by running them concurrently versus serialized. The data pipe can only move so much data. So, with that said, you may want to look to build a more modular approach by using a database table to drive your connections.

However you decide, I think you've got enough information to make some progress.|||ofcourse I have. Thanks

Package configurations stored in the database

If the configurations are stored in the database then does the package pick up the configuration as and when required or it a one time pick when the package is executed.

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

Thanks for your time.

$wapnil

They are a one time pick up at the beginning of execution. Look at the "Execution Results" of one of your packages.|||

How to look at the execution results?

Thanks,

$wapnil

|||

spattewar wrote:

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

You will not be able to change the table content within the same package that will use it, becasue what Phil has just said.

But, why would you want to change it that way....actually where does that script will get the values from?

Have you looked to the Dtexec SET option to assign values to the package properties instead?

|||

spattewar wrote:

How to look at the execution results?

Thanks,

$wapnil

It's a tab in Visual Studio.|||

I think I got it all wrong. Let me try to explain

I have developed a single package and have stored the configuration details in the database in the table SSIS configurations. Now this package does the task of FTP connect, download files and upload into a destination table in the database. Now I want this package

1) to be able to connect to different FTP servers in a sequence one after the other. The FTP connection details are stored in one more table FTP_Details in the database.

2) OR be able to run multiple instance of this package at the same time, each connecting to different FTP servers and downloading files.

Could you kindly provide your inputs.

Thanks for your responses.

$wapnil

|||

A simple way may be to have a master package with a ForEach loop container and an Execute package task inside. The ForEach loop would iterate through the rows in FTP_Details table to get FTP connection details, and place it/them into a variable(s). Then the Child package will use Parent variable Package configuration to receive the proper connection details on each iteration.

Notice that this approach will not execute the package in parallel.

|||Rafael's got it covered, but in case you need more information (again, the forum search is your friend), here's a link on this very topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=729225&SiteID=1|||

Thanks Phil and Rafeal. I got the approach on how to do it now.

just one more question.

If I want to run multiple instances of this package at the same time then what should be the best approach.

again, thanks for your time.

$wapnil

|||You can't run the same package multiple times at once UNLESS you create a master package that calls the same package under an Execute Package task multiple times. Even then though, I'm not sure that's a good idea given that the packages will all share the same GUID (because they are the same!).

I don't understand why you'd want them to run at the same time, when kicking one package off in a foreach loop would be best. If you must run the same package multiple times then perhaps you'd be better off copying the package (physically) and then changing its GUID.

My only concern is how can you run the same package concurrently with another instance of itself without having one step on the other's toes?|||

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

|||

spattewar wrote:

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

Yes, calling dtexec would work just fine. If you want to make your FTP site list database driven, then no, it won't help. UNLESS, you passed in a key to dtexec or something, and that key matches up to a row in the database.

What we've been talking about here is how you can create one package which will loop through a table to setup the FTP connection to as many sites are listed in the table.

There are numerous ways to do this, of course.|||

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

|||

spattewar wrote:

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

Sounds okay to me, if that's what you want to do... I still question the need to run them at the same time.... If you're downloading gigs of data, I'm not sure that you'll get any faster results by running them concurrently versus serialized. The data pipe can only move so much data. So, with that said, you may want to look to build a more modular approach by using a database table to drive your connections.

However you decide, I think you've got enough information to make some progress.|||ofcourse I have. Thanks