SSIS -Multiple queries - 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 -Multiple queries

Author

Topic

pknmca05
Starting Member

9 Posts

Posted - 2011-07-20 : 04:48:19

Hi,

we want 3 queries out put in one flat file destination (i.e. text file) in fixed width, below are 3 queries

1. SET NOCOUNT ON;
SET @Table_Name ='Applications'
SET @dt_stamp=replace(convert(varchar, getdate(),111),'/','') + replace(convert(varchar, getdate(),108),':','')
SET @File_Name=@Table_Name+'_'+@dt_stamp+'.txt'
SET @Header='HDR'+@dt_stamp+@Table_Name
SELECT @Header AS Header --- these one is header query , only 1 column 1 row will be generated

2. SELECT * FROM from Applications –---MULTIPLE ROWS with multiple columns will be generated
3. SELECT 'TLR'+right('00000000'+convert(varchar,count(*)),8) as TLR from Applications
--- these one is TRAILER query , only 1 column 1 row will be generated

Thanks and Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 07:51:40

use UNION ALL to combine them and then use it as source for data flow task with flat file destination

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pknmca05
Starting Member

9 Posts

Posted - 2011-07-20 : 11:50:17

Thanks visakh ,
used the option of UNION ALL , but in flat file the output is coming as follows i.e. in text file

HDR20110718112053APPLICATIONS
2011-06-29 13:19:27.837000000 0 (coming as right allign in text file
2011-06-29 14:45:57.777000000 0
TLR002

But the expected out put is as below

HDR20110718112053APPLICATIONS
2011-06-29 13:19:27.837000000 0
2011-06-29 14:45:57.777000000 0
TLR002

How to achive the above output ?

Thanks and Regards

pknmca05
Starting Member

9 Posts

Posted - 2011-07-20 : 11:52:29

means after the HDR20110718112053APPLICATIONS in next row date is starting from from word S of APPLICATION , i think it is not coming properly here in above window.

pknmca05
Starting Member

9 Posts

Posted - 2011-07-20 : 13:08:10

but i think ie. due to InputColumnWidth of file conection manager property , is anyway to change in run time to 1 or 0 , if values are nothing i.e. empty

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 02:21:31

you can use NULLIF to change empty values to NULL and then use ISNULL to convert it to 0

like

ISNULL(NULLIF(field,''),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pknmca05
Starting Member

9 Posts

Posted - 2011-07-21 : 05:10:27

Thanks Visakh ,

InputColumnWidth of file conection manager property is fixed type , so taking defined length of 30 , then from next row it is taking the InputColumnWidth of header row and then the date ,

so there is any way we defined the InputColumnWidth in run time ? , so in next next row it will become (InputColumnWidth ) will became 0 from header informartion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 05:19:55

nope. you cant change it run time. will your row be alwys of varying length?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pknmca05
Starting Member

9 Posts

Posted - 2011-07-21 : 05:46:18

HDR20110718112053APPLICATIONS

2011-06-29 13:19:27.837000000 0

2011-06-29 14:45:57.777000000 0

TLR002

but we want output as below , these may be fixed width i.e causing an issue

HDR20110718112053APPLICATIONS
2011-06-29 13:19:27.837000000 0
2011-06-29 14:45:57.777000000 0
TLR002

how to overcome these now , any other easy solutions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 06:03:08

is it all space which is filling the space? if yes you could use LTRIM and RTRIM to trim them out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pknmca05
Starting Member

9 Posts

Posted - 2011-07-21 : 06:25:11

there are 300 columns , and first column is datetime ,so trim will not work ,
any other easy way round to work out with ,

pknmca05
Starting Member

9 Posts

Posted - 2011-07-21 : 07:05:00

also TRIM will not work , due to InputColumnWidth already assigned with 30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources