I want to export to a Text File (destination) from a SQL query using a DTS DataPump Task. My query has 28 columns, some how when I try to define the destination columns for my text file the Microsoft Managment Console Crashes completely.
I tough may be my query has two many columns only to find out that it has one to many. If I ramove a column form my query, any column. I get no error at all.
Is there a limit to a Text File destination connection?I got the same problem. I cannot imagine it has anything to do with nr of columns.
Would be interested in solution...
dajm|||I do believe there is a limit to the number of columns but it can't be 28. Still since there's no error besides the crash I have not figured it out yet. Im going to try some test querys.|||I know this isn't what you asked, but I always use bcp for this. Are you doing complex transformations in the data pump? If it is just SQL, you can go to a command line (execute process task if you want DTS to do it) and run bcp "your query" queryout destination.txt -c -T -t "|" (for pipe-delimited). If it is an entire table (or view), you can run bcp "tablename" out destination.txt -c -T -t"|"
Showing posts with label datapump. Show all posts
Showing posts with label datapump. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
DTS DataPump from a Stored Procedure
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.
Paulo
Originally posted by andrewsc
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.
Paulo
Originally posted by andrewsc
Hi,
assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?
The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )
At the SQL Query box, I have entered: exec my_proc
The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.
I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?
Monday, March 19, 2012
DTS and Lotus Notes
I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.You do know that a lotus notes "database" isa not a database. And since your working with a database, you must have some Lotus Notes "guru" who is pushing for this, why not let them use notrix or pump to get the data.
Deliver a comma delimted file to a location everyday...what? they want real time updates?
How do you do that to a non relational architecture?|||Originally posted by sch5479
I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.
Why not have the Lotus Notes people setup the DESC sub-system within Lotus Domino and have the system directly access the information wither live or timed?
Frank|||While Notes Databases arn't relational don't see why that DTS has a problem as you can get Crystal report to run against notes data. Personally I wouldn't touch Notrix my preference has always been to get the Domino application to access the data it needs using LotusScript and ODBC which just makes SQL queries to the data.
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.You do know that a lotus notes "database" isa not a database. And since your working with a database, you must have some Lotus Notes "guru" who is pushing for this, why not let them use notrix or pump to get the data.
Deliver a comma delimted file to a location everyday...what? they want real time updates?
How do you do that to a non relational architecture?|||Originally posted by sch5479
I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.
Why not have the Lotus Notes people setup the DESC sub-system within Lotus Domino and have the system directly access the information wither live or timed?
Frank|||While Notes Databases arn't relational don't see why that DTS has a problem as you can get Crystal report to run against notes data. Personally I wouldn't touch Notrix my preference has always been to get the Domino application to access the data it needs using LotusScript and ODBC which just makes SQL queries to the data.
Subscribe to:
Comments (Atom)