EDI file - generate using SQL?

Can an EDI file be generated using SQL 7 or SQL 2000?
I need to be able to generate EDI files based on data from an SQL table.What are the formats for an EDI file. Methinks you need DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
Can an EDI file be generated using SQL 7 or SQL 2000?
I need to be able to generate EDI files based on data from an SQL table.|||Yes it can, but it is better done client side using ADO, and even better
using BizTalk. Have a look at the FOR XML predicates for an idea of how to
do this. Its not for the faint hearted or weak kneed to do this in SQL
Server.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.|||Hi Tom,
By format, do you mean ANSI X12 or EDIFACT?
I was initially leaning towards DTS but I am unsure how to map headers that
are in the EDI file with data coming out of SQL.
I read somewhere that Biztalk has the ability to do this, unfortunately I do
not have access to this tool.
If DTS can do this, please point me to an article that explains this process.
Tom, thanks for getting back to me.
"Tom Moreau" wrote:
> What are the formats for an EDI file. Methinks you need DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "niv" <niv@.discussions.microsoft.com> wrote in message
> news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
>|||I don't know of any article per se but I do know that DTS is very capable -
and its SQL 2005 descendant, SSIS, is even more so. Clearly, you'll be
looking at Data Pumps with transforms. Perhaps if you posy a question in
the DTS newsgroup re EDI, you may get someone who has already done this.
Also, check out www.sqldts.com and www.sqlis.com.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"niv" <niv@.discussions.microsoft.com> wrote in message
news:1CDBD9EC-2EE7-4651-9450-3BAB5F579971@.microsoft.com...
Hi Tom,
By format, do you mean ANSI X12 or EDIFACT?
I was initially leaning towards DTS but I am unsure how to map headers that
are in the EDI file with data coming out of SQL.
I read somewhere that Biztalk has the ability to do this, unfortunately I do
not have access to this tool.
If DTS can do this, please point me to an article that explains this
process.
Tom, thanks for getting back to me.
"Tom Moreau" wrote:
> What are the formats for an EDI file. Methinks you need DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "niv" <niv@.discussions.microsoft.com> wrote in message
> news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
>|||"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
You have guts!
I'd say in 2005 it's much easier then 2000. I did a snarl of EDI two years
ago with maintaining multiple DC's for multiple MFGs.
I never got to try BizTalk, but they say it's now able to parse EDI easily.
other issue is dates need ' ' for use in SQL server, so using a processing
class to extract what will be pulled or manipulated in the db is the way to
go. Then you have that same class write out the lines you need in your
replies.
__Stephen

EDI file - generate using SQL?

Can an EDI file be generated using SQL 7 or SQL 2000?
I need to be able to generate EDI files based on data from an SQL table.What are the formats for an EDI file. Methinks you need DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
Can an EDI file be generated using SQL 7 or SQL 2000?
I need to be able to generate EDI files based on data from an SQL table.|||Yes it can, but it is better done client side using ADO, and even better
using BizTalk. Have a look at the FOR XML predicates for an idea of how to
do this. Its not for the faint hearted or weak kneed to do this in SQL
Server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.|||Hi Tom,
By format, do you mean ANSI X12 or EDIFACT?
I was initially leaning towards DTS but I am unsure how to map headers that
are in the EDI file with data coming out of SQL.
I read somewhere that Biztalk has the ability to do this, unfortunately I do
not have access to this tool.
If DTS can do this, please point me to an article that explains this process
.
Tom, thanks for getting back to me.
"Tom Moreau" wrote:

> What are the formats for an EDI file. Methinks you need DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "niv" <niv@.discussions.microsoft.com> wrote in message
> news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
>|||I don't know of any article per se but I do know that DTS is very capable -
and its SQL 2005 descendant, SSIS, is even more so. Clearly, you'll be
looking at Data Pumps with transforms. Perhaps if you posy a question in
the DTS newsgroup re EDI, you may get someone who has already done this.
Also, check out www.sqldts.com and www.sqlis.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"niv" <niv@.discussions.microsoft.com> wrote in message
news:1CDBD9EC-2EE7-4651-9450-3BAB5F579971@.microsoft.com...
Hi Tom,
By format, do you mean ANSI X12 or EDIFACT?
I was initially leaning towards DTS but I am unsure how to map headers that
are in the EDI file with data coming out of SQL.
I read somewhere that Biztalk has the ability to do this, unfortunately I do
not have access to this tool.
If DTS can do this, please point me to an article that explains this
process.
Tom, thanks for getting back to me.
"Tom Moreau" wrote:

> What are the formats for an EDI file. Methinks you need DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "niv" <niv@.discussions.microsoft.com> wrote in message
> news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
>|||"niv" <niv@.discussions.microsoft.com> wrote in message
news:64BE6736-CBFF-4EAE-BF11-A6799EA1B996@.microsoft.com...
> Can an EDI file be generated using SQL 7 or SQL 2000?
> I need to be able to generate EDI files based on data from an SQL table.
You have guts!
I'd say in 2005 it's much easier then 2000. I did a snarl of EDI two years
ago with maintaining multiple DC's for multiple MFGs.
I never got to try BizTalk, but they say it's now able to parse EDI easily.
other issue is dates need ' ' for use in SQL server, so using a processing
class to extract what will be pulled or manipulated in the db is the way to
go. Then you have that same class write out the lines you need in your
replies.
__Stephen

Echo sql which has been run

I am relatively new to SQL Server.

I have a command file with the following contents :
osql -E -i%1.sql -d%2 -oq:\%1.log

The sql script file has a number of insert/update statements.
The log file produced looks something like this :

1> 2> (1 row affected)
1> 2> (1 row affected)
1> 2> (0 rows affected)

Is there any setting which can be turned on such that the log file
produced from this command file will echo the statement and then
the number of rows which are affected.

TIA."Michael McGarrigle" <mjm@.barwonwater.vic.gov.au> wrote in message
news:9d0cafdc.0309111717.4dc8efaa@.posting.google.c om...
> I am relatively new to SQL Server.
> I have a command file with the following contents :
> osql -E -i%1.sql -d%2 -oq:\%1.log
> The sql script file has a number of insert/update statements.
> The log file produced looks something like this :
> 1> 2> (1 row affected)
> 1> 2> (1 row affected)
> 1> 2> (0 rows affected)
> Is there any setting which can be turned on such that the log file
> produced from this command file will echo the statement and then
> the number of rows which are affected.
> TIA.

You can try adding -e -n to your command line. It works best if each
statement is in its own batch:

update...
go
insert...
go

Like that, you get each statement with the rowcount immediately after it. If
all statements are in one batch, you'll get all the statements together then
all the rowcounts together. That might be OK for you anyway, of course.

Simonsql

Echo for SQL scripts

Hi all,

I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -

drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist

7444 rows deleted.

Commit complete.

Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?

ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:

EBCDIC to ASCII conversion in SSIS

I tried to setup a flat file data source that has code page 37 (EBCDIC)

Then I have a flat file destionation that is ASCII.

And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.

I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.

Has anyone been able to do this with SSIS out of the box, without any extra components ?

Clarence

EBCDIC 037 is one of the EBCDIC defined in SQL Server you have to use the collation below to create your database, tables and columns and you have to use Nvarchar and SSIS datatype for Nvarchar. To export to ASCII just do convert to Varchar before the export. Some EBCDIC code pages are not defined in SQL Server the link below shows those covered. Hope this helps.

SQL_EBCDIC037_CP1_CS_AS

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

|||

Wow, that's great !! I'm able to import it into a DB table now, but when I do something like this

SELECT

CONVERT(varchar(2), Rec_Type) Rec_Type

FROM dbo.CCP_FAC_EBCDIC

it's giving me an error:

An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.

any ideas ?

|||

I don't think your convert to varchar definition is correct because nvarchar is double bytes that is one nvarchar is two varchar so the question is what is the size of the data you are exporting to ASCII. You could avoid the error by using SELECT INTO with the convert to varchar if the varchar is not big enough to be destination for your nvarchar your SELECT INTO will fail. Hope this helps.

|||

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

|||

ClarenceC wrote:

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

I am glad I could help.

Easy Way Determine Database File Size Another

What is an easy way to determine the numeric value for database file size,
this database exist on another server using T-SQL?
Thank You,If you have a linked server, you can try
EXEC linked_server.database_name.dbo.sp_helpfile
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D329E712-EB1F-4418-BCD1-EA93FCB95704@.microsoft.com...
> What is an easy way to determine the numeric value for database file size,
> this database exist on another server using T-SQL?
> Thank You,
>

Easy question: Copy Image to a File

How I can copy an image to a file?

I want to use SP

I use MSDE, textcopy is not installed

I can use bcp, but I don't understand the syntax for image filed

Something like this

bcp "SELECT [ImageFiled] FROM MyDatabase..MYTable WHERE [ID]=1111" queryout c:\test.BMP -c -Sservername -Usa -Ppassword

This statement dont work.
Please I need an example.

Thanks,
Sorry for my Englishhttp://www.databasejournal.com/features/mssql/article.php/1443521 to feature the task.

Easy question

How can I output the result of a query in a text file.
(I want to use SP)EXEC master..xp_cmdshell 'bcp...

Easy database dump to a file

Hi everyone,
Using SQL Server 2000, what would be the best way to get a dump of a
database that is on a server at a client? I need to be able to say "Here,
run this script/app/command" and it has to be simple enough so that the
person there is able to complete the task. Then, the person will ship me
the dump file so I can have a look at it.
Thanks in advance for any suggestions.
Ericbackup database and the copy the file (.bak) to your workstation
"Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi everyone,
> Using SQL Server 2000, what would be the best way to get a dump of a
> database that is on a server at a client? I need to be able to say "Here,
> run this script/app/command" and it has to be simple enough so that the
> person there is able to complete the task. Then, the person will ship me
> the dump file so I can have a look at it.
> Thanks in advance for any suggestions.
> Eric
>|||Hi
T-SQL command BACKUP DATABASE and give it to them as a file to run against
OSQL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Uri Dimant" wrote:

> backup database and the copy the file (.bak) to your workstation
>
> "Eric Caron" <ecaron.nospam@.quebecaffaires.com> wrote in message
> news:eEPoz9CbFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
>

Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

End If

Larry Pope

Easier way to convert Non-Unicode to Unicode

I have built a large package and due to database changes (varchar to nvarchar) I need to do a data conversion of all the flat file columns I am bringing in, to a unicode data type. The way I know how to do this is via the data conversion component/task. My question is, I am looking for an easy way to "Do All Columns" and "Map all Columns" without doing every column by hand in both spots.

I need to change all the columns, can I do this in mass? More importantly once I convert all these and connect it to my data source it fails to map converted fields by name. Is there a way when using the data conversion task to still get it to map by name when connecting it to the OLE destination?

I know I can use the wizard to create the base package, but I have already built all the other components, renamed and set the data type and size on all the columns (over 300) and so I don't want to have to re-do all that work. What is the best solution?

In general I would be happy if I could get the post data conversion to map automatically to the source. But because its DataConversion.CustomerID it will not map to CustomerID field on destination. Any suggestions on the best way to do this would save me hours of work...

Thanks.

If SSIS has to use a namespace to identify a column...this happens when you have Source1.CustID and DataConversion1.CustID it can't do the mapping. If you changed the names in the source it could, but that might be more trouble than it's worth.

Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.

dynamically exporting data to Access file

I am looking a way to export SQL Server 2005 DB tables to Access file dynamically. Like if i have added or removed any tables from SQL DB then when i run SSIS package it should export that table with data to Access file. is there any easy way to do this. if it is not them please someone please tell me which controls should i look at and which technique i should use to do this.

The only way I can think of is to generate packages programatically; as on each run you have to check what tables are available and whether they exists or not in the Access DB. There is section in BOL that talks about creating packages programatically in case you are interested.

I don't know what is your ultimate goal, but perhaps a DB backup may be what you need?

|||

Thanks Rafael, it is actully a process for extraction of data from SQL Server DB and then export that data to Access file. so the end result should be a package which get tables and data from SQL Server and push that information to Access file every time we run it. will you please give me more inforamtion about BOL and if you have got any good scource about it please point towords it. that would be really helpfull. or any other suggestion

Regards,

Haroon

|||

I google search gives: http://www.google.com/search?q=SSIS+package+programmatically&hl=en&pwst=1&start=10&sa=N

This is the topic in BOL:

http://msdn2.microsoft.com/en-us/library/ms136025(SQL.90).aspx

I Suggested to look into building packages programmatically given the fact that the number and name of tables in not known at run time. Other approach may be to take out the 'dinamically' part of your requirment and yous build one package/dataflow per each table you want to move.

Dynamically creating SSIS package for each flat file

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.

Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.

What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

Trav2003 wrote:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically

Dynamically create text file as destination from sql script in SSIS

I have a select Script as follows:

SELECT c.ABC AS 'ABC'

, a.Qty AS 'Quantity_Recived'

, b.PC AS 'PC'

, b.PC AS 'PC'

, 'I' AS 'Flag'

FROM TNRInventory.dbo.tInventoryAlloc AS a

LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID

LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset

LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID

WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'

group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center

order by c.SKU_NO,vwMap.[DataSourceAllocName]

GO

i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.

I have found some document related to same issue on this url

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17

but still there is a catch.

Can any one guide/suggest me better way to do the aforesaid.

Thanks

Thinking while typing, this could be done with a few steps.

1 - Data Flow - Load a staging table with the results of the SQL. Add to it a row number counter so that every row is numbered uniquely.

2 - Control Flow - Run an Execute SQL Task to select max(rownumber) from that staging table

3 - Control Flow - Use a script task to take the output of the Execute SQL task and populate another variable with the number of iterations needed to populate files with up to 300 rows. (max(rownumber) / 300 - if no remainder, use that value, if remainder add one to the integer, etc...)

4 - Control Flow - Use a for loop to iterate the output variable from step 3 above.

5 - Control Flow - Build a variable set to an expression to use a base filename and the variable from step 3 above.

6 - Control Flow For Loop - Loop through the variable from step 3 above and add a data flow. Inside this data flow, use an OLE DB (or whatever) to connect to the staging table from step 1, filtering by (variable_step3 * 300) you can select just the records for this iteration. Hook that up to a flat file destination, which uses expressions to set the file name equal to the variable from step 5 above.

Something like that.|||

You might also check Jamie's blog post on this topic.

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

Essentially the same technique that Phil recommended, but he's got some sample code already

|||

jwelch wrote:

You might also check Jamie's blog post on this topic.

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

Essentially the same technique that Phil recommended, but he's got some sample code already

D'oh! Should've just looked there first!|||

What i did is i create a view for aforesaid script and then used that script in the following VB.net script

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.SqlClient

Imports System.Text

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim ConnectionString As String = "Server=ServerName;Database=DatabaseName;uid=Login;pwd=Password;"

Dim querystring As String = "SELECT * FROM vwInventory_I"

Dim con As New SqlConnection(ConnectionString)

Dim adapter As New SqlDataAdapter()

Dim ds As New DataSet

Dim dt As New DataTable

adapter.SelectCommand = New SqlCommand(querystring, con)

adapter.Fill(ds)

dt = ds.Tables(0)

CreateFile(dt)

End Sub

Private Sub CreateFile( ByVal table As DataTable)

Dim intRowCount As Integer = table.Rows.Count

Dim Column1Value As String

Dim Column2Value As String

Dim Column3Value As String

Dim Column4Value As String

Dim Column5Value As String

Dim sb As New StringBuilder

Dim seperator As String = vbTab

Dim nFileNameCount As Integer

nFileNameCount = 0

'For i As Integer = 0 To table.Columns.Count - 1

Dim i As Integer = 0

Dim j As Integer = 0

For j = 0 To intRowCount - 1

If nFileNameCount = 100 Then

PrintFile(sb, j)

'clears the stringbuilder

sb.Remove(0, sb.ToString.Length - 1)

nFileNameCount = 0

End If

Column1Value = table.Rows(j)(i).ToString

Column2Value = table.Rows(j)(i + 1).ToString

Column3Value = table.Rows(j)(i + 2).ToString

Column4Value = table.Rows(j)(i + 3).ToString

Column5Value = table.Rows(j)(i + 4).ToString

sb.Append(Column1Value & seperator & Column2Value & seperator & Column3Value & seperator & _

Column4Value & seperator & Column5Value & seperator & vbCrLf)

nFileNameCount = nFileNameCount + 1

Next

PrintFile(sb, j)

'Next

End Sub

Private Sub PrintFile( ByVal sb As StringBuilder, ByVal recordcount As Integer)

' Create an instance of StreamWriter to write text to a file.

Dim strDate As String

strDate = String.Format("{0:yyyy}" , DateTime.Now)

strDate = strDate & String.Format( "{0:MM}", DateTime.Now)

strDate = strDate & String.Format( "{0d}", DateTime.Now)

Using sw As StreamWriter = New StreamWriter("C:\SSIS\I_" & strDate & "_" & recordcount & ".txt")

sw.WriteLine(sb.ToString)

sw.Close()

End Using

'this will create a text file in bin directory

End Sub

End Class

This is how you generate tab delimited files to test check Use Script Tas from SSIS

|||Uh, okay. Good. So why even use SSIS then? Write your own program as you have done, compile it, and execute the resulting binary file. Leave the bloat of SSIS out of it.|||

I am going to put this piece in middle of my design.

It was the one of the key to finish my jik-so-puzzle.

Other one is to delete the files from Unix Aix Server with FTP connection.

And here too I have to go all the way round, as there FTP connection works fine on Microsoft server but not on UNIX server.

It does not allow you to delete the files on UNIX server.

That is the reason I am using the code in SSIS.

Dynamically create text file as destination

I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.

Thanks,

Fred

SSIS by itself won't be able to prompt you. You'll have to write a custom executable perhaps to get this to work.|||

I think you could use the Windows.Forms.SaveFileDialog object in a Script Task.

|||

Thanks for the suggestions but I am new to programming. I have "played" a little with VBA in Excel. Can anyone get me started with some more VB code?

I would think this has been done before but I can not find any code in any SSIS forum for it.

Thanks,

Fred

|||

Couldn't you create a package variable called FilePath (for example) of type string and then read into it through a script task?

Something like the following would give you an input prompt and then store the value into the variable created:

Public Sub Main()

Dts.Variables("FilePath").Value = InputBox("Enter your file path", "Prompt").Trim()

Dts.TaskResult = Dts.Results.Success

End Sub

Then, you could just set the ConnectionString property in Expressions to the FilePath variable.

|||

SaveFileDialog would give the same result than InputBox except you are able to browse...

Dim fSaveFileDialog As New Windows.Forms.SaveFileDialog
fSaveFileDialog.ShowDialog()
Dts.Variables("fileName").Value = fSaveFileDialog.FileName

|||

Thanks for the suggestions but I am still lost.

I have a Data Flow Task which has a SQL Server Source. In the Data Flow Task I connect the SQL Server Source to what I believe is next - a Destination Script Component. I did not see anywhere you code put code in a Data Flow Destination - Flat File Destination.

In the Script Component it allows you to add code in Script Design box which is Visual Studio's designer window. How do I assign the filename to the new file I create?

Thanks for any help.

Fred

|||

Use expressions to assign the filename to the Flat File Destination (property Connection = User::variable)

Add a Script task before your data flow task. This script task will prompt for the file location and set the User::variable.

|||

Can I just question this approach? Is this for end users? If so then I don’t think using SSIS in this way is appropriate. SSIS is a server, so it is licensed as part of the full SQL Server license, and is not something you can install on client desktops as part of the normal Client access License. Each machine needs a full SQL Server license. To have a task offering a UI to the user means that package, and therefore SSIS itself, must be installed on the user's machine, making each user machine a full SSIS server install.

Dynamically changing a backup file name

Hi all
I have read in many posts that DTS is not usually used for doing backups (a
scheduled job is preferred). However if I want to dynamically change the
name of the backup file depending on the date e.g. mydata_september.bak
during the month of september and then mydata_november.bak in november, how
would I go about doing that?
Dale1.Simple way is use the SQL Server Maintenance plan. It
automatically appends the Year,month,Day and time with the
database name.
Example. The backup file name for pubs database will be
pubs_db_200310132200.BAK.
2. If you want to use the datetime with your format, you
have to write a program.
Sample Script:
--
Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin
Select @.dbname=db_name()
Print @.dbname
Select @.CurrentDateTime = substring(DATENAME(month, getdate
()),1,3) +
cast(DATEPART(day, GETDATE()) as
varchar(2))+
cast(DATEPART(hh, GETDATE()) as
varchar(2)) +
cast(DATEPART(mi, GETDATE()) as
varchar(2))
Select @.dbbackupname = @.dbname+@.CurrentDateTime
select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
backup database @.dbname to disk=@.dbbackupname WITH
NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS =10, FORMAT
Print @.CurrentDateTime
End
Warning: Test the script before you use. Use at your own
risk.
- SQLVarad(MDCBA-1999,MCSE-1999)
>--Original Message--
>Hi all
>I have read in many posts that DTS is not usually used
for doing backups (a
>scheduled job is preferred). However if I want to
dynamically change the
>name of the backup file depending on the date e.g.
mydata_september.bak
>during the month of september and then
mydata_november.bak in november, how
>would I go about doing that?
>Dale
>
>.
>|||Thanks, will give it a try
Dale
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:01f101c398d9$706888d0$a601280a@.phx.gbl...
> 1.Simple way is use the SQL Server Maintenance plan. It
> automatically appends the Year,month,Day and time with the
> database name.
> Example. The backup file name for pubs database will be
> pubs_db_200310132200.BAK.
> 2. If you want to use the datetime with your format, you
> have to write a program.
> Sample Script:
> --
> Declare
> @.CurrentDateTime varchar(20),
> @.dbname varchar(20),
> @.dbbackupname varchar(40)
> Begin
> Select @.dbname=db_name()
> Print @.dbname
> Select @.CurrentDateTime = substring(DATENAME(month, getdate
> ()),1,3) +
> cast(DATEPART(day, GETDATE()) as
> varchar(2))+
> cast(DATEPART(hh, GETDATE()) as
> varchar(2)) +
> cast(DATEPART(mi, GETDATE()) as
> varchar(2))
> Select @.dbbackupname = @.dbname+@.CurrentDateTime
> select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
> backup database @.dbname to disk=@.dbbackupname WITH
> NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS => 10, FORMAT
> Print @.CurrentDateTime
> End
> Warning: Test the script before you use. Use at your own
> risk.
> - SQLVarad(MDCBA-1999,MCSE-1999)
> >--Original Message--
> >Hi all
> >
> >I have read in many posts that DTS is not usually used
> for doing backups (a
> >scheduled job is preferred). However if I want to
> dynamically change the
> >name of the backup file depending on the date e.g.
> mydata_september.bak
> >during the month of september and then
> mydata_november.bak in november, how
> >would I go about doing that?
> >
> >Dale
> >
> >
> >.
> >

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan