dts dynamic query & automap columns question

Hello All,

I am sorry if this has been asked - I tried searching but the search kept timing out.

I have designed a DTS package which extracts a query into an excel file.

It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

Currently, the DTS package errors on each attempt because it expects a certain column set.

Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

I would hate to have to set up a different DTS package for each possible column set.

I am sure I am missing something.

Thanks in advance.

- CharlesI would use a sproc with dynamic sql, bcp and xp_cmdshell personally|||Hi Brett,

Thanks for the response. I have to admit I'm fairly new to the more complex aspects of SQLServer (Stored Procedures, cmd_shell, etc). What I have so far was built using the Package Designer - to give you an idea of my level of expertise with DTS. But I'm not opposed to learning if that's what it takes.

Are you aware of any other "simpler" options?|||DTS is such a flukey thing...see it's a dangerous drug...you get used to the GUI, the you start to push it...which means you need to start using ActiveX and such.

What happens then is that DTS acts like a cursor and has to affect rows, one by one. Which slows things down dramatically.

If you could give me/us and example of what you are trying to do, I'm sure we can get you an elegant/effecient solution.

Read the sticky at the top of this thread (ok second sticky) and post what it asks for.|||lol Brett - you have described my addiction exactly. It all started so innocently...

I will try as best as possible to meet your requirements.

The Question: How can I get the result set from an ad-hoc query to export to any format supported by DTS?

This question does not deal with any specific tables, data or queries. I will use examples.

User Interface:
A web-based interface allows a user to create a report from a number of factors, including choosing which fields should display. This allows for variations in the number of fields being generated. The user may also choose from a number of formats.

The web application then dynamically assembles the query based on the user's selections.

Example Query A (5 columns / fields in record-set):
select name, address, state, zip_code, phone_number
from clients
where zip_code like '06%'

Example Query B (8 columns / fields in record-set):
select name, address, state, zip_code, phone_number, billing_number, last_invoice_date, billing_cycle
from clients
where zip_code like '06%'

In the DTS Package:
1) Connection 1 is set to work from a query. The query is set using the Dynamic Properties Task. It could use Query A one time, and Query B another.

2) Connection 2 is set as the output file type. For our purposes it will be xls.

3) The transformation between Connection 1 and Connection 2 exports the query results into the xls file.

The Error:
When Query A is exported, the DTS has 5 columns to map. When Query B is exported, it has 8 columns to map. The one DTS package errors out if the number of columns are different than it expects to find. It is not possible to limit users to a specific number of columns.|||How about as a cheap solution for the 5 column solution, just return empty strings for the last three columns, and that way all of the calls will return 8 columns, but in Excel, it will show as 5.

Will that work for you?|||OK, this is what I would do...

#1. Make sure all access to the database is done through strored procedures
#2. Dynamically build a view based on your users requirements. make it so it's a single column concatenated as a tab or comma delimited and you can even add a headr
#3. bcp out the view with xp_cmdshell

Let me work up a sample|||Wow Brett! Thanks, I'm intruiged. I can't wait to see it.

Thanks again.

- Charles|||What form do you collect the requirements in? Do formulate the query in the front end? Sounds like you do...|||Hi Brett,

You are correct the query if built first in ColdFusion. The user interface is a form through which the fields and associations are made.

The query is created, then saved in a sql table, then loaded as a dynamic property at runtime.

Thanks again,

- Charles

DTS Dynamic Properties task to SSIS

Hi ,

What is the equivalent of DTS Dynamic Properties task into SSIS ?

How do I convert this task to SSIS ?

Thanks,

Vikas

But it used to work in DTS (2) - Changing connections using the Dynamic Properties Task
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4157.aspx)

There is loads of information out there about this if you bother to look: http://www.google.com/search?hl=en&q=ssis+dynamic+properties+task

-Jamie

sql

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

DTS - With Conditions

Hi All,
I have DTS which uses global variables and dynamic mapping to map to the
variables as per the parameters that i send from the Stored procedure that
used to call / execute the DTS.
My DTS has to export data from a SQL Server (Join Based Query) to a TARGET
.DBF file.
problem 1: How do I create the target .dbf file in the specied folder if
that does not exists? I pass the target folder name and dtf file name from
Stored Procedure and that has to be mapped for the dBase Connection.
problem 2: If the Query (that will get executed before the dbf file will get
create) does not return any rows then I dont want to create the dbf file. My
DTS should stop there by logging some message in log (if this is possible?)
Please suggest.
Thanks
PrabhatHi Prabhat,
Please try to use Import-Export whizard tool available in SQL Server. This
is a whizard to program your DTS aswell.
You can then save the steps as a DTS package
hope this gives a hint on how to go about
thanks and regards
Chandra
"Prabhat" wrote:

> Hi All,
> I have DTS which uses global variables and dynamic mapping to map to the
> variables as per the parameters that i send from the Stored procedure that
> used to call / execute the DTS.
> My DTS has to export data from a SQL Server (Join Based Query) to a TARGET
> ..DBF file.
> problem 1: How do I create the target .dbf file in the specied folder if
> that does not exists? I pass the target folder name and dtf file name from
> Stored Procedure and that has to be mapped for the dBase Connection.
> problem 2: If the Query (that will get executed before the dbf file will g
et
> create) does not return any rows then I dont want to create the dbf file.
My
> DTS should stop there by logging some message in log (if this is possible?
)
> Please suggest.
> Thanks
> Prabhat
>
>|||Hi Chandra,
Thanks for the hint. But I believe that In the Import / Export we can start
a Wizard if the file does not exist.
Thanks
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:31D82797-6A69-4ECF-AE96-3845AC1FA694@.microsoft.com...
> Hi Prabhat,
> Please try to use Import-Export whizard tool available in SQL Server. This
> is a whizard to program your DTS aswell.
> You can then save the steps as a DTS package
> hope this gives a hint on how to go about
> thanks and regards
> Chandra
>
> "Prabhat" wrote:
>
that
TARGET
from
get
file. My
possible?)|||If the file already exists, u can over write the file
"Prabhat" wrote:

> Hi Chandra,
> Thanks for the hint. But I believe that In the Import / Export we can star
t
> a Wizard if the file does not exist.
> Thanks
> Prabhat
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:31D82797-6A69-4ECF-AE96-3845AC1FA694@.microsoft.com...
> that
> TARGET
> from
> get
> file. My
> possible?)
>
>|||That is not a problem. But how Do I start the wizard if the file does not
exists?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B9016AB0-D40E-4EAF-A5A9-66D3326F267A@.microsoft.com...
> If the file already exists, u can over write the file
> "Prabhat" wrote:
>
start
This
the
procedure
folder if
name
will