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

Easy question about server log files

SQL Server 2000 running on Windows 2000

Is there a way to set up the SQL Server Log files so that it automatically creates a new one (or overwrites the old one) when the old one is full?

We keep having people shut out of our web site due to the log file being full. Since we have no DBA I volunteered to try to find out how we can avoid this faux pas in the future.

Thanks for your help!New idea for Microsoft !!!

If seriously may be it will be easier to backup transaction log more frequently (if full model) or increase maximal size of log because additional log files will only consume disk space until it is full.

It needs to find out why log is growing so fast - may be it is because of not closed transaction or something else.|||Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?|||Originally posted by Fly Girl
Sorry, but I'm new at this. Are we both talking about the same thing?

I'm not talking about the transaction log files that are backed up but of the log found under:

Management>SQL Server Logs

This log just shows what SQL Server has been doing, not what the users have been doing.

Perhaps you can tell me where the interface to make settings for this log can be found?

I am taking my words back...

Right click on SQL Server Log files/configure:

BOL:

Configure SQL Server Error Logs
Use this dialog box to view or specify the following options.

Options
Limiting the number of the error log files before they are recycled.

Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of Microsoft SQL Server is started. Typically, SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.

Maximum number of the error log files.

Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.|||Ok ... we need to get this clear ...

First of all .. I dont think it would be the SQL server error logs that are causing the problem.

Connect to the server ... using QA and run the following commands

exec xp_fixeddrives

and

sp_helpdb 'production_databasename'

Copy paste results and we will go further.|||Originally posted by snail
Specify the maximum number of error log files created before they are recycled. The default is six, which is the number of previous backup logs SQL Server retains before recycling them.

And you can create a scheduled to be run daily in SQL Server
Command to be executed in Job :-
dbcc errorlog|||OK, I think I've got this one ironed out now.

Thanks for the help!

Easy question

I generally do backups/restores of data and log files
using Enterprise Manager. I am just learning.
What does the following statement do?
BACKUP LOG YourDatabase WITH Truncate_Only
Like if take the backup from Enterprise it creates .bak
files whether the above statement will also create
some .bak files.That command truncates the inactive portion of the transaction log without
taking a transaction log backup. Good from reducing the size of the
transaction log, but since no backup was taken you will not be able to
restore any data that was added, deleted, or updated if those data
modifications where contained in the inactive portion of the transaction
that is truncated.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:3fa501c49ffd$1dc02350$a301280a@.phx.gbl...
> I generally do backups/restores of data and log files
> using Enterprise Manager. I am just learning.
> What does the following statement do?
> BACKUP LOG YourDatabase WITH Truncate_Only
> Like if take the backup from Enterprise it creates .bak
> files whether the above statement will also create
> some .bak files.

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

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 create SQL Scripts

I would like to be able to create SQL Scripts with a stored procedure (or some other means). I have looked all through the help files and even tried watching the profiler to see how EM does it. Any help would be appreciated.I've written a set of procedures that will generate INSERT/SELECT/UPDATE statements for a given table, plus more. Is that what you are looking for?|||Thanks for your reply.

I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.|||Don't know an easy way to do this, I don't think you can do it using DTS
at least not pre-2000.

The hard way is to write an SP which generates all the DDL using
sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.

You can use sp_help code as a preliminary guide on how to extract the DDL.|||You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.|||Thanks for the reply plus any help given, I am using 2000.|||achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?

Thanks|||I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.

This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.|||Attachment|||Thanks!!

That worked great.

Dynamically Change SSIS For Each Loop container

Hello,
I would like to modify "Files" attribute of the Foreach Loop of type File 
Enumerator. This attribute is used to set the mask (for example *.txt) to 
specify which files to include in the selection. I need to be able to change 
this mask dynamically depending on package global variable. Is this possible?
Thank you!
Michael

Use the Expressions property, and create an expression for FileSpec property that references the global variable.|||

More details on How To get to the Extressions Property -

Open the ForEach Loop Editor by double clicking ForEach Loop Container.
Select Collection on left.
Click on the + sign on Expressions
Select FileSpec for Property and On Expression select the Global Variable Name. (which holds the file property such as *.txt)

Thanks,
Loonysan

Dynamically Adding Subreports

I would like to Create a report that will print as one document, but is
basically made up of multiple reports (rdl files), however, I need to be able
to determine which reports (rdl files) should be added and pass parameters to
each rdl file dynamically. Can this be done with SQL Reporting Services?You could do that in VS 2005 using the RS Winforms/Webforms controls in
"local" processing mode and registering a subreport callback which
dynamically provides different RDLs with different sets of parameters.
I don't really have a good suggestion for RS 2000 - you could probably
achieve some of your requirements by dynamically generating RDLs, publishing
and rendering them by writing an application that uses the SOAP-API.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amie Fedric" <AmieFedric@.discussions.microsoft.com> wrote in message
news:6194746B-7D5B-49CE-8498-50AD8EF3B561@.microsoft.com...
>I would like to Create a report that will print as one document, but is
> basically made up of multiple reports (rdl files), however, I need to be
> able
> to determine which reports (rdl files) should be added and pass parameters
> to
> each rdl file dynamically. Can this be done with SQL Reporting Services?

Dynamic transformation to upload flat files into database

Hi,

My scenario:

I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like

1) Multipying the source column by 100

2) Put an if condition for 2 source columns and then select one column to be copied into the destination.

I have the flat files schema with me and also all the transformations that are required.

Question:

Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?

Why I want this way?

In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.

Thanks for your time.

Regards,

$wapnil

spattewar wrote:

I have 4 different flat files types each having different no. of column, order of columns etc.

There is your problem. I doubt you can have a Flat file connection manager to mutate to accommodate a different file structure each time. What I would do is to have 4 single dataflows to load the 4 files into a common destination (staging table/rawfile,etc) and then an extra dataflow to perform the transformations.

|||Rafael is right. You cannot have a Flat File Connection Manager which can accommodate file of different formats. You can use different flat file connection managers to process the files, and apply transformations and then save to a common destination.|||

Okay. understood that a single flat file connention can have only one format attached to it.

Will this be a possiblity?

Create a template package having a single data flow that connects to a file, reads the data into memory, applies transformation and then load that data into a single destination table. Then programmitically load the package and change the objects as required in the code?

Thanks for your time.

Regards,

$wapnil

|||

spattewar wrote:

Okay. understood that a single flat file connention can have only one format attached to it.

Will this be a possiblity?

Create a template package having a single data flow that connects to a file, reads the data into memory, applies transformation and then load that data into a single destination table. Then programmitically load the package and change the objects as required in the code?

Thanks for your time.

Regards,

$wapnil

If you're going to do that, then you might as well write the whole thing in code. The trouble in using a template is the metadata coming in and out of each component in the data flow. They all have to match up and are generally "fixed" at development time. So even if you progmatically change the source, a downstream component may break because the upstream metadata changed.|||

Ok. lets say that mapping a file connection manager to four different files is not possible but then is this possible?

Create a package that transforms a flat file and upload the data into the database. But the transformation can be changed on the fly or the transformation can be picked up from the database. for e.g.

At design time we inserted the transformation lets say using a derived column transformation task. The transformation multiplies all the column in the input by 1000 and gives an output which is further inserted into the destination table. Now if I want to change the transformation from "multiplying by 1000" to "multiplying by 10" on the fly, then is it possible.

Can we make the derived column task to pick up the transformation from the database and create the output.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Ok. lets say that mapping a file connection manager to four different files is not possible but then is this possible?

Create a package that transforms a flat file and upload the data into the database. But the transformation can be changed on the fly or the transformation can be picked up from the database. for e.g.

At design time we inserted the transformation lets say using a derived column transformation task. The transformation multiplies all the column in the input by 1000 and gives an output which is further inserted into the destination table. Now if I want to change the transformation from "multiplying by 1000" to "multiplying by 10" on the fly, then is it possible.

Can we make the derived column task to pick up the transformation from the database and create the output.

Thanks for your time.

$wapnil

Yes... You can use package configurations to achieve this. For that matter, you can store the values in a table and use an Execute SQL task to load a variable with the "multiplication" value, or whatever.

Then, in your derived column, you simply say "[column1] * @.[User::MyVar]". At run time, the variable "MyVar" will pick up the appropriate value. You can have as many as these as you want.

What you cannot do is to alter the number of columns coming into the flat file source.|||

this is great.

sorry to bug you with this, but if I have a transformation which is like

If [file1.column1] = "Good" Then [file2.column1 = file1.column1]

Else [file2.column1 = file1.column2]

End If

How to accomodate this?

Thanks for your time.

$wapnil

|||

spattewar wrote:

this is great.

sorry to bug you with this, but if I have a transformation which is like

If [file1.column1] = "Good" Then [file2.column1 = file1.column1]

Else [file2.column1 = file1.column2]

End If

How to accomodate this?

Thanks for your time.

$wapnil

This doesn't make sense in SSIS terms. You can't update sources. If, however, you have two sources (file1 and file2) that get merged or unioned together, then in a derived column, you can do (and please search the forums. It will help you, I guarantee it):

Derived column: [column3] expresion: [column1] == "Good" ? [column1] : [column2]

[column3] equals [file2.column1]|||

sorry my mistake...it should be

If [file1.column1] = "Good" Then [destination = file1.column1]

Else [destination = file1.column2]

End If

$wapnil

|||

spattewar wrote:

sorry my mistake...it should be

If [file1.column1] = "Good" Then [destination = file1.column1]

Else [destination = file1.column2]

End If

$wapnil

So my example still applies... Please test this stuff so that you can learn it!
In a derived column, create a NEW column and call it what you want. OR, if the data types are the same, you can replace [column1]: (Below is the expression you'd need)

[column1] == "Good" ? [column1] : [column2]|||

going one step ahead in the above aproach.

the below is the table content that is present in the transformation table in our database

SourceColName Transformation DestinationColName

coupon coupon/noofpayments coupon (noofpayments is also a sourcecolname)

quantity quantity*10 quantity

The derived column transformation gets an input which has the following columns coupon,quantity and noofpayments.

Is it possible that the derived column transformation pickes up the transformations from the database and apply it to the input columns.

I am pretty new to this and hence may not be understanding it correctly. apology for the same. But I am just trying to minimize the impact here if any of the transformation changes.

Thank for your time.

Regards,

$wapnil

|||No, this isn't going to be possible.

Perhaps with some advanced scripting, maybe.|||

Bugging you again Phil...

This is to help me finalize my approach of doing the data transformations.

Scenario:

1) 4 different flat file formats

2) 4 transformations set applying on each flat file

3) 1 destination table

Aproaches:

1) Build one file connection, flat file source and one data transformation for each flat file. Create one package for each OR create a single package and have multiple data flow paths in that.

2) Build custom flat file connection, custom flat file source and custom data transformation. Hush.....

Future changes:

There can be more flat file formats added to the list.

Apology for this but at my end here people want to build custom components and use it across projects.......so just trying to find out the fdeasibility.

Thanks again for your time.

Regards,

$wapnil

Dynamic transformation to upload flat files into database

Hi,

My scenario:

I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like

1) Multipying the source column by 100

2) Put an if condition for 2 source columns and then select one column to be copied into the destination.

I have the flat files schema with me and also all the transformations that are required.

Question:

Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?

Why I want this way?

In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.

Thanks for your time.

Regards,

$wapnil

spattewar wrote:

I have 4 different flat files types each having different no. of column, order of columns etc.

There is your problem. I doubt you can have a Flat file connection manager to mutate to accommodate a different file structure each time. What I would do is to have 4 single dataflows to load the 4 files into a common destination (staging table/rawfile,etc) and then an extra dataflow to perform the transformations.

|||Rafael is right. You cannot have a Flat File Connection Manager which can accommodate file of different formats. You can use different flat file connection managers to process the files, and apply transformations and then save to a common destination.|||

Okay. understood that a single flat file connention can have only one format attached to it.

Will this be a possiblity?

Create a template package having a single data flow that connects to a file, reads the data into memory, applies transformation and then load that data into a single destination table. Then programmitically load the package and change the objects as required in the code?

Thanks for your time.

Regards,

$wapnil

|||

spattewar wrote:

Okay. understood that a single flat file connention can have only one format attached to it.

Will this be a possiblity?

Create a template package having a single data flow that connects to a file, reads the data into memory, applies transformation and then load that data into a single destination table. Then programmitically load the package and change the objects as required in the code?

Thanks for your time.

Regards,

$wapnil

If you're going to do that, then you might as well write the whole thing in code. The trouble in using a template is the metadata coming in and out of each component in the data flow. They all have to match up and are generally "fixed" at development time. So even if you progmatically change the source, a downstream component may break because the upstream metadata changed.|||

Ok. lets say that mapping a file connection manager to four different files is not possible but then is this possible?

Create a package that transforms a flat file and upload the data into the database. But the transformation can be changed on the fly or the transformation can be picked up from the database. for e.g.

At design time we inserted the transformation lets say using a derived column transformation task. The transformation multiplies all the column in the input by 1000 and gives an output which is further inserted into the destination table. Now if I want to change the transformation from "multiplying by 1000" to "multiplying by 10" on the fly, then is it possible.

Can we make the derived column task to pick up the transformation from the database and create the output.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Ok. lets say that mapping a file connection manager to four different files is not possible but then is this possible?

Create a package that transforms a flat file and upload the data into the database. But the transformation can be changed on the fly or the transformation can be picked up from the database. for e.g.

At design time we inserted the transformation lets say using a derived column transformation task. The transformation multiplies all the column in the input by 1000 and gives an output which is further inserted into the destination table. Now if I want to change the transformation from "multiplying by 1000" to "multiplying by 10" on the fly, then is it possible.

Can we make the derived column task to pick up the transformation from the database and create the output.

Thanks for your time.

$wapnil

Yes... You can use package configurations to achieve this. For that matter, you can store the values in a table and use an Execute SQL task to load a variable with the "multiplication" value, or whatever.

Then, in your derived column, you simply say "[column1] * @.[User::MyVar]". At run time, the variable "MyVar" will pick up the appropriate value. You can have as many as these as you want.

What you cannot do is to alter the number of columns coming into the flat file source.|||

this is great.

sorry to bug you with this, but if I have a transformation which is like

If [file1.column1] = "Good" Then [file2.column1 = file1.column1]

Else [file2.column1 = file1.column2]

End If

How to accomodate this?

Thanks for your time.

$wapnil

|||

spattewar wrote:

this is great.

sorry to bug you with this, but if I have a transformation which is like

If [file1.column1] = "Good" Then [file2.column1 = file1.column1]

Else [file2.column1 = file1.column2]

End If

How to accomodate this?

Thanks for your time.

$wapnil

This doesn't make sense in SSIS terms. You can't update sources. If, however, you have two sources (file1 and file2) that get merged or unioned together, then in a derived column, you can do (and please search the forums. It will help you, I guarantee it):

Derived column: [column3] expresion: [column1] == "Good" ? [column1] : [column2]

[column3] equals [file2.column1]|||

sorry my mistake...it should be

If [file1.column1] = "Good" Then [destination = file1.column1]

Else [destination = file1.column2]

End If

$wapnil

|||

spattewar wrote:

sorry my mistake...it should be

If [file1.column1] = "Good" Then [destination = file1.column1]

Else [destination = file1.column2]

End If

$wapnil

So my example still applies... Please test this stuff so that you can learn it!
In a derived column, create a NEW column and call it what you want. OR, if the data types are the same, you can replace [column1]: (Below is the expression you'd need)

[column1] == "Good" ? [column1] : [column2]|||

going one step ahead in the above aproach.

the below is the table content that is present in the transformation table in our database

SourceColName Transformation DestinationColName

coupon coupon/noofpayments coupon (noofpayments is also a sourcecolname)

quantity quantity*10 quantity

The derived column transformation gets an input which has the following columns coupon,quantity and noofpayments.

Is it possible that the derived column transformation pickes up the transformations from the database and apply it to the input columns.

I am pretty new to this and hence may not be understanding it correctly. apology for the same. But I am just trying to minimize the impact here if any of the transformation changes.

Thank for your time.

Regards,

$wapnil

|||No, this isn't going to be possible.

Perhaps with some advanced scripting, maybe.|||

Bugging you again Phil...

This is to help me finalize my approach of doing the data transformations.

Scenario:

1) 4 different flat file formats

2) 4 transformations set applying on each flat file

3) 1 destination table

Aproaches:

1) Build one file connection, flat file source and one data transformation for each flat file. Create one package for each OR create a single package and have multiple data flow paths in that.

2) Build custom flat file connection, custom flat file source and custom data transformation. Hush.....

Future changes:

There can be more flat file formats added to the list.

Apology for this but at my end here people want to build custom components and use it across projects.......so just trying to find out the fdeasibility.

Thanks again for your time.

Regards,

$wapnil

Dynamic transformation mapping in DTS

Hi,

I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.

Any ideas or possible workaround would be greatly appreciated.
Thank you very much.http://www.sqlservercentral.com/columnists/bknight/convertdb2.asp

HTH|||Hello,

Thank you for your reply, however I read the article for the link you supplied
and it doesn't solve my problem. My fault. I will elaborate and try to be clearer.

As you know, I need to export data from SQL tables to AS400 files(the SQL table has the same structure and column names as the file on the AS400).
I created a DTS that has 2 global variables for the source file and the destination table. I'm supplying a different source and destination file name every time I execute the DTS package(executed from a stored procedure).
In other words, on the first execution of the DTS, the global variables SQL server table A(source) and AS400 file B(destination) will be passed. On the second run, the variables will contain table name C and file name D. So on and so forth.
I need to re-create mapping every time the package is executed because I don't know the column names ahead of time.

In a nutshell, the data transformation task allows you to dynamically specify
a source and destination table name, but consequently, doesn't automatically generate the column mappings.

I don't think the problem lies in the fact that it's an SQL server to AS400 data transfer, but rather, how to create auto mappings based on a given source and destination.

Sorry if I'm being repetitive and I hope all this clarifies my issue.

Still hoping for a solution or workaround!
Thanks once again.|||In my experience Dynamic Mapping is not possible.

Can you link the databases and dynamically write the insert statement?

Can you create a vbsript to create a text file that always has the same layout but different sql source?

Then do your load on AS400 the same way?

Allow each system to control process on itself.

Just some possible suggestions.|||I'm afraid you're right about dynamic mapping not being possible.

Also, you seem to have some good suggestions to a workaround, however
you would need to elaborate because I don't know how to implement them.

Can you provide one or more examples?

Appreciate the response.

Dynamic table query for DBF files

Hi. I am using DBF files as sources for some tables in SQL Server. The problem is that the table names in the DBF files are not all the same (e.g. frame061, frame949). I want to know the names of the tables inside the DBF files. Is there a way to query the table names, something like "select table_name from information_schema.tables" in SQL Server? By the way, those DBF files came from FoxPro. Thanks!
Edit: I completely misread your question...|||

If you are working with individual DBF files (as opposed to the later DBC database containers, with which I'm not familiar), then it's my understanding that file = table, so you would need a list of *.DBF files in the file system folder that constitutes the database.

You could use the Foreach Loop container with the Foreach File enumerator to do something with each matching file in the folder. Or you could use the simple methods of System.IO within a Script task to get your list.

-Doug

|||I think the question has been misinterpreted. sorry. A single DBF contains 4 tables inside the single file. That means 1 file = 4 tables. What I would like to do is to get the names of the 4 tables INSIDE the single DBF file, using SSIS. Is there a possible solution to this? Thanks in advance!

Dynamic table query for DBF files

Hi. I am using DBF files as sources for some tables in SQL Server. The problem is that the table names in the DBF files are not all the same (e.g. frame061, frame949). I want to know the names of the tables inside the DBF files. Is there a way to query the table names, something like "select table_name from information_schema.tables" in SQL Server? By the way, those DBF files came from FoxPro. Thanks!
Edit: I completely misread your question...|||

If you are working with individual DBF files (as opposed to the later DBC database containers, with which I'm not familiar), then it's my understanding that file = table, so you would need a list of *.DBF files in the file system folder that constitutes the database.

You could use the Foreach Loop container with the Foreach File enumerator to do something with each matching file in the folder. Or you could use the simple methods of System.IO within a Script task to get your list.

-Doug

|||I think the question has been misinterpreted. sorry. A single DBF contains 4 tables inside the single file. That means 1 file = 4 tables. What I would like to do is to get the names of the 4 tables INSIDE the single DBF file, using SSIS. Is there a possible solution to this? Thanks in advance!

Dynamic table name in destination

How to create a new table dynamically in OLE DB destination.

This is what i am doing

I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.

Thanks in advance for any insight on how to make this work.

-Amar

I think you'll have to break this up into multiple work flows. The first executes an Execute SQL task in the control flow to create the table. Then attached to that Execute SQL Task is the data flow that operates on the file and then inserts into the destination, which is derived from the incoming table name.

That may work for you. The problem is, the OLEDB Destination cannot create a table at run time.|||

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

|||

Amar Khaira wrote:

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

In the OLE DB Destination, set it to "Table name or view name variable - fast load". Then just pick the variable that you loaded in the foreach loop. The metadata must be the same, though, for each table.|||

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

|||

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

|||

Amar Khaira wrote:

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

You need to create one table first. Then populate the variable that you are using in the foreach loop with a default value of that table you just created. Then in the OLE DB destination, in selecting that variable, it will read it and find the table. You can then perform your mappings. When you execute the package, the default value of the variable will be over-written.|||

Amar Khaira wrote:

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

See my comment above.|||

Phil,

That what I did...can you please elaborate more...what i am missing

|||

Amar Khaira wrote:

Phil,

That what I did...can you please elaborate more...what i am missing

You need to create a table first. Then, in the variable you are using, TYPE in the name of that table in the DEFAULT VALUE parameter of that variable.

Then in the OLE DB Destination, do as I said above. Select that variable, and it should work for you.|||And this next comment is important, so I'm going to make it its own post:

The metadata (number of columns, data types, etc...) must be the same across all of your tables that you are dynamically feeding into the OLE DB Destination.|||

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

|||

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

|||

Amar Khaira wrote:

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

Try setting the "ValidateExternalMetaData" property on the OLE DB Destination to false. If that doesn't work, then set the "DelayValidation" property of the data flow to true.|||

Amar Khaira wrote:

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

Excellent. Please mark one of these posts as the answer to your question.

Thanks,
Phil