EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

EXEC date problem

Hello all,
I have a standard stored procedure that contains the following code:

SET @.SQL = 'SELECT id, building, firstname, lastname, closed, assignto, vandalism, entered_by, phone, roomno, status, dateentered, date_closed AS DATECLOSED FROM requests '
SET @.SQL = @.SQL + 'WHERE building_no IN (' + @.BUILDINGLIST + ') AND STATUS_NO = ' + @.STATUS + ' '

Works fine, but my dates come back unformated i want to replace the above:

dateentered and date_closed

with

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED
CONVERT(varchar(30), date_closed, 101) AS DATECLOSED

but, when i do, i receive the following error:

ERROR CLOSE TO AS

Does anyone have any ideas? Thanks in advance.

Richard M.what's it look like when you've already replaced them?

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED

also, dateentered appears to be a field that doesn't exist.

Exclusive Join

I have a link table between a user table and a lookup that contains the following data

ID Role
-- --
92166 1
92166 11
92167 7
92167 11
92210 3
92210 7
92210 11
92211 7
92211 11
92212 7
92212 11
92213 7
92213 11

92213 3

The Link table stores whether a user has a combination of rolea. I need to be able to ask for any given user IE 92210 has the roles 3 and 7 any additional roles are ok but they must have all of the combinations I am asking for. The role combinations can be anywhere from 1 to 40. So in one case i have to ask for roles (3,7) and in another (1,7,11) The only way I can figure out how to do this is a dynamic self join or by using a cursor (yuck).

This is a legacy application and the requirement changed from roles being inclusive to exclusive and a table structure change is out of the question.

There must be a better way. Any help would be appreciated.

Thanks in advance

You could do something like joining to the passed array with using a split function which is wrote sometime ago:

CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END

This evaluates to:

DELCARE @.SomeValues VARCHAR(100)
SET @.SomeValues = '1,7,11'

SELECT 'Access Granted'
FROM SomeTable ST
INNER JOIN dbo.Split(@.SomeValues,',') S ON
ON S.SplitValue = ST.Role
WHERE ID = 95321
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Split(SomeValues,','))

This is untested and maybe need to be modified, try to use that in the case you don′t wanna use dynamic sql.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that put me on the right track and now have a udf that does the job

sql

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:

> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data
).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as wel
l
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:
> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as well
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>

Excel to MS SQL

Hai all,
I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

Thanks
NarayanaswamyYou should import your data as VARCHAR in a temporary table, and transform it in a second step.|||Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy|||Yeah, Excel is great, but not for storing data.

I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.|||Originally posted by Narayanaswamy
Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy

I did have the same problem with importing excel data to MSSQL.
After some fighting I just imported data to Access and then to MSSQL.
It was needed to do not often. :)

Excel to Crystal Report

I wish to create a crystal report from an Excel workbook that contains many sheets. Each sheet contains data regarding a one project each. All sheets are of the same format in displaying data. How do I create a crystal report from this workbook, so that the data in the crystal report is displayed as treeview of which the project data itself can be expanded and minimised? Finally this has to be installed in the client's computer. Should I create an addin? You experts out there, please suggest and help me.

ThanksPlease help. No gurus?|||Create a new Report
When selecting Datasource choose the option Access/Excel
Then Choose Database type as Excel
Then Choose name the Excel file
Do the normal procedure thereafter|||Thanks. I will try that.
Thanks|||Did the prior answer to your question help? It seems that you don't need to go from Crystal to Excel but to format a Crystal report to match an Excel spreadsheet. Is that correct?

I have a similar problem. I am trying to convert to Crystal Reports a very complicated Excel worksheet that integrates with a tool that directly pulls data from an Oracle db. With Excel, they "cherry-pick" which accounts they want on any particular row.

In Crystal, you have some control over placing groups and naming them. This approach may work (Group->specified order) but it would be (1) extremely tedious as there are hundreds of lines, (2) I may exceed its capacity, (3) it would be hard for client to support, and (4) it may not work!

Have you found anything that might help?

Thanks!

Excel Numbers format

Hi all i'm having an issues when i trying to do a report:

i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.

I would really appriciate your Help

Thanks

The value in the cell is inserted as a string, so filtering should work. The indicator is just Excel warning you that this string could be changed into a number. You can turn off this warning in the options settings of Excel.

Ian|||

Thanks Ian but the autofilter doesn't work if i do a greater than or less than filter.

But i have another field that only contains names and it does the filter by this criterio, i have try to put in the RDL CSTR() function but doesn't work. something similar happened to me with date fiels and i just addded a Cdate to the RDl field and added the mm/dd/yyyy format for that field and i got the autofilter.

I don't know what else i could try

Thanks

|||

You know how in excel you can prefix a value with a single quote, to force it to accept the input as text. You could try that in your report. The downside is that you'd see this in the web view but should not be visible in the export.

I played around in excel and if I prefix my values with a single quote then the excel filter only works if I also include the single quote in my filter expression.

|||
If you can provide an example of what you a trying to accomplish in the generated spreadsheet, I may be able to point you in the right direction. Using just Excel, can you give me an example of how to add a less than or greater than filter for text?

Ian|||sure i can give you an excel sample, but how can i attache the excel here ? or what address can i send it|||In a excel you can auto filter by greater than and less than by using the custom filtering, that appears when you click the selected column with the autofilter on and there it show custom, then it opens a dialog box where you can especify if you want the condition , Equal, greater than or less than...|||Thanks Adam for t he tip, i added the single quote to the expresion in the report and i created a new report and exported to excel, absouly it works, it stops givng me the error for the text to number conversion but i can see the single quote when i open the Excel even if i print it, is there a way that that single quote can be invisible?... jejjeje or am i asking too much?|||

I have this exact same issue. I have an Access Database that exports a tool report to Excel. The report exports properly and has worked for a long time. I recently updated it to Access 2003 and now it has problem. The tools part number is in the form 99-99-9999 but when it tries to export to Excel it turns it into a number and I get a single value such as 359. So I already figured I could just insert the single quote in front of the values and that works but I can see it in Excel. If you enter a number such as this in Excel directly with the single quote it does not have the problem but exporting from Access to Excel it does.

Are there any solutions to that?

|||

this issues for me began with sorting dates, and i have the same problem that you have wit hthis i couldn't get the date right to be filter, but in my RDL i found the solution to that on, in the expresion field that you are getting the date value you have to add the Cdate function and in the format of that textbox in the RDl you have to specify the format yo want it to be: mm/dd/yyyy so that you will avoid to add the single quote to the date fields, but i still have found a solution for my filtering issues when there are numbers and numbers with characters in the same column :-(

Anyone?

|||
You may want to try using the CStr() function, or ToString() on the value of the field, to cast the value to a string. The latter approach, however, may cause an exception if the value of the field is Nothing.

Ian|||

Thank you for the advice, I tried it but I think the problem is I am using an Access report to export to Excel using DoCmd.OutputTo. So between the report and Excel I can't change the formatting apparently. Maybe I need to look into a way to export directly from my Access Query to Excel. That may improve things as I would be taking out a step. If you know which command that would be please let me know but I will probably begin a search for it.